I'm using an .xla to make modifications to another workbook. In my
procedure, I'm need to test the name of each worksheet to determine how
to proceed. For some reason, I am unable to correctly point to capture
the sheet name in the other workbook. I have tried a few different
methods, none with any success.

Here is the abbreviated code:

Sub UnprotectSheets(strUpdateWb As String)
'where strUpdateWb is the name of an open workbook

Windows(strUpdateWb).Activate
Dim wb As Workbook
Set wb = ActiveWorkbook

'Attempt #1
Dim temp As String
temp = wb.Sheet1.Name
MsgBox (temp) 'yields a run-time error. code stops.

'Attempt #2
temp = Sheet1.Name
MsgBox (temp) 'gives me the sheet name from the xla. I need the
sheet name from the xls workbook

'Attempt #3
temp = strUpdateWb.Sheet1.Name
MsgBox (temp) 'run time error again

End Sub

Can anybody point me in the right direction?

Thanks,
Randy Eastland

As you are using it Sheet1 is going to refer to the CodeName of a sheet. In
VBE's Project explorer a sheet appears as CodeName(Name on Tab). The
CodeName can be changed by changing the (Name) property of a sheet in VBE.
When a sheet is first created these names are the same but from that point
on changes to one are not reflected in the other. i.e., renaming the tab of
Sheet30 to Sheet1 won't change it's CodeName, which remains Sheet30.
Likewise, you can change the codename of a sheet in the VBE and always be
able to refer to it as such without worrying about what names the user may
have applied to the tabs (which is why the CodeName property exists).

#1 wb.Sheet1.Name - - I assume you got an error because the ActiveWorkbook
did not have a sheet with the CodeName "Sheet1". Deleted or changed
somewhere along the way.

#2 Sheet1.Name - - I guess that CodeName must use ThisWorkbook (the
Add-In) as the default (I'm a little surprised by this one). It evidently
has a Sheet1.

#3 strUpdateWb.Sheet1.Name - - strUpdateWb is a string, not a workbook, so
it failed on the workbook reference
(#3b) Workbooks(strUpdateWb).Sheet1.Name - - would have addressed the string
issue, but with results identical to #1

Maybe you can try something like:

for i = 1 to wb.Sheets.Count
temp = wb.Sheets(i).Name & " " & wb.Sheets(i).CodeName
msgBox temp
Next i

BTW, when working with Add-Ins, ActiveWorkbook defaults to the book that is
calling the add-in. You might not need to pass any values or activate any
workbooks.


HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Randy" wrote in message
ups.com...
I'm using an .xla to make modifications to another workbook. In my
procedure, I'm need to test the name of each worksheet to determine how
to proceed. For some reason, I am unable to correctly point to capture
the sheet name in the other workbook. I have tried a few different
methods, none with any success.

Here is the abbreviated code:

Sub UnprotectSheets(strUpdateWb As String)
'where strUpdateWb is the name of an open workbook

Windows(strUpdateWb).Activate
Dim wb As Workbook
Set wb = ActiveWorkbook

'Attempt #1
Dim temp As String
temp = wb.Sheet1.Name
MsgBox (temp) 'yields a run-time error. code stops.

'Attempt #2
temp = Sheet1.Name
MsgBox (temp) 'gives me the sheet name from the xla. I need the
sheet name from the xls workbook

'Attempt #3
temp = strUpdateWb.Sheet1.Name
MsgBox (temp) 'run time error again

End Sub

Can anybody point me in the right direction?

Thanks,
Randy Eastland