Whorksheet Calculate Event Q

[John]

Can anyone direct me on a simple macro code that will call another macro
(that clear cells) when a value in a cell, say B10 changes to anything. This
value in B10 comes from a data validation drop down. I believe I need a
worksheet_calculate event, I've loked at a few sites http://www.cpearson.com
being one but I'm confused about how to create one. I need it working from
Excel97 and 2000 in case that makes any difference


Thanks

 

[Bob Phillips]

John,

Because you want it to work with Excel 97, you have to use the Calculate eve
nt as you say. But you need to do something to trigger that event, so you
need to set a linked cell to your dropdown cell, just a simple =A1. Then
when A1 changes, the linked cell will change which will force a calculate.
The dropdown cell will still be the activecell, so you can pass that to any
routine. Here is some sample code that I have taken from one of my apps

Private Sub Worksheet_Calculate()
Dropdown_Change ActiveCell
End Sub

Private Sub Dropdown_Change(ByVal Target As Range)
Dim oFoundCell As Range
Dim iTargetCol As Long

If Not Intersect(Range("List1"), Target) Is Nothing Then
If Target.Count = 1 Then

With data.Range("List1Values")
Set oFoundCell = .Find(what:=Target.Value, _
LookIn:=xlValues)
If oFoundCell Is Nothing Then
MsgBox "Critical error"
Exit Sub
End If
End With

'load the List2 dropdown and set the default to item 1
iTargetCol = oFoundCell.Column - 1
CreateList2ValidateList Target.Offset(1, 0), "=List2_" &
iTargetCol, Target
Target.Offset(1, 0).Value = data.Range("List2_" &
iTargetCol).Value
End If

End If

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Debra Dalgleish]

Selecting an item from a data validation list in Excel 97 will only
trigger an event if the list is delimited, not if the list is stored on
a worksheet.

There's information here:

http://www.contextures.com/xlDataVal08.html#Change