asked on

Run subroutine when a cell changes - VBA in Excel

I want to run a routine that updates a graph when a specific cell is changed

At the moment, I can't get the Workbook_SheetChange to fire

To test it, I put the following in the sheet containing the cell that is changed by the user, just taken from the VBA help file

Private Sub Workbook_SheetChange(ByVal Target As Range)

    MsgBox ("A change in cell " & Target.Address)
    
End Sub

Open in new window

Then to try to make sure events are enabled, I put the following in the Workbook area

Private Sub Workbook_Open()

    Application.EnableEvents = True
    
End Sub

Open in new window

I saved the file and reopened it, which I presume will have triggered EnableEvents but nothing happens whe I change the value in the sheet concerned, or any other value on that sheet.

How do I get Workbook_Open to be triggered?

SOLUTION

Amick

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sjgrey

ASKER

Thanks but that didn't make any difference

I'm running Excel 2007 SP2

Peter Kwan

1) Did you see a toolbar saying "Security warning" and have an option button when you open the Excel file? If yes, click the option button and click "Enable this content".

sjgrey

ASKER

No there were no warnings and other VBA code in the file runs without any problem

SOLUTION

Peter Kwan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sjgrey

ASKER

That's what I had for my code in the first place - see initial post

I have been pressing Enter to change a value but just tried the tick and that doesn't do it either.

Something else must be wrong I think

ASKER CERTIFIED SOLUTION

TinTombStone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sjgrey

ASKER

Thanks

I put both subroutines in, one in the sheet and one in the workbook and it works

I'll experiemnet to see if I need both to make it happen but if you can tell me easily I'd be grateful

that's the only thing I can see is different from what I'd tried before but I may be mistaken

sjgrey

ASKER

There may have been something else going on as well but as soon as I inserted BOTH of the subroutines provided by TinTombStone it worked