Click here for important information about

Macro dependent upon contents in another cell

Microsoft Excel 2003 (full product)
September 6, 2010 at 08:36:54
Specs: Windows XP

I apologise for my lack of knowledge first thing. I'm learning (pretty swiftly) and will appreciate ANY help that anyone can lend me! I'm using Excel 2003..

Cell E22 is a drop down. On the same worksheet, I have a button assigned with a macro in cell X25 (although I think my first issue is that it's not attached to any specific cell, it's just in the area of that cell number).

I need to assign a macro to that button that will send me to another worksheet within the workbook, but the tab I want it to send me to depends on what is chosen in the drop down list in cell E22.

So if the drop down menu options are "Alpha", "Bravo", "Charlie", etc and I choose "Charlie in the drop down menu and then click on the macro, I want to go to the worksheet called "Charlie".

Is this possible with a macro button? Is it possible at all?

Many many many thank yous in advance!


See More: Macro dependent upon contents in another cell

September 6, 2010 at 09:54:51

You don't need to assign a macro to a button.

You can use the change in value in cell E22 to directly trigger code that will take you to the selected worksheet.

Right-click the name tab of the worksheet containing E22 and select 'View Code'
Paste this into the large Visual Basic window:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHnd
If Target.Address = "$E$22" Then
    'activate the selected worksheet
    'activate a specific cell on that worksheet
End If
Exit Sub
'error handler
End Sub

If your list contains a non-existent worksheet name, the macro will jump to the error handler and close silently. You could add a message at that point such as
MsgBox "Worksheet " & Target.Text & " does not exist"

As this macro does not change any cell values, I have not included code to stop events while the macro is running.
If you decided to add code to this that changes a cell value, then you will need to include code to disable and then re-enable events.


Report •

September 7, 2010 at 01:23:31
I really appreciate your time Humar. I do actually need the macro to be attached to a button that the user has to physically press because there is a whole form to fill out, E22 just being one of the cells that needs completing. They then need to check everything is correct and then physically press something to the move to another sheet. Is this possible?

Report •

September 7, 2010 at 04:06:32

Here is the macro, modified to run from an embedded button on your worksheet. I have added a couple of warning messages - you can edit the text as required.

On the Worksheet, create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to 'Next' or something else suitable.
Right-click the button again and select View Code
In the code window that opens enter this:

Option Explicit
Private Sub CommandButton1_Click()
Dim wsEach As Worksheet
Dim blnFound As Boolean
Dim strSheet As String

On Error GoTo ErrHnd

'set Found flag to 'not found'
blnFound = False
'get sheet name from cell E22
strSheet = ActiveSheet.Range("E22").Text

'test that E22 contains a valid sheet name
'go through each worksheet in this workbook
For Each wsEach In ActiveWorkbook.Worksheets()
    'if found, flag as 'found'
    If strSheet = wsEach.Name Then blnFound = True
Next wsEach
If blnFound = False Or strSheet = ActiveSheet.Name Then
    'not found or it is 'this' worksheet - so show a message
    If strSheet = "" Then
        MsgBox "Select Worksheet name in cell E22 before proceeding"
        ElseIf strSheet = ActiveSheet.Name Then
        MsgBox "Cannot select this worksheet as sheet to jump to"
        MsgBox "Select a valid worksheet name in cell E22"
    End If
    Exit Sub
End If

'activate the worksheet selected in cell E22
'activate a specific cell on that worksheet
Exit Sub

'error handler
End Sub

Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option Explicit goes before Private Sub CommandButton1_Click().
Some lines of code have been split onto two lines for ease of viewing, using the line continuation character "_". This should work 'as is' just copy and paste, or you could remove the "_" and bring the code back to one line.

Click File - Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your workbook. This code has only been tested on a sample workbook, and it has not been tested in your environment, so test it to ensure that it works 'as expected'

Click the 'Next' button to run the macro.

You could add checks to this macro to check that key fields have been completed, and include additional warning messages.

For example:

'test cell A5
If ActiveSheet.Range("A5").Value = "" Then
    'No data entered in A5 - so show warning message
    MsgBox "Cell A5 must be completed before proceeding"
    Exit Sub
End If


Report •

Related Solutions

