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!

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!

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!

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!

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!