Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying new activesheet after other activesheet is hidden? | Excel Programming | |||
activesheet | Excel Programming | |||
Get ActiveSheet name in VB | Excel Programming | |||
ActiveSheet | Excel Programming | |||
ActiveSheet | Excel Programming |