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

-----Original Message-----
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


.

"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




In article ,
"Gareth" wrote:

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

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.

--
Regards,
Tom Ogilvy




Gareth wrote in message
...
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

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

"Sandy V" wrote in message
...
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

-----Original Message-----
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


.

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

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy




Gareth wrote in message
...
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

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

"J.E. McGimpsey" wrote in message
...
"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




In article ,
"Gareth" wrote:

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

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 article ,
"Gareth" wrote:

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

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,Lauren ce

--
Regards,
Tom Ogilvy


Gareth wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy




Gareth wrote in message
...
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

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.




In article ,
"Gareth" wrote:


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



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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


"Debra Dalgleish" wrote in message
...
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.




In article ,
"Gareth" wrote:


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



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

-----Original Message-----
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

"Sandy V" wrote in
message
...
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

-----Original Message-----
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


.



.

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 Guillett wrote:
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


"Debra Dalgleish" wrote in message
...

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.




In article ,
"Gareth" wrote:



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

button

be clicked?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

"Debra Dalgleish" wrote in message
...
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 Guillett wrote:
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


"Debra Dalgleish" wrote in message
...

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.




In article ,
"Gareth" wrote:



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

button

be clicked?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html