Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation ON List of values against by command button
Thank you in advance
I need a command button to check if a list of values I input actually are in another list on a different worksheet. If they are not in the list it would be highlighted a color. I understand there are easier ways (conditional formatting, drop down, etc) but my boss wants it done specifically this way. Please help -Thank you, Alex |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation ON List of values against by command button
Something like this. Of course, you would programatically create your
ranges dimensions if they are subject to change. Dim cl As Range, enteredList As Range, compareList As Range Set enteredList = Range("A2:A15") Set compareList = Range("C2:C15") For Each cl In enteredList If WorksheetFunction.CountIf(compareList, cl.Value) = 0 Then MsgBox "not in list" Else MsgBox "in list" End If Next cl Set entereList = Nothing Set compareList = Nothing Alex wrote: Thank you in advance I need a command button to check if a list of values I input actually are in another list on a different worksheet. If they are not in the list it would be highlighted a color. I understand there are easier ways (conditional formatting, drop down, etc) but my boss wants it done specifically this way. Please help -Thank you, Alex |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation ON List of values against by command button
Thank you. The Compare list is in a different worksheet though. I tried
using =Range("CC!A2:A15"), however that doesn't work. Also instead of a message I would like the cells to be higlighted if they are not in the list. -Thank you, again "JW" wrote: Something like this. Of course, you would programatically create your ranges dimensions if they are subject to change. Dim cl As Range, enteredList As Range, compareList As Range Set enteredList = Range("A2:A15") Set compareList = Range("C2:C15") For Each cl In enteredList If WorksheetFunction.CountIf(compareList, cl.Value) = 0 Then MsgBox "not in list" Else MsgBox "in list" End If Next cl Set entereList = Nothing Set compareList = Nothing Alex wrote: Thank you in advance I need a command button to check if a list of values I input actually are in another list on a different worksheet. If they are not in the list it would be highlighted a color. I understand there are easier ways (conditional formatting, drop down, etc) but my boss wants it done specifically this way. Please help -Thank you, Alex |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation ON List of values against by command button
I've actually got the highlight part down, but still need help in set
comparelist on a different worksheet. Also, is there a way not to specify a range. Because both the entered and compare list will always be different. In other words have it select the range based if the value is not null. "JW" wrote: Something like this. Of course, you would programatically create your ranges dimensions if they are subject to change. Dim cl As Range, enteredList As Range, compareList As Range Set enteredList = Range("A2:A15") Set compareList = Range("C2:C15") For Each cl In enteredList If WorksheetFunction.CountIf(compareList, cl.Value) = 0 Then MsgBox "not in list" Else MsgBox "in list" End If Next cl Set entereList = Nothing Set compareList = Nothing Alex wrote: Thank you in advance I need a command button to check if a list of values I input actually are in another list on a different worksheet. If they are not in the list it would be highlighted a color. I understand there are easier ways (conditional formatting, drop down, etc) but my boss wants it done specifically this way. Please help -Thank you, Alex |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation ON List of values against by command button
To reference the range in a seperate workbook, use something like
Worksheets("CC").range("A2:A15") As for not setting it at all, the program wouldn't know where to look for the compare. Are the lists always in the same columns? If so, we can automate the range dimension by finding the top row and bottom row of the columns. Just need a little more detail. Alex wrote: I've actually got the highlight part down, but still need help in set comparelist on a different worksheet. Also, is there a way not to specify a range. Because both the entered and compare list will always be different. In other words have it select the range based if the value is not null. "JW" wrote: Something like this. Of course, you would programatically create your ranges dimensions if they are subject to change. Dim cl As Range, enteredList As Range, compareList As Range Set enteredList = Range("A2:A15") Set compareList = Range("C2:C15") For Each cl In enteredList If WorksheetFunction.CountIf(compareList, cl.Value) = 0 Then MsgBox "not in list" Else MsgBox "in list" End If Next cl Set entereList = Nothing Set compareList = Nothing Alex wrote: Thank you in advance I need a command button to check if a list of values I input actually are in another list on a different worksheet. If they are not in the list it would be highlighted a color. I understand there are easier ways (conditional formatting, drop down, etc) but my boss wants it done specifically this way. Please help -Thank you, Alex |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation ON List of values against by command button
Yes both lists are in the same column in their respective worksheets. I was
fooling around with this piece of code; Range("B3").End(xlDown).Select. but nothing Yet. The Entered list is in Worksheet Orginal starting in cell B3 and the Compare List in Worksheet CC starting in A1 "JW" wrote: To reference the range in a seperate workbook, use something like Worksheets("CC").range("A2:A15") As for not setting it at all, the program wouldn't know where to look for the compare. Are the lists always in the same columns? If so, we can automate the range dimension by finding the top row and bottom row of the columns. Just need a little more detail. Alex wrote: I've actually got the highlight part down, but still need help in set comparelist on a different worksheet. Also, is there a way not to specify a range. Because both the entered and compare list will always be different. In other words have it select the range based if the value is not null. "JW" wrote: Something like this. Of course, you would programatically create your ranges dimensions if they are subject to change. Dim cl As Range, enteredList As Range, compareList As Range Set enteredList = Range("A2:A15") Set compareList = Range("C2:C15") For Each cl In enteredList If WorksheetFunction.CountIf(compareList, cl.Value) = 0 Then MsgBox "not in list" Else MsgBox "in list" End If Next cl Set entereList = Nothing Set compareList = Nothing Alex wrote: Thank you in advance I need a command button to check if a list of values I input actually are in another list on a different worksheet. If they are not in the list it would be highlighted a color. I understand there are easier ways (conditional formatting, drop down, etc) but my boss wants it done specifically this way. Please help -Thank you, Alex |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation ON List of values against by command button
Not tested, but this should do the trick. Could be streamlined a bit
more, but you get the idea. Dim cl As Range, enteredList As Range, compareList As Range Dim enteredBotRow as Long, compareBotRow as Long enteredBotRow = Range("B65536").End(xlUp).Row Set enteredList = Range("B3:B" & enteredBotRow) compareBotRow = Worksheets("CC").Range("A65536").End(xlUp).Row Set compareList = Worksheets("CC").Range("A1:A" & compareBotRow) For Each cl In enteredList If WorksheetFunction.CountIf(compareList, cl.Value) = 0 Then MsgBox "not in list" Else MsgBox "in list" End If Next cl Set entereList = Nothing Set compareList = Nothing Alex wrote: Yes both lists are in the same column in their respective worksheets. I was fooling around with this piece of code; Range("B3").End(xlDown).Select. but nothing Yet. The Entered list is in Worksheet Orginal starting in cell B3 and the Compare List in Worksheet CC starting in A1 "JW" wrote: To reference the range in a seperate workbook, use something like Worksheets("CC").range("A2:A15") As for not setting it at all, the program wouldn't know where to look for the compare. Are the lists always in the same columns? If so, we can automate the range dimension by finding the top row and bottom row of the columns. Just need a little more detail. Alex wrote: I've actually got the highlight part down, but still need help in set comparelist on a different worksheet. Also, is there a way not to specify a range. Because both the entered and compare list will always be different. In other words have it select the range based if the value is not null. "JW" wrote: Something like this. Of course, you would programatically create your ranges dimensions if they are subject to change. Dim cl As Range, enteredList As Range, compareList As Range Set enteredList = Range("A2:A15") Set compareList = Range("C2:C15") For Each cl In enteredList If WorksheetFunction.CountIf(compareList, cl.Value) = 0 Then MsgBox "not in list" Else MsgBox "in list" End If Next cl Set entereList = Nothing Set compareList = Nothing Alex wrote: Thank you in advance I need a command button to check if a list of values I input actually are in another list on a different worksheet. If they are not in the list it would be highlighted a color. I understand there are easier ways (conditional formatting, drop down, etc) but my boss wants it done specifically this way. Please help -Thank you, Alex |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Xl2003: Validation.Add method gives error from Command Button | Excel Programming | |||
Xl2003: Validation.Add method gives error from Command Button | Excel Programming | |||
Where is a list of Command Button uses? | Excel Discussion (Misc queries) | |||
How can I add values in 2 cells using a command button | Excel Programming | |||
How can I add values in 2 cells using a command button | Excel Programming |