Changing the ActiveSheet

[David]

I've got a workbook with several different sheets. I want a macro
that will select the required sheet depending on what cell is
highlighted.

For example there are cells for Jan, through to Dec. If the Mar cell
is highlighted, when the user clicks on the macro button I want it to
open the sheet for Mar and do some processing. I know how to do the
processing but how do you change what sheet is active based on this
kind of user action?

Thanks!

 

[Guest]

Hi David -

Here's a basic starting point:

Private Sub CommandButton1_Click()
Worksheets(ActiveCell.Value).Activate
End Sub

 

[David]

OK, this works:

sMonth = ActiveCell.Value

Worksheets(sMonth).Activate

Another question, how do I used wildcards in Excel?

For example, if the active cell was "Mar" but the worksheet was Mar
'07, how could I get it to still open the right worksheet?

I tried adding a * after the Worksheets(sMonth) but that didn't work,
any ideas?

 

[Guest]

The following activates the worksheet whose name starts with whatever is in
the activecell (sMonth):

Private Sub CommandButton1_Click()

sMonth = ActiveCell.Value

For Each ws In Worksheets
If InStr(1, ws.Name, sMonth) = 1 Then ws.Activate
Next 'ws

End Sub

 

[Mike Fogleman]

Take a look at the LIKE operator.

Mike F

 

[David]

Thanks for the help so far, it's working well!

Another query.

I have worksheets Apr, May, Jun etc.

Sometimes when I go into Jun worksheet for example, I might need to
also check what was in the previous month, is there an easy way of
doing this rather than saying if it's Jun then check May etc etc? Are
worksheets numbered even though they have names?

Thanks again!

 

[Guest]

Hi David -

Yes, worksheets have an Index property and they are numbered sequentially
from left to right, starting with 1. So, these statements may work for you:

'Check the value in cell A1 of the sheet before the activesheet
MsgBox Worksheets(ActiveSheet.Index - 1).Range("A1")

'Activate the sheet before the activesheet
Worksheets(ActiveSheet.Index - 1).Activate