checking multiple sheets.

[Scott]

Hi,

Can someone please help me with my macro.

I'm trying to create a macro that will check the name of the active sheet
from a list in an array, or something like that, if this sheet is in the
list of named sheets then exit the sub.

Is this possible?

Hope someone can help,
Thanks
Best regards,
Scott

 

[Tom Ogilvy]

res = application.Match(activesheet.name,Array("sheet1","sheet2"),0)
if not iserror(res) then
exit sub
End if

 

[Scott]

Thanks Tom,

Just one more thing, is there any way of changing this so it is not case
sensitive?

Thanks
Best regards,
Scott






res = application.Match(activesheet.name,Array("sheet1","sheet2"),0)
if not iserror(res) then
exit sub
End if

 

[Tom Ogilvy]

There is no change required - it isn't case sensitive as written.

 

[mrmac]

to match strings whether they are in upper or lower case
use the option:

Option Compare Text

at the top of the module, before the sub

 

[Tom Ogilvy]

Tilde is a special character, so you would have to double up the tilde

?
application.Match("Micro~~100",Array("Micro~10","Micro~50","Micro~100","Micr
o~400"),0)
3

as an example,

 

[Scott]

Hi Tom,

Thank you very much for your help, but I'm still having problems. As you may
have suspected I am only a beginner and don't know how to fix this problem.

I've tried the code you sent, but cannot get it to work on the active sheet.
This code is supposed to check the active sheet, if it is the Micro~100
sheet, then it displays the message "Micro~100 Sheet Found", and if it isn't
it then displays "Sheet Not Found". But it displays "Micro~100 Sheet Found"
weather it's the active sheet or not, can't work it out.

res = Application.Match("Micro~~100", Array("Micro~100"), 0)
If Not IsError(res) Then
Msgbox "Micro~100 Sheet Found"
Exit Sub
End If
Msgbox "Sheet Not Found"

Hope you or someone can please help me out,
Thanks
Best regards,
Scott

P.S. I have another problem with the first bit of code, but I think I'd
better send it in another message.






Tilde is a special character, so you would have to double up the tilde

?
application.Match("Micro~~100",Array("Micro~10","Micro~50","Micro~100","Micr
o~400"),0)
3

as an example,

 

[Dave Peterson]

You've coded the actual names into the line of code. Maybe using the
activesheet's name:

Sub testme01()
Dim res As Variant

res = Application.Match(Application.Substitute(ActiveSheet.Name, "~", "~~"), _
Array("Micro~100"), 0)
If Not IsError(res) Then
MsgBox "Micro~100 Sheet Found"
Exit Sub
End If
MsgBox "Sheet Not Found"
End Sub

If you're using xl2k or above, you can use Replace instead of
application.substitute.