Solved

Excel97 - Worksheet Change Event (?)

Posted on 2002-03-04
29
866 Views
Last Modified: 2012-05-04
On a data selection worksheet, I have a cell that contains a data validation drop down list (not a form control). When the value in this cell changes I would like some code to run that will clear some input fields.

I have assigned some code to the "Worksheet_Change" event, but this only works when a change is made to the cell via the keyboard. If I use the mouse to select a different item from the list then this action is ignored by the change event.

I also tried basing the code on a different cell containing a formula based on this selected item. However, the change event does not seem t owork on formulated cells either.

How can I get the code to run when I select a new item from the data validation drop down list?

Thanks.
0
Comment
Question by:SHardy
  • 10
  • 6
  • 5
  • +4
29 Comments
 
LVL 8

Expert Comment

by:starl
Comment Utility
*listening*

hiya S :-)
0
 
LVL 4

Expert Comment

by:WolfgangKoenig
Comment Utility
Do you mean a Forms.ComboBox Object ??

WoK
0
 
LVL 1

Author Comment

by:SHardy
Comment Utility
Hi Tracy! Keeping yourself busy? Not gonna have a stab at this one? :0P

WoK:
No I don't. I have not placed ANY kind of control on the sheet.
I have simply added data validation to the cell, and linked it to a list on another sheet. I have checked the "in-cell dropdown" box to show as a dropdown list.
0
 
LVL 4

Expert Comment

by:WolfgangKoenig
Comment Utility
I have to species of controls in excel:
---------------------------------------
1) formular controls
and
2) control elements toolbox

For the 1) you can assign an macro to the combobox with
a right mouse click on the control (context menu)

For the 2) you can define a routine in VBA:
Private Sub ComboBox1_Change()
...
End Sub

Will this answer your question?

Best regards
WoK



0
 
LVL 4

Expert Comment

by:WolfgangKoenig
Comment Utility
Ok, i have understood you problem ....
0
 
LVL 4

Expert Comment

by:WolfgangKoenig
Comment Utility
I would say your way no chance ...
, but you can use the formular controls, which do the same thing and you can assign a macro to the control ...
(This is not a dialog ole control ...)

;)
WoK
0
 
LVL 22

Expert Comment

by:Dreamboat
Comment Utility
Hi simon. What do you mean by *clear input forms*?

User forms?
0
 
LVL 11

Expert Comment

by:LambertHeenan
Comment Utility
Simple enough...

On the worksheet that has the validated drop down list, create a calculation that is based on the validated cell,

e.g. =C1 & " "  ' assuming C! is the cell with the validation

Then you can create a Worksheet.Calculate() event  handler. This event DOES fire when you select from the dropdown list, though the Change event does not (who knows why!).

Youre code will will have to distinguish between a calculation as a result of the dropdown selectiong and any other calculation. Acting on the former and ignoring the latter.
0
 
LVL 8

Expert Comment

by:starl
Comment Utility
nope, Simon - this one's over my head.. haven't gotten too much into controls on forms. Have a co-worker who has, but he isn't in today.
0
 
LVL 22

Expert Comment

by:ture
Comment Utility
SHardy,

Strange. It works for me. This is what I've done:

1. Started Excel 2000 SR1
2. Entered a list of fruits in H1:H4 on Sheet1
3. Selected cell A1 on Sheet1
4. Data - Validation
   Allow: List
   Source: =$H$1:$H$4
   OK
5. Right-Clicked the Sheet1 tab and selected "View code"
6. Entered this code:
   Private Sub Worksheet_Change(ByVal Target As Range)
     MsgBox "Changed cell " & Target.Address
   End Sub
7. Switched back to Excel

Now, if I change the value of cell A1 by using the dropdown list and the mouse, the VBA code DOES execute.

Ture Magnusson
Karlstad, Sweden
0
 
LVL 16

Expert Comment

by:sebastienm
Comment Utility
ture,
I tried the _Change event on a cell with List-data-validation:
- Excel2000-SR1: behaves as you say ( _Change event
 works in any case)
