Solved

Excel97 - Worksheet Change Event (?)

Posted on 2002-03-04
29
874 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
ID: 6839153
*listening*

hiya S :-)
0
 
LVL 4

Expert Comment

by:WolfgangKoenig
ID: 6839291
Do you mean a Forms.ComboBox Object ??

WoK
0
 
LVL 1

Author Comment

by:SHardy
ID: 6839313
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
ID: 6839326
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
ID: 6839345
Ok, i have understood you problem ....
0
 
LVL 4

Expert Comment

by:WolfgangKoenig
ID: 6839382
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
ID: 6839581
Hi simon. What do you mean by *clear input forms*?

User forms?
0
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 6839603
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
ID: 6839650
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
ID: 6839661
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
ID: 6839872
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
ID: 6839894
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
ID: 6839906
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
ID: 6839928
or, with that pointer in another cell, use:
Worksheet_Calculate
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 16

Expert Comment

by:sebastienm
ID: 6839939
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
ID: 6841133
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
ID: 6841135
The write lines?

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

Expert Comment

by:WolfgangKoenig
ID: 6841221
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
ID: 6841222
...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
ID: 6841317
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
ID: 6841728
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
ID: 6841826
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
ID: 6843801
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
ID: 6843824
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
ID: 6843833
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
ID: 6843872
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
ID: 6843888
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
ID: 6844319
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
ID: 6844357
SHardy,

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

/Ture
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
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 and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

912 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

20 Experts available now in Live!

Get 1:1 Help Now