View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.programming
Mike Mike is offline
external usenet poster
 
Posts: 3,101
Default Data Validation List

gord,

i changed the sub name, thought that was it. no dice. make sure my logic
is right.
'sheet2
Private Sub Worksheet_Change(ByVal Target As Range)
' if list is not empty
If Range("H1").Value < "" Then
' set all VD's to sheet 2 currrent list value
Sheet3.Range("G1").Value = Sheet1.Range("H1").Value
Sheet4.Range("H1").Value = Sheet1.Range("H1").Value
Sheet5.Range("G1").Value = Sheet1.Range("H1").Value
Sheet6.Range("H1").Value = Sheet1.Range("H1").Value
End If
End Sub




"Gord Dibben" wrote:

Wrong event type.

Should be Private Sub Worksheet_Change(ByVal Target As Range)

You are changing the value in Sheet1 H1 by selecting from a DV list, not by
selecting the cell.


Gord Dibben MS Excel MVP


On Thu, 9 Oct 2008 04:46:00 -0700, mike
wrote:

Corey-John,

thx for the info. this is the code i used for sheet1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' code for sheet1
If Range("H1").Value < "" Then
Sheet2.Range("H1").Value = Sheet1.Range("H1").Value
Sheet3.Range("G1").Value = Sheet1.Range("H1").Value
Sheet4.Range("H1").Value = Sheet1.Range("H1").Value
Sheet5.Range("G1").Value = Sheet1.Range("H1").Value
Sheet6.Range("H1").Value = Sheet1.Range("H1").Value
End If
End Sub

-When i change list on sheet1 the list on the other sheets do not change.
Selecting a new item in the list should trigger this code to run, correct?

"John" wrote:

On Oct 9, 3:07 am, "corey" wrote:
Will only work where Sheet1 list item is changed

If the similar code with sheet names changed is placed on each sheet
the update could be done from any sheet.