Quick Links ▼

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft VBA Training and help » vba courses london - Perform a macro whenever the Sheet is updated

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

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

Resolved · Low Priority · Version Standard

Reply Thu 18 Jan 2007, 16:24Delegate 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

For upcoming training course dates see: Pricing & availability

Reply Tue 23 Jan 2007, 09:38 Edited on Tue 23 Jan 2007, 09:39Trainer 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

VBA articles

· How To Find A Good VB For Access Trainer
· Introduction to Visual Basic for Applications
· VBA - Not Just For Programmers
· Starting Out With Excel 2007 VBA Using The Macro Recorder
· Step Into The Unknown With VBA

Vba tip:

Use VbNullString instead of

When needing to default a String variable back to it's default of "" use vbNullString as in the following example:

Sub ClearText()

Dim strEmpName As String

strEmpName = "John Smith"
MsgBox strEmpName

strEmpName = vbNullString
MsgBox strEmpName

End Sub

View all VBA hints and tips