vba courses london - Perform a macro whenever the Sheet is updated | VBA forum

Resolved · Low Priority · Version Standard

Thu 18 Jan 2007, 16:24 Delegate Julian said...

Julian has attended:
Excel VBA Intro Intermediate course

Perform a macro whenever the Sheet is updated

Hi there,
Everytime I change a drop down box or any data on the sheet changes I want a macro to execute. The macro is in place of a 'nested IF' formula as they are limited to 7 x IFs.

I remember from the course that if you put a proceedure in the 'ThisWorkbook' module it will execute when anything on a specified sheet is updated. but I cant make it work. I tried putting the following in:

Sub auto_change()
Range("C26").Select
If ActiveCell = 10 Then
Range("D26") = 0
ElseIf ActiveCell = 20 Then
Range("D26") = 1
ElseIf ActiveCell = 21 Then
Range("D26") = 2
ElseIf ActiveCell = 22 Then
Range("D26") = 3
ElseIf ActiveCell = 23 Then
Range("D26") = 4
ElseIf ActiveCell = 30 Then
Range("D26") = 5
ElseIf ActiveCell = 31 Then
Range("D26") = 6
ElseIf ActiveCell = 32 Then
Range("D26") = 7
ElseIf ActiveCell = 33 Then
Range("D26") = 8
End If
End Sub

Many thanks

julian

Tue 23 Jan 2007, 09:38 Edited on Tue 23 Jan 2007, 09:39 Trainer Carlos said...

RE: Perform a macro whenever the Sheet is updated

The macro won't work on its own. All you have done is save it in the Workbook's module. To run the code:

On the code page for the relevant worksheet

Create an event procedure for the Worksheet called

Worksheet_SelectionChange

Call the macro from it OR Copy the code into the procedure

If it still doen't work with this event, then try the Event procedure

Worksheet_Change