- Excel97-SR2: behaves as SHardy says: changing the value
by picking from the list (Mouse or ALT+UP/DOWN) does NOT
fire the event, while it does when cell directly edited
from keyboard.

Regards,
Sébastien.
0
 
LVL 8

Expert Comment

by:starl
Comment Utility
fyi: SHardy has excel97 (he's probably getting tucked in about now :-)

hmm something wrong in 97 fixed in 2000 (gee, where haven't we seen that!).. work around? additional code to add???
0
 
LVL 8

Expert Comment

by:starl
Comment Utility
by using
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

I can get it to do something (in this case ture's msgbox) when the cell is clicked on...but that means it happens before the contents are actually changed.
0
 
LVL 8

Expert Comment

by:starl
Comment Utility
or, with that pointer in another cell, use:
Worksheet_Calculate
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 16

Expert Comment

by:sebastienm
Comment Utility
No extra code needed.
Use the method proposed by WolfgangKoenig.

The formular controls are the easiest to use
(menu View>Toolbars>Forms). CHoose a combobox,
place it on the sheet. To assign its properties,
just right-click on it:
- use 'Assigne Macro...' to call your macro when value
  changes.
- use 'Format Control...' to set InputRange
  (your list range)

Regards,
Sebastien
0
 
LVL 1

Author Comment

by:SHardy
Comment Utility
Hmmm. Looks like an argument for Office2k. ;)

I do know about adding the combo boxes and assigning code, but I really don't want to use these. I shouldn't have used the word "form". What I was referring to was simply an area on a worksheet that I want the user to enter data into.

At the moment I am just using a single record, but what I am planning on is a multi-record input into a tabular format on a worksheet (fields going left to right and records going top to bottom). For a particular field I want a dropdown list (via data validation). When a different value is chosen from a dropdown I want to clear the values from a couple of fields (cells) for that record.

Seeing as the change event returns the address of the changed cell I could've got the row number from this address and used this to clear the relevant cells... if it worked in 97.

I hope this illustrates clearly why I'd rather use data validation than combo controls. Using controls I'd have to write code for each control. Also, this would make it a bit trickier for adding more records by code.

At the moment it looks like LambertHeenan is going along the write lines for finding a way around it. However, I now need to be able to identify the cell that causes the Worksheet_Calculate event to run.
0
 
LVL 1

Author Comment

by:SHardy
Comment Utility
The write lines?

Surely I meant "the RIGHT lines"? :0P
0
 
LVL 4

Expert Comment

by:WolfgangKoenig
Comment Utility
There are no other solution is seems to be a excel 97 bug and therefore you must use a another technic.

Here is a simple routine that fulfill your needs:

Sub AddComboCell(ComboCell As String, ListRange As String)
Dim Cellx As Double
Dim Celly As Double
Dim Cellw As Double
Dim Cellh As Double

Cellx = Range(ComboCell).Left
Celly = Range(ComboCell).Top
Cellw = Range(ComboCell).Width
Cellh = Range(ComboCell).Height

    ActiveSheet.DropDowns.Add(Cellx, Celly, Cellx, Celly).Select
    Selection.ShapeRange.LockAspectRatio = msoFalse
    Selection.ShapeRange.Height = Cellh
    Selection.ShapeRange.Width = Cellw
    With Selection
        .Placement = xlMoveAndSize
        .PrintObject = True
    End With
    With Selection
        .ListFillRange = ListRange
        .LinkedCell = ""
        .DropDownLines = 8
        .Display3DShading = False
    End With
    Selection.OnAction = "MyRoutine"
End Sub

Sub TestMacro()
  Call AddComboCell("G1", "$B$1:$B$5")
End Sub


For "MyRoutine" you must replace your handler for the selection change event ...
;)
WoK

0
 
LVL 4

Expert Comment

by:WolfgangKoenig
Comment Utility
...Using controls
I'd have to write code for each control. Also, this would make it a bit trickier for adding more records
by code...

This should be solved through this routine ... ;o)

