Search code examples
excelvbamacrosbasic

excel/VBA pass a sheetname to a function

i have few sheet, and a need for once of it that a specific cell (A1) is equal "1" the label color become green once the change their value.

I declare in a ThisWorkBook the following function: 

Public Function ColorLabel(LabelName)
Set Foglio = Sheets(LabelName)
Set Target = Foglio.Range("A1")

If Target = "1" Then
    Foglio.Tab.ColorIndex = 4
Else
    Foglio.Tab.ColorIndex = xlNone
end if
End Function

So, in every sheet i define the following code

Private function Worksheet_Change(ByVal Target As Range)
 ColorLabel(ActiveSheet.CodeName)
end function

but I get the following error

Compilation error. Expected variable or routine and not form

What's wrong?

Someone help me?

Thanks!

Solution

  • If the function is defined in the ThisWorkbook module, you should call it this way:

        ThisWorkbook.ColorLabel Me.Name

    and by the way, as it is written, ColorLabel shoud be a Sub not a function (it does not return anything).

    However, better design would be:

    • move the sub to an independent code module or
    • rename the sub into an event handler using Workbook_SheetChange