i have a data validation with a drop down list, and

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("d2").Address Then
Range("d5").Value = Range("b1").Value
Range("f5").Value = Range("c1").Value
Application.Run "changesquare"
End If
End Sub

however when i drop down the list and select a different value nothing happens
any ideas?

thanks in advance

Hi,
It worked OK for me i.e. change of value of D2 was always reflected in
the message in "changesquare":

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("d2").Address Then
Range("d5").Value = Range("b1").Value
Range("f5").Value = Range("c1").Value
Run "changesquare", Range("d2").Value
End If
End Sub

Sub Changesquare(n)
MsgBox "Changesquare called " & n
End Sub

What does "changesquare" do? Does it work if you don't run it?

Sorry I can't be more helpful.

MS 2003


"choice" wrote:

i have a data validation with a drop down list, and

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("d2").Address Then
Range("d5").Value = Range("b1").Value
Range("f5").Value = Range("c1").Value
Application.Run "changesquare"
End If
End Sub

however when i drop down the list and select a different value nothing happens
any ideas?

thanks in advance

If you are using Excel 97 data validation change does not fire an event.

If that's what you're using requires radical workaround. Maybe simulate with
a Combobox from the Controls Toolbox and link your code into that. Or link
the changing value to a formula and look at the Calculation event. Update
and compare a module level variable, or static variable within the event
code, to the changing value.

Regards,
Peter T

Regards,
Peter T
"choice" wrote in message
...
i have a data validation with a drop down list, and

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("d2").Address Then
Range("d5").Value = Range("b1").Value
Range("f5").Value = Range("c1").Value
Application.Run "changesquare"
End If
End Sub

however when i drop down the list and select a different value nothing
happens
any ideas?

thanks in advance

In Excel 97, data validation will fire the change event if the list is
delimited.

Peter T wrote:
If you are using Excel 97 data validation change does not fire an event.

If that's what you're using requires radical workaround. Maybe simulate with
a Combobox from the Controls Toolbox and link your code into that. Or link
the changing value to a formula and look at the Calculation event. Update
and compare a module level variable, or static variable within the event
code, to the changing value.

Regards,
Peter T

Regards,
Peter T
"choice" wrote in message
...

i have a data validation with a drop down list, and

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("d2").Address Then
Range("d5").Value = Range("b1").Value
Range("f5").Value = Range("c1").Value
Application.Run "changesquare"
End If
End Sub

however when i drop down the list and select a different value nothing

happens

any ideas?

thanks in advance





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

Hi Debra,

In Excel 97, data validation will fire the change event if the list is
delimited.

So it does, wish I had known that years ago! I suppose because I've never
used a delimited list with DV.

Had a quick look and it seems viable to programatically convert the List
from that in a range to a delimited list, in (say) the workbook open event
if version is Excel 97. Only problem is if the length is more than 255 then
crash occurs when Add'ing DV with the long list. But 255 is often enough for
a DV list.

Regards,
Peter T

Peter T wrote:
If you are using Excel 97 data validation change does not fire an event.

If that's what you're using requires radical workaround. Maybe simulate
with
a Combobox from the Controls Toolbox and link your code into that. Or
link
the changing value to a formula and look at the Calculation event.
Update
and compare a module level variable, or static variable within the
event
code, to the changing value.

Regards,
Peter T

Regards,
Peter T
"choice" wrote in message
...

i have a data validation with a drop down list, and

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("d2").Address Then
Range("d5").Value = Range("b1").Value
Range("f5").Value = Range("c1").Value
Application.Run "changesquare"
End If
End Sub

however when i drop down the list and select a different value nothing

happens

any ideas?

thanks in advance





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

You'd also have problems if any list items contain the delimiter
character, but for most lists that wouldn't be a problem.

Peter T wrote:
Hi Debra,


In Excel 97, data validation will fire the change event if the list is
delimited.


So it does, wish I had known that years ago! I suppose because I've never
used a delimited list with DV.

Had a quick look and it seems viable to programatically convert the List
from that in a range to a delimited list, in (say) the workbook open event
if version is Excel 97. Only problem is if the length is more than 255 then
crash occurs when Add'ing DV with the long list. But 255 is often enough for
a DV list.

Regards,
Peter T


Peter T wrote:

If you are using Excel 97 data validation change does not fire an event.

If that's what you're using requires radical workaround. Maybe simulate

with

a Combobox from the Controls Toolbox and link your code into that. Or

link

the changing value to a formula and look at the Calculation event.

Update

and compare a module level variable, or static variable within the

event

code, to the changing value.

Regards,
Peter T

Regards,
Peter T
"choice" wrote in message
...


i have a data validation with a drop down list, and

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = Range("d2").Address Then
Range("d5").Value = Range("b1").Value
Range("f5").Value = Range("c1").Value
Application.Run "changesquare"
End If
End Sub

however when i drop down the list and select a different value nothing

happens


any ideas?

thanks in advance




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