Case "Question"

Pinaceous

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

I have a Case code question.

In having:

Code:
         Case "Tab Name"
Now the
"Tab Name"
represents just that, the name at the bottom of the tab of which is the actual name of the worksheet.

Is there anyway to specify this in a generic fashion?

For example with the sheet name?

For example, Sheet1 (Tab Name); somehow using Sheet1 as the name for a Case.

I don't know how to code this.

Please help.

Thank you,
Pinaeous
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,317
Office Version
2013
Platform
Windows
Something like this ??

Code:
For Each ws In Worksheets
 ws.Activate
    Select Case (ws.Name)
           Case "Sheet1"
                'Do Stuff

            Case "Sheet2"
                'Do other stuff
           Case Else
                "Do more stuff
     End Select
 

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
589
Hi Michael,

Be patient with me if I confuse it,

In the VBA window, under Microsoft Excel Objects, it lists ..
Sheet1(Name1) - I've named the tab as Name1

So, here the worksheet, ws.Name = Name1 but now if I want with,

Case "Sheet1" meaning the worksheet(1) is this the same as your answer??
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,317
Office Version
2013
Platform
Windows
Be patient with me if I confuse it,
you confused me...:LOL:


Code:
For Each ws In Worksheets
 ws.Activate
    Select Case (ws.Name)[color=red]'this doesn't change !![/color]
           Case "Name1" [color=red]'this changes to your required sheet name !![/color]
                'Do Stuff

            Case "YOURSHEETNAME"[color=red]'this changes to your required sheet name !![/color]
                'Do other stuff
           Case Else
                "Do more stuff
     End Select
Did that help ??
 

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
589
Code:
        Select Case UCase(ws.Name)
        
            Case "Sheet1"
What happens if you want …

Code:
        Select Case UCase(ws.NUMBER)
        
            Case Worksheets(2)
So, not in using the ws.Name but using its ws.Number.

How do I do this?

& Thank you!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,317
Office Version
2013
Platform
Windows
Ahh...you mean like this ??

Code:
Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
 ws.Activate
    Select Case ActiveSheet.Index
           Case 1 "sheet number
                'Do stuff
            Case 2 'sheet number
               'Do more stuff
     End Select
Next ws
End Sub
 

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
589
Hi Michael,

I think that did it for my question.

Thank you,
Paul
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,317
Office Version
2013
Platform
Windows
Keeping in mind of course that you may / may not need to loop through the sheets.
So, if you want to apply / or not, the code to activesheets it could simply be

Code:
Sub MM1()
Select Case ActiveSheet.Index
   Case 1 "sheet number
           'Do stuff
   Case 2 'sheet number
           'Do more stuff
End Select
End Sub
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,909
Office Version
365
Platform
Windows
In the VBA window, under Microsoft Excel Objects, it lists ..
Sheet1(Name1) - I've named the tab as Name1

So, here the worksheet, ws.Name = Name1 but now if I want with,

Case "Sheet1" meaning the worksheet(1)
Just a word of warning, Sheet1 is NOT the same as worksheet(1).
Sheet1 is the codename of the sheet & will appear in the Project window in alphabetical order.
Worksheets(1) is the sheet index & refers to the position of sheet in the workbook. So the left most sheet is sheet(1), the next is sheet(2) etc.
Using the index number is unreliable, because if somebody changes the order of the sheets, your code will potentially still work, but on the wrong sheet.
 

Forum statistics

Threads
1,085,931
Messages
5,386,824
Members
402,020
Latest member
tsfan74

Some videos you may like

This Week's Hot Topics

Top