0
 
LVL 1

Author Comment

by:SHardy
Comment Utility
So each dropdown would run the same routine. So how would that routine know which row it needs to clear data from?

Presumably this would mean knowing on which row each and every individual dropdown resides. Testing for which dropdown called the routine and running a case statement against this to make sure the correct row is cleared. So when a new record is added, and a new dropdown created, the routine would need to be amended to add the new dropdown to the case statement.

All sounds like a lot of work. Is it not possible to pinpoint which cell(s) change(s) during calculation, or which cell triggered a recalc?
0
 
LVL 4

Expert Comment

by:WolfgangKoenig
Comment Utility
First:
...
Is it not possible to pinpoint which cell(s) change(s) during calculation, or which cell triggered a recalc?
...

No there is no adequat solution because the bug in Excel 97..

Second:
Here is the sophisticated complete solution for your problem (hard work beside: there where some undocumented object types which now works perfectly ...)

'__________________________________________________________
Private CoupledFieldsCol As New Collection
Private ComboCellCol As New Collection

Sub AddComboCell(ComboCell As String, ListRange As String, CoupledFields As String)
Dim Cellx As Double
Dim Celly As Double
Dim Cellw As Double
Dim Cellh As Double
Dim aDropDown As DropDown

   Cellx = Range(ComboCell).Left
   Celly = Range(ComboCell).Top
   Cellw = Range(ComboCell).Width
   Cellh = Range(ComboCell).Height

   Set aDropDown = ActiveSheet.DropDowns.Add(Cellx, Celly, Cellx, Celly)
   ComboCellCol.Add aDropDown
   CoupledFieldsCol.Add CoupledFields, aDropDown.Name
   
   aDropDown.Select
   With Selection
       .ShapeRange.LockAspectRatio = msoFalse
       .ShapeRange.Height = Cellh
       .ShapeRange.Width = Cellw
       .Placement = xlMoveAndSize
       .PrintObject = True
       .ListFillRange = ListRange
       .LinkedCell = ""
       .DropDownLines = 8
       .Display3DShading = False
       .OnAction = "ComboCellHandler"
   End With

End Sub

Sub ComboCellHandler()
Dim compDropDown As DropDown
Dim CoupledFieldsRange As Range
 
  For Each aDropDown In ActiveSheet.DropDowns
      For Each compDropDown In ComboCellCol
        ' Found the dropdown element to process
        If aDropDown.Name = compDropDown.Name Then
           Set CoupledFieldsRange = Range(CoupledFieldsCol(compDropDown.Name))
        ' Clear the coupled fields
           CoupledFieldsRange.Clear
        End If
      Next
   Next

End Sub


Sub TestMacro()
 Call AddComboCell("G1", "$B$1:$B$5", "$A$1:$A$3")
End Sub

'__________________________________________________________



Now i will explain the function AddComboCell:
arg1:  cell to validate as String for instance: "G1"
arg2:  cell list as String for instance: "$B$1:$B$5"
arg3:  coupled cell list as String for i.: "$A$1:$A$3"

The coupled cells will now be cleared if an entry is choosen form the cell to validate.
You can predefine another activity on this code point:
...
' Clear the coupled fields
  CoupledFieldsRange.Clear
...

CoupledFieldsRange is a range object that holds the
coupled cells ...

Hope that finally this solution fill your needs
Best regards
WoK
0
 
LVL 4

Expert Comment

by:WolfgangKoenig
Comment Utility
Sorry the code was not correct at all here is the complete
!tested! version:
Private CoupledFieldsCol As New Collection
Private ComboCellCol As New Collection
Private ComboCellIndexes As New Collection

