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

CFrazier · Jun 30th 2004

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.

Jong · Jun 30th 2004

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:

Code

Private Sub Worksheet_Change(ByVal Target As Range)


     'First check to see if Changed cell is found in Range A1
     If Application.Intersect(Range(Target.Address), Range("A1")) Is Nothing Then
        if ActiveSheet.Range("A1").Value = 1 then
            call Macro1
        elseif ActiveSheet.Range("A1").Value = 2 then
            call Macro2
        end if
  end if


end Sub

Display More

Richie(UK) · Jun 30th 2004

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

CFrazier · Jun 30th 2004

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.

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

Participate now!

Share