Validation List

[Accesshelp]

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.

 

[Jacob Skaria]

Right click the sheet tab>View 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

 

[Jim Thomlinson]

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

 

[Accesshelp]

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.

 

[Accesshelp]

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.

 

[Jacob Skaria]

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

 

[Accesshelp]

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]

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