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?

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?

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?

Who me? Yes, thank you.

"STEVE BELL" wrote:

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?

Didn't get it to work. The issue is that I have to analyze each single
sheet in the workbook. I have a certain list of sheets that are the
original "native" sheets. Any extra sheets are those that were added
by the user. By looping through each worksheet and comparing each
sheet to my list of "native" sheets, I can determine if it is a new
(i.e. "foreign") sheet. If it is, then I do a series of very simple
steps to the user's sheet so that I doesn't screw up some other things
that I need to do (e.g. I take out all of the user's formulas and
replace them with text).

Here is my basic code:

Dim ForeignSheet as Boolean
Set ForeignSheet = True
For x = 1 to Worksheets.Count
If Worksheet(x).Name = Sheet1.Name Then ForeignSheet = False
If Worksheet(x).Name = Sheet2.Name Then ForeignSheet = False
'repeat this sequence for each of the "native" sheets
Next
If ForeignSheet = True then Do Stuff

The weird thing is that if I replace Sheet1.Name with the exact tab
name of the sheet, the procedure works just fine. However, when I
attempt to use the CodeName method, it doesn't work. I want to refer
to code names rather than tab names to make the macro more bullet proof
and allow the user to change their tab names at their discretion.

Anybody see where I am going wrong?

I think Steve has already alluded to this, but the codenames of the
worksheets will not necessarily match the tab order of the the worksheets,
which appears to be what your code presumes.

Your sheets could be as follows:

Tab Order 1 2 3 4 5
CodeName Sheet2 Sheet4 Sheet1 Sheet5 Sheet3

If you have a listing of your native sheet names, set up a nested loop.
First go through your worksheets, and for each worksheet loop through your
listing of native sheets and compare the sheet name against each name in your
native list.




"Randy" wrote:

Didn't get it to work. The issue is that I have to analyze each single
sheet in the workbook. I have a certain list of sheets that are the
original "native" sheets. Any extra sheets are those that were added
by the user. By looping through each worksheet and comparing each
sheet to my list of "native" sheets, I can determine if it is a new
(i.e. "foreign") sheet. If it is, then I do a series of very simple
steps to the user's sheet so that I doesn't screw up some other things
that I need to do (e.g. I take out all of the user's formulas and
replace them with text).

Here is my basic code:

Dim ForeignSheet as Boolean
Set ForeignSheet = True
For x = 1 to Worksheets.Count
If Worksheet(x).Name = Sheet1.Name Then ForeignSheet = False
If Worksheet(x).Name = Sheet2.Name Then ForeignSheet = False
'repeat this sequence for each of the "native" sheets
Next
If ForeignSheet = True then Do Stuff

The weird thing is that if I replace Sheet1.Name with the exact tab
name of the sheet, the procedure works just fine. However, when I
attempt to use the CodeName method, it doesn't work. I want to refer
to code names rather than tab names to make the macro more bullet proof
and allow the user to change their tab names at their discretion.

Anybody see where I am going wrong?

Had to cut last reply short - had people waiting. I think you're looking for
something like this:

Sub test()
Dim MySheets()
Dim Native As Boolean

MySheets = Array(Sheet1, Sheet2, Sheet3)

For Each x In Worksheets
Native = False
For Each y In MySheets
If x.Name = y.Name Then Native = True
Next y
If Not Native Then < your procedures here
Next x

End Sub


The array contains the sheet objects (codename w/o quotes)- so you don't
have to worry about names changing.

If you're not already familiar with For..Each loops, when you put your
procedures in the above code, you would refer to the non-native worksheet as
x (unless you change x to a more descriptive name).

instead of activesheet.name or Sheets("Sheet1").name it would be x.name.

Hope this helps.



"Randy" wrote:

Didn't get it to work. The issue is that I have to analyze each single
sheet in the workbook. I have a certain list of sheets that are the
original "native" sheets. Any extra sheets are those that were added
by the user. By looping through each worksheet and comparing each
sheet to my list of "native" sheets, I can determine if it is a new
(i.e. "foreign") sheet. If it is, then I do a series of very simple
steps to the user's sheet so that I doesn't screw up some other things
that I need to do (e.g. I take out all of the user's formulas and
replace them with text).

Here is my basic code:

Dim ForeignSheet as Boolean
Set ForeignSheet = True
For x = 1 to Worksheets.Count
If Worksheet(x).Name = Sheet1.Name Then ForeignSheet = False
If Worksheet(x).Name = Sheet2.Name Then ForeignSheet = False
'repeat this sequence for each of the "native" sheets
Next
If ForeignSheet = True then Do Stuff

The weird thing is that if I replace Sheet1.Name with the exact tab
name of the sheet, the procedure works just fine. However, when I
attempt to use the CodeName method, it doesn't work. I want to refer
to code names rather than tab names to make the macro more bullet proof
and allow the user to change their tab names at their discretion.

Anybody see where I am going wrong?