Sheet event doesn't work in '97

  • Thread starter Thread starter Gareth
  • Start date Start date
G

Gareth

Can anyone help?

I have the following code which works fine on 2000 but in work we have '97
and the sheet event doesn't fire when selecting items from a drop down
valuidation list in column F.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Column = 6 Then
If Target.Offset(0, -5).Value <= Worksheets("Sheet2").Range("A1").Value
Then
If Right(Target.Value, 1) = "^" Then
MsgBox "Error, you must choose one without a roof on!"
End If
ElseIf Target.Offset(0, -5).Value >
Worksheets("Sheet2").Range("A1").Value Then
If Right(Target.Value, 1) <> "^" Then
MsgBox "Error, you must choose one with a roof on!"
End If
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

This check is very important, is there any other way to do it?

Thanks in advance.

Gareth
 
Your code works as expected for me in XL97. However are
you sure you want this in a "Worksheet_Change" event
rather than in "Worksheet_SelectionChange"

Not sure why you are disabling events. If events have not
been re-enabled for any reason that would disable your
event code. If you think your code is not working try
this in a normal module:

MsgBox Application.EnableEvents

Regards,
Sandy
savituk yahoo co uk
 
"In Excel 97, selecting an item from a Data Validation dropdown
listdoes not trigger a change event, unless the list items have been
typed in the Data Validation dialog box. In these versions, you can
add a button to the worksheet, and assign a macro to the button."
--Debra Dalgleish: http://www.contextures.com/xlDataVal08.html#Change
 
It is pretty well established that, in xl97, the change event is not fired
by when a selection is made from a data validation list. Debra Dalgleish
has further qualified this to be:

Debra verified that in xl97 the event is triggered if the list is embedded
in
the DataValidation. But won't fire if the list is a range on a worksheet.


So apparently Sandy V entered her validation list directly in the control.
The more common use of a range, does not work in Excel 97. The common
workaround is to have a formula reference the results of the data validation
cell and use the calculate event, but this would fire everytime the sheet
was calculated. Since you are offering a warning, it is clear when this
would be appropriate to show.
 
I am a novice as far as sheet code goes, whats the difference between Change
and SelectionChange? I want the code to 'fire' when a selection is made
from the data validation list (the cell is blank).

I tried MsgBox Application.EnableEvents and got 'TRUE'.

The code still doesn't work, if I type the value from the list, it does!

I'm not sure why I'm disabling events either, I think what I need is some
kind of error handling. When entries (a few cells at a time) are deleted it
causes an error.

Hope you can help.

Gareth
 
I didn't notice your comment re drop down validation,
ignore my previous post. Go with J.E. McGimpsey, who is
not only more expert but more observant than I!

Sandy
 
So having the list on a sheet doesn't work...

If there is one, what would be the easiest way to do it?

I have always put list into ranges on sheets, how do you put them into the
control?

Gareth
 
Sorry for being thick but a macro which does what and when would the button
be clicked?
 
Put your Worksheet_Change() code into a macro in a regular code
module. Create a button on your sheet and attach the macro to it.
Click the button whenever you change the cell in the dropdown.

You may be able to automate the process if other cells calculations
depend on your dropdown cell. While a worksheet_Change event won't
fire in XL97, a worksheet_Calculate will. I can't tell from your
code exactly what you're looking for, so I don't know whether that
would be appropriate.
 
In the data validation where it says list, instead of

=$A$1:$A$20

Put the values you would find in those cells
Mike,Tom,Fred,Sally,Rich,Bill,Hoover,Jamime,Laurence
 
I've added a sample workbook that uses the Calculate event to run the
filter. On the following page:

http://www.contextures.com/excelfiles.html

under the Filters heading, find Product List by Category, and download
the ProductsList97Calc.xls file.

The the ProductsList97.xls file runs the code from a button.
 
I was having trouble in xl2002 with a change event doing a lookup without
the formulas so I used this idea to come up with
I put a =now() formula elsewhere on the sheet and used this.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
On Error GoTo quitit
'=====
If ActiveCell.Column <> 1 Then Exit Sub
ActiveCell.Offset(, 1) = _
Application.VLookup(ActiveCell, [mylookup], 2, 0)
'========
quitit:
Application.EnableEvents = True
End Sub
 
Seems we were posting earlier at the same time.
Having incorrectly answered your question I hesitate to go
further, but...
whats the difference between Change and SelectionChange
Click to expand...
Change - fires when cell entry is changed.
SelectionChange - fires when a cell or range of cells is
selected.
When entries are deleted it causes an error
Click to expand...
You are changing the cells thereby triggering
your "Change" code.

To clarify my first post, your code worked if I entered a
cell in Col 6 w/out a ^ in "abc^" matching your your other
criteria. If I changed it from Change to SelectionChange
it also worked when I selected a cell in Col 6 (which I
wrongly thought might be your intention).

The other posts should help you more.

Regards,
Sandy
 
Thanks Don. It might not be a problem in your worksheet, but if the
active cell isn't in column A, you exit the sub without turning
EnableEvent back on.

Don said:
I was having trouble in xl2002 with a change event doing a lookup without
the formulas so I used this idea to come up with
I put a =now() formula elsewhere on the sheet and used this.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
On Error GoTo quitit
'=====
If ActiveCell.Column <> 1 Then Exit Sub
ActiveCell.Offset(, 1) = _
Application.VLookup(ActiveCell, [mylookup], 2, 0)
'========
quitit:
Application.EnableEvents = True
End Sub


I've added a sample workbook that uses the Calculate event to run the
filter. On the following page:

http://www.contextures.com/excelfiles.html

under the Filters heading, find Product List by Category, and download
the ProductsList97Calc.xls file.

The the ProductsList97.xls file runs the code from a button.

button
Click to expand...
Click to expand...
 
Guess it would be prudent to change to
if activecell.column=1 then "do the thing"

Debra Dalgleish said:
Thanks Don. It might not be a problem in your worksheet, but if the
active cell isn't in column A, you exit the sub without turning
EnableEvent back on.

Don said:
I was having trouble in xl2002 with a change event doing a lookup without
the formulas so I used this idea to come up with
I put a =now() formula elsewhere on the sheet and used this.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
On Error GoTo quitit
'=====
If ActiveCell.Column <> 1 Then Exit Sub
ActiveCell.Offset(, 1) = _
Application.VLookup(ActiveCell, [mylookup], 2, 0)
'========
quitit:
Application.EnableEvents = True
End Sub


I've added a sample workbook that uses the Calculate event to run the
filter. On the following page:

http://www.contextures.com/excelfiles.html

under the Filters heading, find Product List by Category, and download
the ProductsList97Calc.xls file.

The the ProductsList97.xls file runs the code from a button.

J.E. McGimpsey wrote:

Put your Worksheet_Change() code into a macro in a regular code
module. Create a button on your sheet and attach the macro to it.
Click the button whenever you change the cell in the dropdown.

You may be able to automate the process if other cells calculations
depend on your dropdown cell. While a worksheet_Change event won't
fire in XL97, a worksheet_Calculate will. I can't tell from your
code exactly what you're looking for, so I don't know whether that
would be appropriate.




Sorry for being thick but a macro which does what and when would the
button

be clicked?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top