VBA Renaming Case " "

[Pinaceous]

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?

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

 

[Rick Rothstein]

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]

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]

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...

Select Case ActiveSheet.Index
  Case 1
    Debug.Print "One"
  Case 2
    Debug.Print "Two"
  Case 3
    Debug.Print "Three"
End Select

 

[Pinaceous]

Interesting approach. I like it.

How would you then substitute the

worksheet name ​

for example to worksheet(2)?

For example:

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]

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...

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]

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]

Does this code snippet do what you want...

  Dim ws As Worksheet

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