| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation List
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Data Validation List
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. "mike" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Data Validation List
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Data Validation List
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Data Validation List
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Data Validation List
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... "mike" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| data validation list: how do i 'force' a user to enter data from the list? | Excel Discussion (Misc queries) | |||
| Crazy Data Validation ... List Validation Not Working | Excel Programming | |||
| data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
| data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
| data validation invalid in dynamic validation list | Excel Programming | |||
Linear Mode
