Auto-executing a macro based on the contents of a cell

  • Ok, so I'm a newbie at VBA. Here is what I want to be able to do:


    I have a cell connected to either a drop-down button or perhaps a formula (not sure if it matters that the cell changes based on a button or a formula, but I'm putting that in just in case).


    What I would like to have happen is that a macro runs based on the contents of that cell.


    For example, if the contents of cell "A1" change to a 5, I might want to change to full screen mode. If "A1" changes to a 6, I might want a UserForm to automatically pop-up, etc.


    So, I am able to get bits and pieces, but can't get it to work quite right, particularly the part about getting it to run automatically upon a change in the cell. Again, cell A1 might be an IF or OFFSET statement itself.


    Thanks in advance & please remember that I know very little about VBA.


    Curtis.

  • The best way I know of to execute a command when a cell is changed is to utilize the built in events provided by excel. To do so, you can modify the Sheet object that needs to check for a change in your VBA editor.


    For example, if your sheet is called "Data", then you should edit the "Data" object in your VBA editor.


    Add the following:


  • Hi Curtis,


    What you need is some event code for the worksheet.


    Right-click on the sheet tab and select 'View Code'. Then paste in the following example:

    Code
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        If Target.Address = "$A$1" Then
            Select Case Target.Value
                Case Is = 1: MsgBox "Your sub here"
                Case Is = 2: MsgBox "Another one here"
            End Select
        End If
    End Sub

    This looks at changes to A1 and acts according to the value (only for 1 and 2 at present, just add more to suit). If you want to work with formulas as the driver then have a look at the Calculate event.


    HTH

  • Woo hoo! This is working.


    Any ideas about how to get it to work if the cell that the macro runs off is a formula?


    Again, thanks in advance.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!