Hi there,

I read that (O'Reilly Excel Hacks, page 192) if you use "absolute"
Worksheet references that if the actual worksheet name changes that the
application will continue to work. This is true but I am using Excel
2003 and keep getting a reference not valid message, although the macro
still appears to work. Is there any way to fix this so that the
message does not appear?

To make myself clear:

Normally to reference a worksheet you can use:

1)
dim wks as Excel.Worksheet
set wks = ThisWorkbook.Worksheets("Sheet1")

OR

2)
dim wks as Excel.Worksheet
set wks = ThisWorkbook.Worksheets(1)

However, if you change Sheet1 to another name, (1) above breaks. If
you change the worksheet position within the workbook, (2) above
breaks.

However you are supposedly able to just reference a sheet by the name
given to it in the properties window. So looking in your Project
Explorer under Microsoft Excel Objects if you use the Codename of the
worksheet rather than the name you have given it (i.e. Sheet1, etc.)
you can reference the sheet directly without going through the above,
wiht the added benefit of it being (supposedly) impervious to worksheet
name changes... Except that it gives you this annoying Reference not
valid message.

Any help on getting the message to go away?

Regards,

Ric

You are absolutely correct that it is better to reference teh code andm than
the tab name for the reasons you describe. I am not too sure what problem you
are having with Sheet1 as a valid reference (just as an aside in the
properties you can change the code name to something more descriptive like
shtRawData.

Try this

sub Test
msgbox Sheet1.Name
end sub

Note that the intellisence will work when you type the period which is
another advantage...
--
HTH...

Jim Thomlinson


"ric_deez" wrote:

Hi there,

I read that (O'Reilly Excel Hacks, page 192) if you use "absolute"
Worksheet references that if the actual worksheet name changes that the
application will continue to work. This is true but I am using Excel
2003 and keep getting a reference not valid message, although the macro
still appears to work. Is there any way to fix this so that the
message does not appear?

To make myself clear:

Normally to reference a worksheet you can use:

1)
dim wks as Excel.Worksheet
set wks = ThisWorkbook.Worksheets("Sheet1")

OR

2)
dim wks as Excel.Worksheet
set wks = ThisWorkbook.Worksheets(1)

However, if you change Sheet1 to another name, (1) above breaks. If
you change the worksheet position within the workbook, (2) above
breaks.

However you are supposedly able to just reference a sheet by the name
given to it in the properties window. So looking in your Project
Explorer under Microsoft Excel Objects if you use the Codename of the
worksheet rather than the name you have given it (i.e. Sheet1, etc.)
you can reference the sheet directly without going through the above,
wiht the added benefit of it being (supposedly) impervious to worksheet
name changes... Except that it gives you this annoying Reference not
valid message.

Any help on getting the message to go away?

Regards,

Ric

Hi Jim,

I can reference both the .Name and the .Codename properties from the
immediate window and the macros run as intended, it is just that prior
to the macros even executing I get an annoying Message Box with the
"Reference Not Valid" message. Are you using Excel 2003?

Regards,

Ric

I have 2003. I don't have access to it right now but I do use it. I don't
think this is a matter of the version. In the VBE select Debug - Compile
VBA_Project. Does it compile?
--
HTH...

Jim Thomlinson


"ric_deez" wrote:

Hi Jim,

I can reference both the .Name and the .Codename properties from the
immediate window and the macros run as intended, it is just that prior
to the macros even executing I get an annoying Message Box with the
"Reference Not Valid" message. Are you using Excel 2003?

Regards,

Ric

Check under tools|references for that error message (I don't recall ever seeing
this, so it's a guess.)

But this worked ok for me (xl2003):

Option Explicit
Sub testme()
Dim wks As Worksheet
Set wks = Sheet2
Debug.Print wks.Name & "--" & wks.CodeName
End Sub

And I saw this in the immediate window.

ThisIsATest--Sheet2

ric_deez wrote:

Hi Jim,

I can reference both the .Name and the .Codename properties from the
immediate window and the macros run as intended, it is just that prior
to the macros even executing I get an annoying Message Box with the
"Reference Not Valid" message. Are you using Excel 2003?

Regards,

Ric

--

Dave Peterson

ric_deez wrote:

Normally to reference a worksheet you can use:

1)
dim wks as Excel.Worksheet
set wks = ThisWorkbook.Worksheets("Sheet1")

OR

2)
dim wks as Excel.Worksheet
set wks = ThisWorkbook.Worksheets(1)




Or

dim wks as Excel.Worksheet
set wks = ThisWorkbook.Sheet1


if Sheet1 is still a name of your worksheet set in property windows


Is this what you wanted to have?

dim wks as Excel.Worksheet
set wks = ThisWorkbook.Sheet1

That fails for me in Excel 2K, as does
set wks = ActiveWorkbook.Sheet1

but I can do -

Set wks = Sheet1

If I want to reference a worksheet with the codename "Sheet1" in any
workbook

For each wks in Activeworkbook
If wks.Codename = "Sheet1" then
msgbox wks.Name
' got my sheet, etc
End if
Next

Regards,
Peter T