Data Validation List

  • Thread starter Thread starter mike
  • Start date Start date
M

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.
 
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.
 
Will only work where Sheet1 list item is changed
Click to expand...

If the similar code with sheet names changed is placed on each sheet
the update could be done from any sheet.
 
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?
 
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
 
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..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...
 
Back
Top