Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet _Calculate event handler_how to use
Here is the problem...
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 an 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.... - NOW I THINK THE PROBLEM IS BECAUSE I NEED TO USE THE worksheet_calculate event handler - BUT I DONT KNOW HOW TO..... WHAT NEEDS changin BELOW...? I would greatly appreciate any help Attached code 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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet _Calculate event handler_how to use
So you use a combobox / listbox with linked cell as "E4" ? You can put the code in the combobox_change event. In designed mode, double click on the combobox and it will take you to the combobox_change event procedure. Write your code in this event procedure. Private Sub ComboBox1_Change() Dim userOption as string userOption = Worksheets("name_of_the_sheet_here").Range("E4").V alue If userOption = "FAS" '.. and so on, your code. End Sub Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet _Calculate event handler_how to use
Thanks - Im just using normal data validation, not a list box... maybe I will try your way... sounds like it will work... thanks very much ( I may get back to you.. :) *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet _Calculate event handler_how to use
Darin,
Do you have Excel97 by any chance? Excel 97 doesn't detect achange in a drop-down list. What you need to do is link a cell to E4 (=E4), and use the Worksheet_Calculate event to text the activecell (which will still be E4) Private Sub Worksheet_Calculate() Dropdown_Change ActiveCell End Sub Private Sub Dropdown_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 -- HTH RP (remove nothere from the email address if mailing direct) "Darin Kramer" wrote in message ... Here is the problem... 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 an 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.... - NOW I THINK THE PROBLEM IS BECAUSE I NEED TO USE THE worksheet_calculate event handler - BUT I DONT KNOW HOW TO..... WHAT NEEDS changin BELOW...? I would greatly appreciate any help Attached code 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet _Calculate event handler_how to use
Well!, if you are using only the validation with source list (or so),
then the worksheet_change event to trigger your code. Selecting a value from the dropdown is actual 'WorkSheet_Change" event, not 'Calculate' event. (Calculate event may occur as a consequence if you have any further formula involving the cell in question.) 1. Did you enter the code in Sheet1 - Worksheet_Change event procedure as it appears in VBE (Class module) or not? 2. Check that when you are trying to fire your code, you are not still in Design mode. Exit Design mode and try. Sharad *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with worksheet event | Excel Worksheet Functions | |||
Excel VBA - Need help with a _calculate event | Excel Programming | |||
Event Procedures: Event on Worksheet to fire Event on another Worksheet | Excel Programming | |||
Copying Worksheet triggers Click event of combobox on another worksheet | Excel Programming | |||
macro to apply worksheet event to active worksheet | Excel Programming |