Data Validation List

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mike - 09 Oct 2008 01:58 GMT

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.

Reply to this Message

corey - 09 Oct 2008 09:11 GMT

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.

>I have the same data validation on several worksheets. When I select an
>item
[quoted text clipped - 6 lines]
>
> any advice is appreciated.

Reply to this Message

John - 09 Oct 2008 11:32 GMT

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

Reply to this Message

mike - 09 Oct 2008 12:46 GMT

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?

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

Reply to this Message

Gord Dibben - 09 Oct 2008 21:52 GMT

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

>Corey-John,
>
[quoted text clipped - 18 lines]
>> If the similar code with sheet names changed is placed on each sheet
>> the update could be done from any sheet.

Reply to this Message

mike - 10 Oct 2008 01:51 GMT

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

> Wrong event type.
>
[quoted text clipped - 27 lines]
> >> If the similar code with sheet names changed is placed on each sheet
> >> the update could be done from any sheet.

Reply to this Message

mike - 10 Oct 2008 13:22 GMT

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

> gord,
>
[quoted text clipped - 43 lines]
> > >> If the similar code with sheet names changed is placed on each sheet
> > >> the update could be done from any sheet.

Reply to this Message