Sub AddComboCell(ComboCell As String, ListRange As String, CoupledFields As String)
Dim Cellx As Double
Dim Celly As Double
Dim Cellw As Double
Dim Cellh As Double
Dim aDropDown As DropDown

   Cellx = Range(ComboCell).Left
   Celly = Range(ComboCell).Top
   Cellw = Range(ComboCell).Width
   Cellh = Range(ComboCell).Height

   Set aDropDown = ActiveSheet.DropDowns.Add(Cellx, Celly, Cellx, Celly)
   ComboCellCol.Add aDropDown
   ComboCellIndexes.Add aDropDown.ListIndex, aDropDown.Name
   CoupledFieldsCol.Add CoupledFields, aDropDown.Name
   
   aDropDown.Select
   With Selection
       .ShapeRange.LockAspectRatio = msoFalse
       .ShapeRange.Height = Cellh
       .ShapeRange.Width = Cellw
       .Placement = xlMoveAndSize
       .PrintObject = True
       .ListFillRange = ListRange
       .LinkedCell = ""
       .LinkedCell = ""
       .DropDownLines = 8
       .Display3DShading = False
       .OnAction = "ComboCellHandler "
   End With

End Sub


Sub ComboCellHandler()
Dim compDropDown As DropDown
Dim CoupledFieldsRange As Range
 
  For Each aDropDown In ActiveSheet.DropDowns
    For Each compDropDown In ComboCellCol
        ' Found the dropdown element to process
      If aDropDown.Name = compDropDown.Name Then
        If aDropDown.ListIndex <> ComboCellIndexes(compDropDown.Name) Then
           Set CoupledFieldsRange = Range(CoupledFieldsCol(compDropDown.Name))
        ' Clear the coupled fields
           CoupledFieldsRange.Clear
        ' Set the new choosen index
           ComboCellIndexes.Remove (compDropDown.Name)
           ComboCellIndexes.Add aDropDown.ListIndex, compDropDown.Name
        End If
      End If
      Next
   Next

End Sub


Sub TestMacro()
 Call AddComboCell("A1", "$B$1:$B$3", "$C:$C")
 Call AddComboCell("A2", "$B$4:$B$6", "$D:$D")
 Call AddComboCell("A3", "$B$7:$B$9", "$E:$E")
End Sub



'WoK 2002
0
 
LVL 1

Author Comment

by:SHardy
Comment Utility
WoK:

Please be assured that all your hard work is very much appreciated.

I have now tested your code and was very impressed. FYI I used a call like:

Call AddComboCell("B8", "NameList", "$D$8:$E$8")

so that it cleared the correct row only.

However, there were two problems that I came across:

(1) ALL combo boxes need to be added by code. My spreadsheet is setup with a default number of records, but with the ability to add more. This code was to be used when adding extra records. This isn't too much of a problem really. It would just mean adding the initial combos with a temporary macro. Problem solved.

(2) And this is the one that kills it - The code assigned to the combo boxes (ComboCellHandler) will not do anything once the file has been closed and reopened. The information regarding the "CoupledFields" is kept within a variable. Once the file has been closed the code is reset and all variables cleared. This means that any combo boxes will ONLY work during the session that they were created.

Does this mean creating a table somewhere with all the combo box references and reading them in everytime the file is opened?
0
 
LVL 22

Accepted Solution

by:
ture earned 100 total points
Comment Utility
SHardy,

I'm sorry that I didn't read the title of your question before posting my previous comment. As everyone has seen, the Worksheet_Change event isn't triggered when selecting a value from a "validatiin list" in Excel 97.

Here is a description of the work-around that I have used. I assume here that the "validation dropdowns" are all in column A and will never extend further down than A1000.

1. Start Excel with an empty workbook. Select Sheet1.
2. In any cell on Sheet1, enter a formula that calculates whenever changes are made to the sheet.
   I used this one: =TEXT(NOW(),"")
