I'm have a procedure built in one workbook that is designed to make
modifications to another workbook that is selected by the user. In my
procedure, I need to test the name of each worksheet in the user's
workbook to determine how to proceed on each sheet. For some reason, I
am unable to correctly point the appropriate sheet in the user's
workbook and capture its name in a variable. 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 selected by the user

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

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

'Attempt #2
ShName = Sheet1.Name
MsgBox (ShName) 'gives me the sheet name from the immediate
workbook. I need the sheet name from the user-selected workbook

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

End Sub

Why can't I "see" the other workbook? Can anybody point me in the
right direction?

Thanks,
Randy Eastland

Instead of Sheet1.Name
try either:
Sheets(1).Name
Sheets("Sheet1").Name

you could:

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
MsgBox (ws.Name)
Next


--
steveB

Remove "AYN" from email to respond
"Randy" wrote in message
ups.com...
I'm have a procedure built in one workbook that is designed to make
modifications to another workbook that is selected by the user. In my
procedure, I need to test the name of each worksheet in the user's
workbook to determine how to proceed on each sheet. For some reason, I
am unable to correctly point the appropriate sheet in the user's
workbook and capture its name in a variable. 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 selected by the user

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

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

'Attempt #2
ShName = Sheet1.Name
MsgBox (ShName) 'gives me the sheet name from the immediate
workbook. I need the sheet name from the user-selected workbook

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

End Sub

Why can't I "see" the other workbook? Can anybody point me in the
right direction?

Thanks,
Randy Eastland

Thanks, Steve. Unfortunately, neither of these methods will work for
me. I am specifically trying to avoid using the "tab name" (e.g.
"Sheet1") so that the user can remain free to change the sheet names at
their discretion.

The Sheets(1) method doesn't work for me since that method requires
that the sheet be the first sheet in the stack of sheets. If the user
moves the sheet, then Sheet(1) refers to a different sheet then I am
intending. If my understanding of this is incorrect, let me know.

Any other takers on this question?

How about this.

In this one wsn = the codename (the name shown in VBA)
This stays the same no matter what the tab name is...

Dim ws As Worksheet, x, wsn As String

For Each ws In ActiveWorkbook.Worksheets
wsn = ws.CodeName
Next

--
steveB

Remove "AYN" from email to respond
"Randy" wrote in message
oups.com...
Thanks, Steve. Unfortunately, neither of these methods will work for
me. I am specifically trying to avoid using the "tab name" (e.g.
"Sheet1") so that the user can remain free to change the sheet names at
their discretion.

The Sheets(1) method doesn't work for me since that method requires
that the sheet be the first sheet in the stack of sheets. If the user
moves the sheet, then Sheet(1) refers to a different sheet then I am
intending. If my understanding of this is incorrect, let me know.

Any other takers on this question?

Sorry Steve - I replied to the wrong post. I meant to reply to Randy that
I've never gotten the syntax

Wb.Sheet1.Name

to work (although I've always thought that it should).


"STEVE BELL" wrote:

How about this.

In this one wsn = the codename (the name shown in VBA)
This stays the same no matter what the tab name is...

Dim ws As Worksheet, x, wsn As String

For Each ws In ActiveWorkbook.Worksheets
wsn = ws.CodeName
Next

--
steveB

Remove "AYN" from email to respond
"Randy" wrote in message
oups.com...
Thanks, Steve. Unfortunately, neither of these methods will work for
me. I am specifically trying to avoid using the "tab name" (e.g.
"Sheet1") so that the user can remain free to change the sheet names at
their discretion.

The Sheets(1) method doesn't work for me since that method requires
that the sheet be the first sheet in the stack of sheets. If the user
moves the sheet, then Sheet(1) refers to a different sheet then I am
intending. If my understanding of this is incorrect, let me know.

Any other takers on this question?

No problem!

Did you solve your problem?

--
steveB

Remove "AYN" from email to respond
"JMB" wrote in message
...
Sorry Steve - I replied to the wrong post. I meant to reply to Randy that
I've never gotten the syntax

Wb.Sheet1.Name

to work (although I've always thought that it should).


"STEVE BELL" wrote:

How about this.

In this one wsn = the codename (the name shown in VBA)
This stays the same no matter what the tab name is...

Dim ws As Worksheet, x, wsn As String

For Each ws In ActiveWorkbook.Worksheets
wsn = ws.CodeName
Next

--
steveB

Remove "AYN" from email to respond
"Randy" wrote in message
oups.com...
Thanks, Steve. Unfortunately, neither of these methods will work for
me. I am specifically trying to avoid using the "tab name" (e.g.
"Sheet1") so that the user can remain free to change the sheet names at
their discretion.

The Sheets(1) method doesn't work for me since that method requires
that the sheet be the first sheet in the stack of sheets. If the user
moves the sheet, then Sheet(1) refers to a different sheet then I am
intending. If my understanding of this is incorrect, let me know.

Any other takers on this question?

I've never gotten that to work either. The Worksheets method appears to do
what you need.


MsgBox wb.Worksheets(Sheet1.Index).Name

"Randy" wrote:

Thanks, Steve. Unfortunately, neither of these methods will work for
me. I am specifically trying to avoid using the "tab name" (e.g.
"Sheet1") so that the user can remain free to change the sheet names at
their discretion.

The Sheets(1) method doesn't work for me since that method requires
that the sheet be the first sheet in the stack of sheets. If the user
moves the sheet, then Sheet(1) refers to a different sheet then I am
intending. If my understanding of this is incorrect, let me know.

Any other takers on this question?