Macro in add-in runs depending on if sheet exists in workbook

[JC]

I have a macro in an add-in (located in the XLSTART folder) which is run
from a toolbar.

I want a routine to run if sheet1 of the current workbook is called "Order
List 1"

If Sheet1.Name = "Order List 1" Then
'Run a routine
Else
MsgBox "This program only works on the `Internet Orders & Packing Sheets'
spread sheet"
Exit Sub
End If

This works if you only open 1 workbook at a time, but if you have more than
one open it sometimes defaults to the sheet1 name of the first worksheet
opened.

Thanks

 

[Barb Reinhardt]

By current workbook, I presume you mean ActiveWorkbook? Is there a reason
that you need to have a specific worksheet name? Why not define another
worksheet code name for the worksheet of interest. To do this, right click
on the worksheet tab to view Code. Select the worksheet of interest and if
the properties aren't displayed, press F4. You should have an option to
change the name there..

Sub Test()
Dim aWB As Workbook
Dim WS As Worksheet
Dim myWS As Worksheet

Set aWB = ActiveWorkbook

For Each WS In aWB.Worksheets
If WS.CodeName = "Orders_PackingSheets" Then
Set myWS = WS
Exit For
End If
Next WS

If Not myWS Is Nothing Then
'Run a routine
Else
MsgBox "This program only works on the `Internet Orders & " & vbNewLine & _
"Packing Sheets' spread sheet"
Exit Sub
End If

End Sub

 

[JC]

That's work fine, thanks for the post

By current workbook, I presume you mean ActiveWorkbook? Is there a reason
that you need to have a specific worksheet name? Why not define another
worksheet code name for the worksheet of interest. To do this, right
click
on the worksheet tab to view Code. Select the worksheet of interest and
if
the properties aren't displayed, press F4. You should have an option to
change the name there..

Sub Test()
Dim aWB As Workbook
Dim WS As Worksheet
Dim myWS As Worksheet

Set aWB = ActiveWorkbook

For Each WS In aWB.Worksheets
If WS.CodeName = "Orders_PackingSheets" Then
Set myWS = WS
Exit For
End If
Next WS

If Not myWS Is Nothing Then
'Run a routine
Else
MsgBox "This program only works on the `Internet Orders & " & vbNewLine &
_
"Packing Sheets' spread sheet"
Exit Sub
End If

End Sub

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.