3. Add a number of "validation dropdowns" to cells in column A. You know how to do that.
4. Right-click the tab of Sheet1 and select "View Code"
5. Add this code (I hope that it's understandable):

Private Sub Worksheet_Calculate()
 
  'Declare variables
  Static OldValues As Variant
  Dim CurrentValues As Variant
  Dim rmax As Long
  Dim r As Long
  Dim x As Long
 
  'Set max number of rows to check
  rmax = 1000
 
  'Read current values of column A into array
  CurrentValues = Range("A1").Resize(rmax).Value
 
  'Run only if we have old values to compare with
  If Not IsEmpty(OldValues) Then
   
    'Loop through all rows of array
    For r = 1 To rmax

      'If the current value is different from old value
      'remember row number and get out of loop
      If CurrentValues(r, 1) <> OldValues(r, 1) Then
        x = r
        Exit For
      End If
     
    Next r
 
  End If
 
  'Remember values so that they are available
  'when this procedure is executed again
  OldValues = CurrentValues

  'Display x if it is not 0
  If x <> 0 Then MsgBox x

End Sub

6. Switch back to Excel and change some of the values in column A by using the dropdown lists.

Happy?

/Ture
0
 
LVL 22

Expert Comment

by:ture
Comment Utility
A small addition: To make this work the first time after the workbook is opened, add this code in ThisWorkbook:

Private Sub Workbook_Open()
  Sheet1.Calculate
End Sub

/Ture
0
 
LVL 4

Expert Comment

by:WolfgangKoenig
Comment Utility
Dear Hardy,
...
Does this mean creating a table somewhere with all the combo box references and reading them in everytime
the file is opened?
...
Yes i would think so... Here is the code th solve problem (2):

Add this code to the VBA modul (the position where the routine AddComboCells is placed,for instance "modul1"):
___________________________________________________________
Sub RemoveAllCombos(WsSheet As Worksheet)
Dim aDropDown As DropDown
 
For Each aDropDown In WsSheet.DropDowns
  aDropDown.Delete
Next

Set CoupledFieldsCol = Nothing
Set ComboCellCol = Nothing
Set ComboCellIndexes = Nothing
End Sub

___________________________________________________________

And add this code to the related spreadsheet that holds the data:
___________________________________________________________

Private Sub Worksheet_Activate()
 Call AddComboCell("A1", "$B$1:$B$3", "$C:$C")
 Call AddComboCell("A2", "$B$4:$B$6", "$D:$D")
 Call AddComboCell("A3", "$B$7:$B$9", "$E:$E")
End Sub


Private Sub Worksheet_Deactivate()
 Call RemoveAllCombos(Me)
End Sub

___________________________________________________________



That code would recreate the combos if needed and clear them when the worksheet is leaving by the user ---

Hope that solved the problem
Regards
WoK

0
 
LVL 4

Expert Comment

by:WolfgangKoenig
Comment Utility
I haved tested the solution and find it better to do the following.. use not the Worksheet Activate and Deactivate but the Workbook methode like this:
__________________________________________________________
Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Call RemoveAllCombos(Tabelle1)
End Sub

Private Sub Workbook_Open()
  Tabelle1.Activate
  Call AddComboCell("A1", "$B$1:$B$3", "$C:$C")
  Call AddComboCell("A2", "$B$4:$B$6", "$D:$D")
  Call AddComboCell("A3", "$B$7:$B$9", "$E:$E")
End Sub
__________________________________________________________

Here you must replace "Tabelle1" with the name of your
created worksheets perhaps "Table1" or so i don't know ...
then all works perfectly ...
(Don't forget the "Tabelle1.Activate" to place the combos at the right place ...)

When you want to use more than one worksheet let know me
this ... that would expand the code

WoK
0
 
LVL 1

Author Comment

by:SHardy
Comment Utility
WOW! It's no suprise that you are right up the top of that list. This is superb. They said it couldn't be done, but you've proved that it can. Very well deserved points.

WoK:

With your last amendment we would lose the current selections upon re-opening the file. This, unfortunately, is still a bit of a problem. We were nearly there though. I'm sure that you'll agree though that Ture's solution is ideal for what I was after.
However, I appreciate your efforts very much and will be posting you some points too. Cheers!
0
 
LVL 22

Expert Comment

by:ture
Comment Utility
SHardy,

Thanks for the points. I'm very glad that my suggestion was useful for you.

/Ture
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now