Launch Macro on Result of Validation

[Darin Kramer]

Howdie...

I have a cell that is validated, allowing user a choice of one of three
items. I have then written a macro to do something if he chooses option
1.
How do I get the Macro to run automatically when the user chooses the
option?

(I have tried referencing another cell with IF statemnts to get the
right answer, but still doesnt launch the macro)

Effectively I need to tell Excel to check the result of a cell every
time a choice is made in the valiation box....

Any ideas most welcome...

Regards

Darin

 

[Guest]

Hi
you need a worksheet event procedure (e.g. worksheet_change). See:
http://cpearson.com/excel/events.htm

 

[Darin Kramer]

Thanks it sounds like the right thing... I have tried to just insert it
as a new module within VBA, but it doesnt seem to work...? whre do I
need to insert it?

 

[Guest]

In VBA you need to double click the worksheet containing the cell.

This should cause a new code window to pop up.

Type it in there.

Nick Shinkins

 

[Frank Kabel]

Hi
right-click on the tab name and choose 'Code'

 

[Darin Kramer]

Frank, NEARLY THERE...

If I enter text in the cell, the Macro runs perfectly.
However the cell is a formulae, based on validation.
so user does his selection (using validation and selects option1). -
Cell A1 then displays option 1. MACRO does not run!!!!
If i merely edit the Cell (f2) and press enter then Macro runs. I need
to tell it to refresh or recalculate or something...?

Here is code as I have it.... WHAT CAN I ADD...????? THANKS SO MUCH

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Sheets("Answers").Select

If Target.Address = "$E$4" Then
Application.EnableEvents = False
If Target.Value = "No" Then
Call FAS_Hide
End If

If Target.Value = "FAS" Then
Call FAS_Unhide

Application.EnableEvents = True
End If
End If
End Sub

 

[Darin Kramer]

Any one got any ideas on how to tell Excel to Refresh a sheet (achieve
the same effect as pressing F2) ...?
(or actually just to refresh a cell....

 

[Frank Kabel]

Hi
F2 does not refresh a sheet?. Maybe ou have to turn on automatic
calculation.
could you explain what exactly is not working?

 

[Frank Kabel]

Hi
for a calculation you have to use the worksheet_calculate event

 

[Darin Kramer]

Frank... Whats wrong...? - well simply put The macro is not running on
the cell until I edit that cell (by pressing F2) or if I enter text into
that cell instead of a formaulae.

To summarise (from the beginning)

User selects from a drop down validation box an option (say option 1)

This displays a word(FAS in this example) in another Cell, say in E4.
I want the attached EVENT to run if cell E4 has a value - so if E4 has
the value FAS, I want to call a Macro called FAS_UNhide. If E4 has ANY
OTHER VALUE (using a simple IF I can make this other value a NO), then I
want it to run another MACRO (called FAS_hide)

Using the Code as reflected below nothing happens UNTIL I MANUALLY GO
INTO CELL E4 and press F2(ie function key 2). I need the event to run
when the cell contents are changed....

Attached code

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
With Worksheets(1)

End With
End Sub


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Sheets("Answers").Select

If Target.Address = "$E$4" Then
Application.EnableEvents = False
If Target.Value = "No" Then
Call FAS_Hide
End If

If Target.Value = "FAS" Then
Call FAS_Unhide

Application.EnableEvents = True
End If
End If
End Sub

 

[Darin Kramer]

Can you advise as to the syntax / wording ?

 

[Frank Kabel]

Hi
as stated in the othe rresponse. You ned to use the worksheet_calculate
event handler