Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation List
Hello all,
In Cell C5 of my Excel worksheet, I have a Validation List. There are values in the Validation List, and when the users select a value from the Validation List, I want my code to perform a list of procedures. How should I write a code to triggle my code to run when the users select a value from the Validation List or change a value from previously selected? From Help, I saw the following event: Private Sub object_DropButtonClick( ) I don't know the above event would work. What should I have in place of object in event? ValidateList? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation List
Right click the sheet tabView Code. This will take you to the code module of
the sheet. Paste the below code which is the change event . Target is the cell reference. If your validation drop down range is A1:A100 Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1:A100")) Is Nothing Then Msgbox "Change has occured" End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Accesshelp" wrote: Hello all, In Cell C5 of my Excel worksheet, I have a Validation List. There are values in the Validation List, and when the users select a value from the Validation List, I want my code to perform a list of procedures. How should I write a code to triggle my code to run when the users select a value from the Validation List or change a value from previously selected? From Help, I saw the following event: Private Sub object_DropButtonClick( ) I don't know the above event would work. What should I have in place of object in event? ValidateList? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation List
I would use code something like this. It assumes your validation list
contains 'This' and 'That'. Right click the sheet tab and select view code. Paste the following... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$5" Then Select Case Target.Value Case "This" MsgBox "A" Case "That" MsgBox "B" Case Else MsgBox "C" End Select End If End Sub -- HTH... Jim Thomlinson "Accesshelp" wrote: Hello all, In Cell C5 of my Excel worksheet, I have a Validation List. There are values in the Validation List, and when the users select a value from the Validation List, I want my code to perform a list of procedures. How should I write a code to triggle my code to run when the users select a value from the Validation List or change a value from previously selected? From Help, I saw the following event: Private Sub object_DropButtonClick( ) I don't know the above event would work. What should I have in place of object in event? ValidateList? Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation List
Jacob,
Thank you very much for your help. Your code works perfectly, except one small thing. Somehow, my code still executes without the value changes in C5. For example, C5 has value "1234". If I click on the Validation List without selecting a new value (just by selecting the same value 1234), the code still executes. Is there a way not to let the code to execute when the value is the same as before? Basically, the code should only execute when the value changes from 1234 to 5675? Thanks. "Jacob Skaria" wrote: Right click the sheet tabView Code. This will take you to the code module of the sheet. Paste the below code which is the change event . Target is the cell reference. If your validation drop down range is A1:A100 Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1:A100")) Is Nothing Then Msgbox "Change has occured" End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Accesshelp" wrote: Hello all, In Cell C5 of my Excel worksheet, I have a Validation List. There are values in the Validation List, and when the users select a value from the Validation List, I want my code to perform a list of procedures. How should I write a code to triggle my code to run when the users select a value from the Validation List or change a value from previously selected? From Help, I saw the following event: Private Sub object_DropButtonClick( ) I don't know the above event would work. What should I have in place of object in event? ValidateList? Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation List
Jim,
Thank you very much for your help. For what I need now, the same code is executed regardless of what value is selected from the list. Therefore, the code only executes when the value is changed from the list. I will keep your code for furture reference. Thanks again. "Jim Thomlinson" wrote: I would use code something like this. It assumes your validation list contains 'This' and 'That'. Right click the sheet tab and select view code. Paste the following... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$5" Then Select Case Target.Value Case "This" MsgBox "A" Case "That" MsgBox "B" Case Else MsgBox "C" End Select End If End Sub -- HTH... Jim Thomlinson "Accesshelp" wrote: Hello all, In Cell C5 of my Excel worksheet, I have a Validation List. There are values in the Validation List, and when the users select a value from the Validation List, I want my code to perform a list of procedures. How should I write a code to triggle my code to run when the users select a value from the Validation List or change a value from previously selected? From Help, I saw the following event: Private Sub object_DropButtonClick( ) I don't know the above event would work. What should I have in place of object in event? ValidateList? Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation List
Right click the sheet tab. Remove the existing code and paste the below code.
There are two event codes and 1 variable declared outside the events. Try and feedback Dim varTemp As Variant Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1:A100")) Is Nothing Then If Target.Text < varTemp Then MsgBox "Change has occured" End If End If Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) varTemp = Target.Text End Sub If this post helps click Yes --------------- Jacob Skaria "Accesshelp" wrote: Jacob, Thank you very much for your help. Your code works perfectly, except one small thing. Somehow, my code still executes without the value changes in C5. For example, C5 has value "1234". If I click on the Validation List without selecting a new value (just by selecting the same value 1234), the code still executes. Is there a way not to let the code to execute when the value is the same as before? Basically, the code should only execute when the value changes from 1234 to 5675? Thanks. "Jacob Skaria" wrote: Right click the sheet tabView Code. This will take you to the code module of the sheet. Paste the below code which is the change event . Target is the cell reference. If your validation drop down range is A1:A100 Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1:A100")) Is Nothing Then Msgbox "Change has occured" End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Accesshelp" wrote: Hello all, In Cell C5 of my Excel worksheet, I have a Validation List. There are values in the Validation List, and when the users select a value from the Validation List, I want my code to perform a list of procedures. How should I write a code to triggle my code to run when the users select a value from the Validation List or change a value from previously selected? From Help, I saw the following event: Private Sub object_DropButtonClick( ) I don't know the above event would work. What should I have in place of object in event? ValidateList? Thanks. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation List
Jacob,
Thank you very much for continuing to help. I tried your updated code, and somehow, the updated or previous code is no longer executed. When I change the value, nothing happens. I don't know it is caused by my other code. I have one other code that does the similar thing, except it has a few lines than the one that you are helping me. Plus, the other code is executed after I click the command button so it does not run automatically like the one that you are helping me. Basically, the other code is looking up the value in Cell C4 and populates Cell C5 (the cell that you are helping me) via vlookup (for the first value and the other values are in Validation List). Then it executes the same code as C5. The one that you have been helping me is to execute the code if the users change the value from Validation List in C5. I hope I did not confuse you. Please ask me with questions. Thanks again. "Jacob Skaria" wrote: Right click the sheet tab. Remove the existing code and paste the below code. There are two event codes and 1 variable declared outside the events. Try and feedback Dim varTemp As Variant Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1:A100")) Is Nothing Then If Target.Text < varTemp Then MsgBox "Change has occured" End If End If Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) varTemp = Target.Text End Sub If this post helps click Yes --------------- Jacob Skaria "Accesshelp" wrote: Jacob, Thank you very much for your help. Your code works perfectly, except one small thing. Somehow, my code still executes without the value changes in C5. For example, C5 has value "1234". If I click on the Validation List without selecting a new value (just by selecting the same value 1234), the code still executes. Is there a way not to let the code to execute when the value is the same as before? Basically, the code should only execute when the value changes from 1234 to 5675? Thanks. "Jacob Skaria" wrote: Right click the sheet tabView Code. This will take you to the code module of the sheet. Paste the below code which is the change event . Target is the cell reference. If your validation drop down range is A1:A100 Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1:A100")) Is Nothing Then Msgbox "Change has occured" End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Accesshelp" wrote: Hello all, In Cell C5 of my Excel worksheet, I have a Validation List. There are values in the Validation List, and when the users select a value from the Validation List, I want my code to perform a list of procedures. How should I write a code to triggle my code to run when the users select a value from the Validation List or change a value from previously selected? From Help, I saw the following event: Private Sub object_DropButtonClick( ) I don't know the above event would work. What should I have in place of object in event? ValidateList? Thanks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation List
As per the code I pasted earlier the validation list should be in the range
A1:A100. Try that in a fresh workbook and see OR in immediate window you can execute the below command... Application.EnableEvents = True If this post helps click Yes --------------- Jacob Skaria "Accesshelp" wrote: Jacob, Thank you very much for continuing to help. I tried your updated code, and somehow, the updated or previous code is no longer executed. When I change the value, nothing happens. I don't know it is caused by my other code. I have one other code that does the similar thing, except it has a few lines than the one that you are helping me. Plus, the other code is executed after I click the command button so it does not run automatically like the one that you are helping me. Basically, the other code is looking up the value in Cell C4 and populates Cell C5 (the cell that you are helping me) via vlookup (for the first value and the other values are in Validation List). Then it executes the same code as C5. The one that you have been helping me is to execute the code if the users change the value from Validation List in C5. I hope I did not confuse you. Please ask me with questions. Thanks again. "Jacob Skaria" wrote: Right click the sheet tab. Remove the existing code and paste the below code. There are two event codes and 1 variable declared outside the events. Try and feedback Dim varTemp As Variant Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1:A100")) Is Nothing Then If Target.Text < varTemp Then MsgBox "Change has occured" End If End If Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) varTemp = Target.Text End Sub If this post helps click Yes --------------- Jacob Skaria "Accesshelp" wrote: Jacob, Thank you very much for your help. Your code works perfectly, except one small thing. Somehow, my code still executes without the value changes in C5. For example, C5 has value "1234". If I click on the Validation List without selecting a new value (just by selecting the same value 1234), the code still executes. Is there a way not to let the code to execute when the value is the same as before? Basically, the code should only execute when the value changes from 1234 to 5675? Thanks. "Jacob Skaria" wrote: Right click the sheet tabView Code. This will take you to the code module of the sheet. Paste the below code which is the change event . Target is the cell reference. If your validation drop down range is A1:A100 Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1:A100")) Is Nothing Then Msgbox "Change has occured" End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Accesshelp" wrote: Hello all, In Cell C5 of my Excel worksheet, I have a Validation List. There are values in the Validation List, and when the users select a value from the Validation List, I want my code to perform a list of procedures. How should I write a code to triggle my code to run when the users select a value from the Validation List or change a value from previously selected? From Help, I saw the following event: Private Sub object_DropButtonClick( ) I don't know the above event would work. What should I have in place of object in event? ValidateList? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use VBA to reset data validation (=list) value to first value in that list (list is a named range) | Excel Programming | |||
Crazy Data Validation ... List Validation Not Working | Excel Programming | |||
Validation (Drop down list vs simple text length validation) | Excel Programming | |||
Validation (Drop down list vs simple text length validation) | Excel Programming | |||
Validation (Drop down list vs simple text length validation) | Excel Programming |