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
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
