My sheet ('Purchases') contains a list of medicines, the code below provides
me with a list of medicines which haven't run out.

The list is put in column A on the 'Medicine in stock' sheet.

Sub Medicineinstock()
With Worksheets("Purchases")
For Each cell In .Range("H2:H" & .Range("H65536").End(xlUp).Row)
If Not IsEmpty(cell.Value) Then
If IsNumeric(cell.Value) Then
If cell.Value 0 Then
Worksheets("Medicine in stock").Range("A65536").End(xlUp).Offset(1, 0).Value
= cell.Offset(0, -6).Value
End If
End If
End If
Next
End With
End Sub

I want to make a data validation list out of the medicine in stock and apply
that list to the next 20 blank cells in column A on sheet 'Medicine Record'.

My problem is that I want to have the Change Event of that sheet work when
an item in the data validation list is picked and work has Excel '97 and I
believe it doesn't 'fire' in '97.

A previos workaround was to enter the items directly into the control, is it
possible to do this by macro??

Thanks in advance.

Gareth

Gareth,

Data Validation doesn't trigger the Change event in 97, but what you could
do is to set another cell linked to your data validation cell, and have a
calculate event do the work for you. I used this technique to have dynamic
data validation list in XL97.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
My sheet ('Purchases') contains a list of medicines, the code below
provides
me with a list of medicines which haven't run out.

The list is put in column A on the 'Medicine in stock' sheet.

Sub Medicineinstock()
With Worksheets("Purchases")
For Each cell In .Range("H2:H" & .Range("H65536").End(xlUp).Row)
If Not IsEmpty(cell.Value) Then
If IsNumeric(cell.Value) Then
If cell.Value 0 Then
Worksheets("Medicine in stock").Range("A65536").End(xlUp).Offset(1,
0).Value
= cell.Offset(0, -6).Value
End If
End If
End If
Next
End With
End Sub

I want to make a data validation list out of the medicine in stock and
apply
that list to the next 20 blank cells in column A on sheet 'Medicine
Record'.

My problem is that I want to have the Change Event of that sheet work when
an item in the data validation list is picked and work has Excel '97 and I
believe it doesn't 'fire' in '97.

A previos workaround was to enter the items directly into the control, is
it
possible to do this by macro??

Thanks in advance.

Gareth

If you enter the data validation source into the control it does work (eg -
yes,no,maybe)

what I want to do is get the list of medicines in B2:B? which have 0 in
H2:H? into a data validation list.

Any ideas?

Gareth
"Bob Phillips" wrote in message
...
Gareth,

Data Validation doesn't trigger the Change event in 97, but what you could
do is to set another cell linked to your data validation cell, and have a
calculate event do the work for you. I used this technique to have dynamic
data validation list in XL97.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
My sheet ('Purchases') contains a list of medicines, the code below
provides
me with a list of medicines which haven't run out.

The list is put in column A on the 'Medicine in stock' sheet.

Sub Medicineinstock()
With Worksheets("Purchases")
For Each cell In .Range("H2:H" & .Range("H65536").End(xlUp).Row)
If Not IsEmpty(cell.Value) Then
If IsNumeric(cell.Value) Then
If cell.Value 0 Then
Worksheets("Medicine in stock").Range("A65536").End(xlUp).Offset(1,
0).Value
= cell.Offset(0, -6).Value
End If
End If
End If
Next
End With
End Sub

I want to make a data validation list out of the medicine in stock and
apply
that list to the next 20 blank cells in column A on sheet 'Medicine
Record'.

My problem is that I want to have the Change Event of that sheet work
when
an item in the data validation list is picked and work has Excel '97 and
I
believe it doesn't 'fire' in '97.

A previos workaround was to enter the items directly into the control, i
s
it
possible to do this by macro??

Thanks in advance.

Gareth

It may not be a problem in your worksheet, but the delimited list has a
maximum of 255 characters:

'=================================
Sub Medicineinstock()
Dim c As Range
Dim str As String
str = ""
With Worksheets("Purchases")
For Each c In .Range("H2:H" & .Range("H65536").End(xlUp).Row)
If Not IsEmpty(c.Value) Then
If IsNumeric(c.Value) Then
If c.Value 0 Then
str = str & c.Offset(0, -6).Value & ","
End If
End If
End If
Next
str = Left(str, Len(str) - 1)
str = Left(str, 255)
End With
Debug.Print str
End Sub
'===============================

Gareth wrote:
If you enter the data validation source into the control it does work (eg -
yes,no,maybe)

what I want to do is get the list of medicines in B2:B? which have 0 in
H2:H? into a data validation list.

Any ideas?

Gareth
"Bob Phillips" wrote in message
...

Gareth,

Data Validation doesn't trigger the Change event in 97, but what you could
do is to set another cell linked to your data validation cell, and have a
calculate event do the work for you. I used this technique to have dynamic
data validation list in XL97.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
.. .

My sheet ('Purchases') contains a list of medicines, the code below

provides

me with a list of medicines which haven't run out.

The list is put in column A on the 'Medicine in stock' sheet.

Sub Medicineinstock()
With Worksheets("Purchases")
For Each cell In .Range("H2:H" & .Range("H65536").End(xlUp).Row)
If Not IsEmpty(cell.Value) Then
If IsNumeric(cell.Value) Then
If cell.Value 0 Then
Worksheets("Medicine in stock").Range("A65536").End(xlUp).Offset(1,

0).Value

= cell.Offset(0, -6).Value
End If
End If
End If
Next
End With
End Sub

I want to make a data validation list out of the medicine in stock and

apply

