Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 9th 08, 01:58 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,101
Default 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   Report Post  
Old October 9th 08, 10:07 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2008
Posts: 11
Default 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   Report Post  
Old October 9th 08, 11:32 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2008
Posts: 19
Default 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   Report Post  
Old October 9th 08, 12:46 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,101
Default 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   Report Post  
Old October 9th 08, 09:52 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default 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   Report Post  
Old October 10th 08, 01:51 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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.



  #7   Report Post  
Old October 10th 08, 01:22 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,101
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
data validation list: how do i 'force' a user to enter data from the list? showsomeidnow Excel Discussion (Misc queries) 2 April 29th 07 11:09 PM
Crazy Data Validation ... List Validation Not Working TW Bake Excel Programming 1 March 29th 07 02:41 AM
data validation invalid in dynamic validation list ilia Excel Discussion (Misc queries) 0 November 7th 06 12:54 PM
data validation invalid in dynamic validation list ilia Excel Worksheet Functions 0 November 7th 06 12:54 PM
data validation invalid in dynamic validation list ilia Excel Programming 0 November 7th 06 12:54 PM


All times are GMT +1. The time now is 05:08 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017