Changing the ActiveSheet
 

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!

Changing the ActiveSheet
 

Hi David -

Here's a basic starting point:

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

"David" wrote:

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!

Changing the ActiveSheet
 

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?

Changing the ActiveSheet
 

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

"David" wrote:

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?

Changing the ActiveSheet
 

Take a look at the LIKE operator.

Mike F
"David" wrote in message
ps.com...
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?

Changing the ActiveSheet
 

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!

Changing the ActiveSheet
 

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

---
Jay

"David" wrote:

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!