Data Validation List

[mike]

I have the same data validation on several worksheets. When I select an item
from the list on one worksheet, I would like for that same item to be selcted
on the other worksheets' list.

I have a list of communities and when i select one i want the other
workhseets list to switch to the selected community.

any advice is appreciated.

 

[corey]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' code for sheet1
If Range("A1").Value <> "" Then ' modify range where Validation exists to
suit
Sheet2.Range("A1").Value = Sheet1.Range("A1").Value
Sheet3.Range("A1").Value = Sheet1.Range("A1").Value
Sheet5.Range("A1").Value = Sheet1.Range("A1").Value
End If
End Sub
Will only work where Sheet1 list item is changed

Change Sheet names to suit and ranges.

 

[John]

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.

 

[mike]

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?

 

[Gord Dibben]

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

 

[mike]

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

 

[mike]

gord..thx

it works now, had to change security settings to low. now something new...

-added code for sheets 2 and 3, works fine
-add code for sheet 4, when i change item in the VD list program gets hung up
-if 2 and 3 work then adding 4 (5 and 6) for that matter shouldnt pose a
conflict, should it?
-checked to make sure all sheet and value references were accurate...