Case select and sheet codename
 

Hi everyone,

I'm replacing all sheet tab names with sheet codenames in my project.

I'm getting Runtime error 438. Object doesn't support this property or method.

I'm guessing it's this part of the code. ???

Select Case sht.CodeName
Case Sheet33, Sheet25, Sheet28, Sheet27
sht.Visible = xlVeryHidden
Case Else
sht.Visible = True
End Select

Hoping that someone can help :-)

--
Thank you
Annie

Case select and sheet codename
 

sht.codename is a string.

I'd try:

Select Case sht.CodeName
Case "Sheet33", "Sheet25", "Sheet28", "Sheet27"

Annie Whitley wrote:

Hi everyone,

I'm replacing all sheet tab names with sheet codenames in my project.

I'm getting Runtime error 438. Object doesn't support this property or method.

I'm guessing it's this part of the code. ???

Select Case sht.CodeName
Case Sheet33, Sheet25, Sheet28, Sheet27
sht.Visible = xlVeryHidden
Case Else
sht.Visible = True
End Select

Hoping that someone can help :-)

--
Thank you
Annie

--

Dave Peterson

Case select and sheet codename
 

Hi Annie,

When the error message box occurs, try pressing the
"Debug" button and VBA will highlight exactly where
the error is occurring. The error is probably occurring
at the line that says:

Case Sheet33, Sheet25, Sheet28, Sheet27

Try placing double quotes around each sheet name
as follows:

Case "Sheet33", "Sheet25", "Sheet28", "Sheet27"

Hope that helps!

Robert



"Annie Whitley" wrote in message
...
Hi everyone,

I'm replacing all sheet tab names with sheet codenames in my project.

I'm getting Runtime error 438. Object doesn't support this property or
method.

I'm guessing it's this part of the code. ???

Select Case sht.CodeName
Case Sheet33, Sheet25, Sheet28, Sheet27
sht.Visible = xlVeryHidden
Case Else
sht.Visible = True
End Select

Hoping that someone can help :-)

--
Thank you
Annie

Case select and sheet codename
 

Hi Annie,

The sheet codenames are strings so enclose the names in double quotes.

Case "Sheet33", "Sheet25", "Sheet28", "Sheet27"


--
Regards,

OssieMac


"Annie Whitley" wrote:

Hi everyone,

I'm replacing all sheet tab names with sheet codenames in my project.

I'm getting Runtime error 438. Object doesn't support this property or method.

I'm guessing it's this part of the code. ???

Select Case sht.CodeName
Case Sheet33, Sheet25, Sheet28, Sheet27
sht.Visible = xlVeryHidden
Case Else
sht.Visible = True
End Select

Hoping that someone can help :-)

--
Thank you
Annie

Case select and sheet codename
 

Hi OssieMac,

That does the trick! Thank you :-)


--
Thank you
Annie


"OssieMac" wrote:

Hi Annie,

The sheet codenames are strings so enclose the names in double quotes.

Case "Sheet33", "Sheet25", "Sheet28", "Sheet27"


--
Regards,

OssieMac


"Annie Whitley" wrote:

Hi everyone,

I'm replacing all sheet tab names with sheet codenames in my project.

I'm getting Runtime error 438. Object doesn't support this property or method.

I'm guessing it's this part of the code. ???

Select Case sht.CodeName
Case Sheet33, Sheet25, Sheet28, Sheet27
sht.Visible = xlVeryHidden
Case Else
sht.Visible = True
End Select

Hoping that someone can help :-)

--
Thank you
Annie

Case select and sheet codename
 

Thanks for your help Dave :-)
--
Thank you
Annie


"Dave Peterson" wrote:

sht.codename is a string.

I'd try:

Select Case sht.CodeName
Case "Sheet33", "Sheet25", "Sheet28", "Sheet27"

Annie Whitley wrote:

Hi everyone,

I'm replacing all sheet tab names with sheet codenames in my project.

I'm getting Runtime error 438. Object doesn't support this property or method.

I'm guessing it's this part of the code. ???

Select Case sht.CodeName
Case Sheet33, Sheet25, Sheet28, Sheet27
sht.Visible = xlVeryHidden
Case Else
sht.Visible = True
End Select

Hoping that someone can help :-)

--
Thank you
Annie

--

Dave Peterson
.

Case select and sheet codename
 

Cheers Robert :-)
--
Thank you
Annie


"Robert Crandal" wrote:

Hi Annie,

When the error message box occurs, try pressing the
"Debug" button and VBA will highlight exactly where
the error is occurring. The error is probably occurring
at the line that says:

Case Sheet33, Sheet25, Sheet28, Sheet27

Try placing double quotes around each sheet name
as follows:

Case "Sheet33", "Sheet25", "Sheet28", "Sheet27"

Hope that helps!

Robert



"Annie Whitley" wrote in message
...
Hi everyone,

I'm replacing all sheet tab names with sheet codenames in my project.

I'm getting Runtime error 438. Object doesn't support this property or
method.

I'm guessing it's this part of the code. ???

Select Case sht.CodeName
Case Sheet33, Sheet25, Sheet28, Sheet27
sht.Visible = xlVeryHidden
Case Else
sht.Visible = True
End Select

Hoping that someone can help :-)

--
Thank you
Annie

.

Case select and sheet codename
 

Annie,

As others have pointed out, you were missing the quotes around the
code names. Just FYI, if you are using code names in your code in
order to prevent problems that might occur if the user renames a
sheet, you can use the code name anywhere you would normally use the
worksheet's tab name. The code name doesn't change when the user
changes a tab name. For example

Sheet1.Range("A1").Value =1234

Moreover, you can change the code name of a worksheet to something
more meaningful that "Sheet3". In VBA, select the module in question
in the Project window, press F4 to display the Properties window if it
is not already visible, and change the Name property to the desired
name. Note that unlike a worksheet's tab name, the code name cannot
contains spaces or punctuation characters. Alpha-numeric only. Then
you can reference the sheet like

SummarySheet.Range("A1").Value = 1234

where SummarySheet is the new code name. This is obviously more
meaningful that just 'Sheet3'.

You can change code names via code. If you want to access the
worksheet via its tab name, use code like the following:

With ThisWorkbook
.VBProject.VBComponents( _
.Worksheets("Sheet Two").CodeName).Name = "NewCodeName"
End With

Or, if you know the current code name, use code like

ThisWorkbook.VBProject. _
VBComponents("CurrentCodeName").Name = "NewCodeName"

And, just for completeness, you can also use

ThisWorkbook.VBProject.VBComponents("MainSheet"). _
Properties("Name") = "AnotherName"

but this last method has no advantage over the other methods. Just yet
another way to do the same thing.

I often use code names rather than tab names in my projects to prevent
run time errors if the user changes a tab name. No matter how many
times you tell a user not to change the tab name, someone will always
do just that, screwing things up.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]





On Sat, 6 Feb 2010 12:41:01 -0800, Annie Whitley
wrote:

Hi everyone,

I'm replacing all sheet tab names with sheet codenames in my project.

I'm getting Runtime error 438. Object doesn't support this property or method.

I'm guessing it's this part of the code. ???

Select Case sht.CodeName
Case Sheet33, Sheet25, Sheet28, Sheet27
sht.Visible = xlVeryHidden
Case Else
sht.Visible = True
End Select

Hoping that someone can help :-)