Checking Active Sheet is in an approved list

M

Matt

Hi Guys

I want my code to make a descion depending on whether or not the active
sheet is in an approved list.

I thought I would use an array for this as below

If ActiveSheet.Name = Array("Addis ETB", "Addis USD", "Geneva USD", _
"HSBC Cards", "London GBP", "London USD", "Hong Kong HKD", "Hong Kong USD")
Then

But obviously my syntax is wrong. If someone could help with the syntax or
let me know of a better way that would be fantastic.

Thanks for reading this far, and in advance for any help

Matt
 
M

Muhammed Rafeek M

Dim tmpS as string

tmpS=ActiveSheet.Name
If tmpS= "Addis ETB" OR tmpS = "Addis USD" OR tmpS = "Geneva USD"_
OR tmpS = "HSBC Cards" OR tmpS = "London GBP" OR tmpS = "London USD" OR
tmpS = "Hong Kong HKD" OR tmpS = "Hong Kong USD"
Then
 
D

Don Guillett

maybe?

myarray = Array("Sheet1", "Sheet2")
For Each S In myarray
If S = ActiveSheet.Name Then MsgBox "Hi"
Next
 
M

Matt

Thanks Muhammed

That did the trick

Matt

Muhammed Rafeek M said:
Dim tmpS as string

tmpS=ActiveSheet.Name
If tmpS= "Addis ETB" OR tmpS = "Addis USD" OR tmpS = "Geneva USD"_
OR tmpS = "HSBC Cards" OR tmpS = "London GBP" OR tmpS = "London USD" OR
tmpS = "Hong Kong HKD" OR tmpS = "Hong Kong USD"
Then
Click to expand...
 
D

Dave Peterson

Another way:

dim myNames as variant
dim res as variant

mynames = Array("Addis ETB", "Addis USD", "Geneva USD", _
"HSBC Cards", "London GBP", "London USD", "Hong Kong HKD", "Hong Kong USD")

res = application.match(activesheet.name,mynames,0)

if iserror(res) then
'not found
else
'found
end if
 
Top