I have an Excel file that is a calendar with some specifications. I have code that will send emails, according to what is selected on the calendar.
When I click the command button on the sheet that corresponds to January, the emails are prepared.
I don't think it makes sense to repeat the code on the other sheets, but I don't know how to call it from there.
This is part of the code I have on the January sheet.
Private Sub CommandButton1_Click()
Call sendEmail_testemunho("Semana1")
End Sub
Public Sub sendEmail_testemunho(nSemana As String)
On Error GoTo ErrHandler
Dim testemunho, hcell, cell, mcell, rgHorarios, diasSemana As Range
Set testemunho = Range(nSemana)
Set diasSemana = Range("DiasSemana")
Set emails = Worksheets("Publicadores Locais Horários").ListObjects("emails").Range
...
...
...
Set testemunho = Nothing
ErrHandler:
Debug.Print Err.Message & " on line " & Erl
End Sub
I tried to call the sub on another sheet. It sends this error.
Compile error:
Sub or Function not defined
I also tried to copy and paste the first sheet. When I click the button I have another error.
Runtime error '438':
Object doesn't support this property or method
CodePudding user response:
You have to use the codename as prefix
Code in January-sheet: codename = wsJanuary
Public sub sendMail(Email as string)
msgbox email
End sub
Code in Febuary-sheet: codename = wsFebuary
Public sub sendMail(Email as string)
wsJanuary.sendMail Email
End sub
BUT the better solution would be to put sendMail
into a normal module. Then you can access it without prefix.