Excel97 - Worksheet Change Event (?)

*listening*

hiya S :-)

Do you mean a Forms.ComboBox Object ??

WoK

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.

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

Ok, i have understood you problem ....

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

Hi simon. What do you mean by *clear input forms*?

User forms?

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.

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.

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

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.

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???

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.

or, with that pointer in another cell, use:
Worksheet_Calculate

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

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.

The write lines?

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

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

...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)

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?

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

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

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?

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

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

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

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!

SHardy,

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

/Ture