1. in VBA you don't have to use Worksheet_Activate, since you can refer to every sheet directly, using its indexnumber, name or codename: sheet(7), sheets("July") or sheet7
2. in VBA checking in the worksheet_Activate event whether the name of the sheet is part of a customlist seems to be redundant.
3. if you are checking the name of the sheet in the worksheet_Activate event, you should us the argument sh ot het event, not the activesheet.name. In your example code you should have used
sh.name
4. if you aks this to understand the technique:
4a. a customlist is a 1-dimensional array
4b. a sheetname is a string
4c. there are several/many techniques to check whether a string is part of a 1-dimensional array.
to mention only a few:
5.1
Code: Select all
msgbox ubound(filter(application.getcustomlistcontents(3),activesheet.name))>-1
5.2
Code: Select all
msgbox instr(activesheet.name,join(application.getcustomlistcontents(3),"")
5.3
Code: Select all
msgbox not iserror(application.match(actviesheet.name,application.getcustomlistcontents(3),0))
5.4
Code: Select all
for j=1 to 12
if activesheet.name=application.getcustomlistcontent(3)(j) then exit for
next
msgbox j<13
6. If this code is part of the Worksheet_activate event the code shoud read as follows:
6.1
Code: Select all
msgbox ubound(filter(application.getcustomlistcontents(3),sh.name))>-1
6.2
Code: Select all
msgbox instr(sh.name,join(application.getcustomlistcontents(3),"")
6.3
Code: Select all
msgbox not iserror(application.match(sh.name,application.getcustomlistcontents(3),0))
6.4
Code: Select all
for j=1 to 12
if sh.name=application.getcustomlistcontent(3)(j) then exit for
next
msgbox j<13