Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Launch Macro on Result of Validation
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Launch Macro on Result of Validation
Hi
you need a worksheet event procedure (e.g. worksheet_change). See: http://cpearson.com/excel/events.htm "Darin Kramer" wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Launch Macro on Result of Validation
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? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Launch Macro on Result of Validation
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 "Darin Kramer" wrote: 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? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Launch Macro on Result of Validation
Hi
right-click on the tab name and choose 'Code' -- Regards Frank Kabel Frankfurt, Germany Darin Kramer wrote: 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? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Launch Macro on Result of Validation
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Launch Macro on Result of Validation
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.... *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Launch Macro on Result of Validation
Hi
F2 does not refresh a sheet?. Maybe ou have to turn on automatic calculation. could you explain what exactly is not working? -- Regards Frank Kabel Frankfurt, Germany "Darin Kramer" schrieb im Newsbeitrag ... 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.... *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Launch Macro on Result of Validation
Hi
for a calculation you have to use the worksheet_calculate event -- Regards Frank Kabel Frankfurt, Germany "Darin Kramer" schrieb im Newsbeitrag ... 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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Launch Macro on Result of Validation
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Launch Macro on Result of Validation
Can you advise as to the syntax / wording ? *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Launch Macro on Result of Validation
Hi
as stated in the othe rresponse. You ned to use the worksheet_calculate event handler -- Regards Frank Kabel Frankfurt, Germany "Darin Kramer" schrieb im Newsbeitrag ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Launch of Macro | Excel Discussion (Misc queries) | |||
How do I Launch a Macro on a different sheet | Excel Programming | |||
Can you use an IF statement to launch a macro? | Excel Programming | |||
auto-launch a MACRO? | Excel Programming | |||
Launch Macro in Access via Macro running in Excel??? | Excel Programming |