September 7, 2010 at 08:13:30
Oh that is incredibly intelligent of you!! I am so unbelievably grateful. If I knew who or where you are I would drop you round some home made lasagne (just happens to be what we're having tonight). You have made my week!

Just another side there a way for a command button to only appear after a certain cell has been filled? For example when someone types something in cell Q19 is there a macro or something that will then make the command button (you helped me with above) suddently appear? Or am I expecting too much?

You never know with technology these days if you've taken it one step too far. I like to assume it does everything including making hot chocolate and then I'm not limiting it's potential...:)

Report •

September 7, 2010 at 09:06:07

Glad to have been able to help and I love Lasagne...

So for my next meal - make a button appear.
Actually this just changes the visibility of a button.

From the Developer tab on the Ribbon, in Controls - Insert and select an ActiveX button.

Draw it on the worksheet and its name will appear in the address box, to the left of the formula bar. It will likely be "CommandButton1" - if not, note it's name. There's no need to add a macro to it at this stage.

Now right-click on the worksheet name tab and select 'View Code'
In the large Visual Basic window enter this:

Sub BtnVis_Not()
ActiveSheet.CommandButton1.Visible = False
MsgBox "OK"
ActiveSheet.CommandButton1.Visible = True
End Sub

If the name of the button is not "CommandButton1", change it in the code above (both occurrences) - but this won't work on a 'forms' button, e.g. "Button1"

From the Ribbon - select Macros in the Code section of the Developer tab.
Find and run "BtnVis_Not"

The Button will disappear, then click OK on the message box and it reappears.

Must run now, but tomorrow will include code for making this happen when a cell contains a specific word.

Let me know which cell and what text will trigger making the button visible.


Report •

September 7, 2010 at 11:08:30
Hello King of Excel,

I want the button to appear if any numbers are input into cell Q19.

Can you help (silly question really). It's beans on toast tomorrow night I'm afraid.

Thanks again,


PS I've just put another post on regarding printing specific pages dependent upon contents in cells....not sure whether you can help with that one as well?? Pwetty pwease!

Report •

September 8, 2010 at 04:09:40

I have written a small macro that responds to any change to cell Q19.
If Q19 contains a number, then the button "CommandButton1" is made visible.

As before, change the name of the button in the code if the button has another name.

Right-click the name tab of the worksheet containing the button, and select 'View Code'
In the VB window paste this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' as this macro does not change any cells
' events have not been disabled.

'test if changed cell is Q19
If Target.Address = "$Q$19" Then
    'It is Q19 - so lets test its contents
    If IsNumeric(Target.Value) Then
        'it contains a number - so display the button
        CommandButton1.Visible = True
    End If
End If
End Sub

Of course there has to be a way to hide the button in the first place, so my suggestion is that it is hidden as soon as the worksheet containing it is selected. To do this, from the Visual Basic window, look in the 'Project Explorer' pane (usually on the left - if it is not visible select 'Project Explorer' from the Menu bar 'View' menu).
Find the name of your workbook: VBAProject(MyWorkbook.xlsm) and double click 'ThisWorkbook' under it.
In the Visual Basic code window enter this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'test if the worksheet activated is Sheet1
If Sh.Name = "Sheet1" Then
    'it is Sheet1 - so hide the button
    Worksheets("Sheet1").CommandButton1.Visible = False
End If
End Sub

Change the name of the worksheet from "Sheet1" to the name of the worksheet containing the Button (two occurrences)

Now on switching to Sheet1 the Button is hidden.
Enter a number in Q19 and it becomes visible.

Hope this helps - if so remember that I just love Beans on Toast


PS If you think that a different way of hiding the button would be appropriate, please let me know.

Report •

September 10, 2010 at 09:41:03

I'm trying to work out this disappearing button act but I'm stuck at the first hurdle. I'm working off Excel there a developer tab? If so, how do I find it? And if not, where do I find an ActiveX button. All I can find is the form one.



Report •

September 10, 2010 at 10:33:27

My mistake, I gave you instructions for Excel 2007 or later.

For Excel 2003:

Create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select 'Command Button Object -> Edit' and change the name as required.
Right-click the button again and select View Code
Enter your button code - not required to test the button visible/not visible actions.

To exit the button design mode click on the first icon on the Control Toolbox toolbar.

Check the button name as before - "CommandButton1" is the default for the first button and edit code if required.
The rest of the code works as previously posted.


Report •

September 24, 2010 at 12:16:56

thanks for sharing this code. i have a question though.
lets say for example i have sheet1, sheet2, sheet3..
I typed "sheet2" on the e22 cell then clicked on the macro button, the sheet2 is now activated, but I want to hide the "non-activated sheets". is there a way to hide sheet1 and sheet3 and the only visible sheet is the one I activated? (the worksheet written on e22)

Thanks in advance.

Report •

Ask Question