VBA Renaming Case " "

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
657
Good Evening Everyone,

I'm curious, if in using the "Select Case" function below can be modified to reflect the Worksheet(#) in lieu of the Sheet Name?

VBA Code:
Select Case UCase(ws.Name)
        
            Case "Aq277"   
                With ws
                
                 Call Something
            
               End With
                          
                
            Case "Ar277"
                With ws
            
                Call Whatever
                
               End With
                                
        
     End Select
    
    Next ws

Please let me know, if you can come up with anything.

Thank you,
pinaceous
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
If you mean the position number of the worksheet in the tabs, then I think the answer is "yes" you can (untested though); however, since the user can move the tabs around as they please, I do not think that would not be an advisable method to use.
 

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
657
If you mean the position number of the worksheet in the tabs, then I think the answer is "yes" you can (untested though); however, since the user can move the tabs around as they please, I do not think that would not be an advisable method to use.

Yes, I'm testing out the position number of the worksheet in the tabs because I've locked its position so the user is only using one sheet. If you have a sample of the untested code, I'd love to test it out on my workbook.

Thank you.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
Here I have used the ActiveSheet for my sheet reference, but if you have code above the Select Case which references a worksheet via a variable declared as a Worksheet, then use that variable in place of ActiveSheet...
VBA Code:
Select Case ActiveSheet.Index
  Case 1
    Debug.Print "One"
  Case 2
    Debug.Print "Two"
  Case 3
    Debug.Print "Three"
End Select
 

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
657

ADVERTISEMENT

Interesting approach. I like it.

How would you then substitute the
worksheet name
for example to worksheet(2)?

For example:
VBA Code:
Dim ws As Worksheet

 For Each ws In ActiveWorkbook.Worksheets
 
 If ws.Name <> "  Worksheet(2)   " Then
 
 ws.visible = xlSheetHidden
 
 End If
 Next

Thank you!
Paul
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
I'm confused. I thought your original question was asking how to use Select Case? Also, I am not sure what this line of code is supposed to be doing...
VBA Code:
If ws.Name <> "  Worksheet(2)   " Then
Is your worksheet's name really the characters in "Worksheet(2)" with two leading spaces and three trailing spaces? Or is "Worksheet(2)" an actual worksheet reference which is what it looks like to me (in which case your test is not valid)? Please clarify what you are actually trying to test against ws.Name for us.
 

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
657
Hi Rick,

Sorry for the confusion, I'm actually asking a different question here. I'm all set with the case application that you suggested in post#4, I just had an additional question hence my post#5.

If you think it would better server to start a new thread, my apologies in advance.

To clarify, my worksheet's name is not called Worksheet(2), I only placed the worksheet(#) there to indicate where I'd like a substitution for the actual sheet name in the code. That's all.

Thank you!
Paul
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
Does this code snippet do what you want...
VBA Code:
  Dim ws As Worksheet

  For Each ws In ActiveWorkbook.Worksheets
    ws.Visible = ws.Name = "  Worksheet(2)   "
  Next
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,863
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top