Determine current worksheet

[Alias]

Hello,

I have several worksheets and want to know which one is current when Excel
is loaded. Worksheets are named for each month as such:

Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec.

I was thinking a Case Statement might be best approach, but don't know how
to code this in VBA. In each case I want to go to certain cell in each sheet
such as:

Jan = A5, Feb = B2, Mar = C11, etc.

Can anyone write example code to achieve this. Thanks in advance.

 

[Billy Liddel]

This is best don in the Worksheet Module. Right_click the Exccel Icon on the
File MenuBar (xl2003), select view code and paste in the code.

Option Explicit

Private Sub Workbook_Open()
Dim sWksName As String

sWksName = ActiveSheet.Name
If sWksName = "Jan" Then
Range("a5").Select
ElseIf sWksName = "Feb" Then
Range("B2").Select
ElseIf sWksName = "Mar" Then
Range("C11").Select

' more code here


End If

MsgBox "Activesheet is " & sWksName & "," & " Cell " & ActiveCell.Address

End Sub

Private Sub Workbook_SheetActivate(ByVal sh As Object)
Dim sWksName As String

sWksName = sh.Name
If sWksName = "Jan" Then
Range("a5").Select
ElseIf sWksName = "Feb" Then
Range("B2").Select
ElseIf sWksName = "Mar" Then
Range("C11").Select

' more code here


End If

MsgBox "You have selected " & sWksName & " Cell " & ActiveCell.Address

End Sub

'You will want to remove the msgbox or place an apostrophe before the line
after testing the code.

 

[Shane Devenshire]

You are correct, the best solution would be Select Case:

Private Sub Workbook_Open()
Select Case ActiveSheet.Name
Case "Jan": [A1].Select
Case "Feb": [B11].Select
'more code here
End Select
End Sub

this goes into the thisWorkbook object, not a sheet object.

 

[Scientific]

Shane, Billy,

Thanks to you both because both versions of code worked just great. The
Case statements are much easier to read and follow, but a major thanks to you
both for helping me with this one. I learned some different approaches to
accomplish the same thing, and that's worth an awful lot to me.

I tried both versions of your code using embedded pictures instead of cell
references but it doesn't seem to work, kept getting error messages. Why
won't the code work on pictures too?

-S

 

[Alias]

Shane, Billy,

Sorry for the post as Scientific, we are both one in the same.

Anyway, I figured out how to make it work with pictures by borrowing code
from other posts and adding it to what you posted earlier.

I am a happy camper and could not have done this myself without your help.
I'm deeply grateful to you, and many thanks again

-A and -S

 

[AltaEgo]

Sub CurSht()

MsgBox = ActiveSheet.Name

End Sub

 

[chris]

Well, you can figure out the active sheet using the following macro
code

Private Sub Workbook_Open()
MsgBox "Active sheet is" & ThisWorkbook.ActiveSheet.Name
End Sub

Chris