Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Whorksheet Calculate Event Q
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Whorksheet Calculate Event Q
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) "John" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Whorksheet Calculate Event Q
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 he http://www.contextures.com/xlDataVal08.html#Change John wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Turn off calculate event | Excel Discussion (Misc queries) | |||
Worksheet Calculate Event | Excel Programming | |||
OnTime event not firing in Workbook_Open event procedure | Excel Programming | |||
SheetChange event starts before .calculate finished | Excel Programming | |||
SheetChange event starts before .calculate finished | Excel Programming |