that list to the next 20 blank cells in column A on sheet 'Medicine

Record'.

My problem is that I want to have the Change Event of that sheet work

when

an item in the data validation list is picked and work has Excel '97 and

I

believe it doesn't 'fire' in '97.

A previos workaround was to enter the items directly into the control, i

s

it

possible to do this by macro??

Thanks in advance.

Gareth








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

Debra

Thanks for this, works just great. You are right about the 255 characters
causing a problem though.

Therefore, I would like to try Bob's suggestion of using a linked cell.
Unfortunately I have no idea how to go about it, could anyone please offer
any assistance?

Gareth

"Debra Dalgleish" wrote in message
...
It may not be a problem in your worksheet, but the delimited list has a
maximum of 255 characters:

'=================================
Sub Medicineinstock()
Dim c As Range
Dim str As String
str = ""
With Worksheets("Purchases")
For Each c In .Range("H2:H" & .Range("H65536").End(xlUp).Row)
If Not IsEmpty(c.Value) Then
If IsNumeric(c.Value) Then
If c.Value 0 Then
str = str & c.Offset(0, -6).Value & ","
End If
End If
End If
Next
str = Left(str, Len(str) - 1)
str = Left(str, 255)
End With
Debug.Print str
End Sub
'===============================

Gareth wrote:
If you enter the data validation source into the control it does work
(eg -
yes,no,maybe)

what I want to do is get the list of medicines in B2:B? which have 0 in
H2:H? into a data validation list.

Any ideas?

Gareth
"Bob Phillips" wrote in message
...

Gareth,

Data Validation doesn't trigger the Change event in 97, but what you
could
do is to set another cell linked to your data validation cell, and have
a
calculate event do the work for you. I used this technique to have
dynamic
data validation list in XL97.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
.. .

My sheet ('Purchases') contains a list of medicines, the code below

provides

me with a list of medicines which haven't run out.

The list is put in column A on the 'Medicine in stock' sheet.

Sub Medicineinstock()
With Worksheets("Purchases")
For Each cell In .Range("H2:H" & .Range("H65536").End(xlUp).Row)
If Not IsEmpty(cell.Value) Then
If IsNumeric(cell.Value) Then
If cell.Value 0 Then
Worksheets("Medicine in stock").Range("A65536").End(xlUp).Offset(1,

0).Value

= cell.Offset(0, -6).Value
End If
End If
End If
Next
End With
End Sub

I want to make a data validation list out of the medicine in stock and

apply

that list to the next 20 blank cells in column A on sheet 'Medicine

Record'.

My problem is that I want to have the Change Event of that sheet work

when

an item in the data validation list is picked and work has Excel '97
and

I

believe it doesn't 'fire' in '97.

A previos workaround was to enter the items directly into the control,
i

s

it

possible to do this by macro??

Thanks in advance.

Gareth








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

Gareth,

Can I just check exactly what you want to do?

You have a list of Purchases on one sheet.
You have a Data Validation cell linked to a list - where is that, is it just
one cell or many?
What do you want to do when the Data Validation is selected from?
Do you want the DV list to update automatically when you change purchases
(e.g. something may come in to/go out of stock)?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
...
Debra

Thanks for this, works just great. You are right about the 255 characters
causing a problem though.

Therefore, I would like to try Bob's suggestion of using a linked cell.
Unfortunately I have no idea how to go about it, could anyone please offer
any assistance?

Gareth

"Debra Dalgleish" wrote in message
...
It may not be a problem in your worksheet, but the delimited list has a
maximum of 255 characters:

'=================================
Sub Medicineinstock()
Dim c As Range
Dim str As String
str = ""
With Worksheets("Purchases")
For Each c In .Range("H2:H" & .Range("H65536").End(xlUp).Row)
If Not IsEmpty(c.Value) Then
If IsNumeric(c.Value) Then
If c.Value 0 Then
str = str & c.Offset(0, -6).Value & ","
End If
End If
End If
Next
str = Left(str, Len(str) - 1)
str = Left(str, 255)
End With
Debug.Print str
End Sub
'===============================

Gareth wrote:
If you enter the data validation source into the control it does work
(eg -
yes,no,maybe)

what I want to do is get the list of medicines in B2:B? which have 0
in
H2:H? into a data validation list.

Any ideas?

Gareth
"Bob Phillips" wrote in message
...

Gareth,

Data Validation doesn't trigger the Change event in 97, but what you
could
do is to set another cell linked to your data validation cell, and
have
a
calculate event do the work for you. I used this technique to have
dynamic
data validation list in XL97.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Gareth" wrote in message
.. .

My sheet ('Purchases') contains a list of medicines, the code below

provides

me with a list of medicines which haven't run out.

The list is put in column A on the 'Medicine in stock' sheet.

Sub Medicineinstock()
With Worksheets("Purchases")
For Each cell In .Range("H2:H" & .Range("H65536").End(xlUp).Row)
If Not IsEmpty(cell.Value) Then
If IsNumeric(cell.Value) Then
If cell.Value 0 Then
Worksheets("Medicine in stock").Range("A65536").End(xlUp).Offset(1,

0).Value

= cell.Offset(0, -6).Value
End If
End If
End If
Next
End With
End Sub

I want to make a data validation list out of the medicine in stock
and

apply

that list to the next 20 blank cells in column A on sheet 'Medicine

Record'.

My problem is that I want to have the Change Event of that sheet work

when

an item in the data validation list is picked and work has Excel '97
and

I

believe it doesn't 'fire' in '97.

A previos workaround was to enter the items directly into the
control,
i

s

it

possible to do this by macro??

Thanks in advance.

Gareth








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