Home > Mobile >  Calling same function on many worksheets
Calling same function on many worksheets

Time:07-30

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

error1

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

error2

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.

enter image description here

  • Related