Activesheet in Select Case Statement

[bearcub]

I want to use a series of message boxes to describe to the user how to use a sheet in a big file that I have (I have about 20 different sheets.


What I would like to do is set up a Select Case statement so that when the user pushes a button the appropriate set of instructions will pop up describing the purpose of the sheet. In stead of have 20 different macros for 20 buttons I would like to have one macro which will fire and be able to display the appropriate instructions based upon the active sheet name.

Is this possible?

Thank you in advance,

Michael

 

[Joe4]

Where exactly is this button going to reside?

 

[Dryver14]

something like

Sub getsheetname()
    
        Select Case ActiveSheet.Name
            Case "Sheet1"
                MsgBox "Hello"
            Case "Sheet2"
                MsgBox "goodbye"
    End Select
 End Sub

 

[bearcub]

That's what I was looking for, the Select Case Activesheet.name. I was wasn't quite sure if that could be used as a criteria but I guess it can.

Thank you.

I'll attach the macro using this construct to a command button so when the user presses this button( I don't want it to pop up everytime the sheet is activated) they will get the information explaining the purpose of the of spreadsheet and how to update it.

Michael

 

[Joe4]

I'll attach the macro using this construct to a command button

OK. I will under the mistaken assumption that you didn't actually want 20 different buttons (one for each sheet), but it sounds like you are OK with that.

 

[Dryver14]

No worries,

I'm not sure that using select case will be any easier to construct rather than separate macros if you are placing a button on every sheet anyway, tidier I suppose.

 

[bearcub]

Either way, I would have to create different message boxes but I thought I would use the select case just so I can get used to using it. The button is going on a userform so I thought this would be more efficient.

Michael

 

[Joe4]

The button is going on a userform so I thought this would be more efficient.

Ah, yes. That would make more sense. Didn't realize that you were using User Forms here (which is why I was asking where you planned to put the button).