Change Event with Drop Down Validation List
Hi, I have 13 sheets in my workbook (one for each month plus a GlobalSettings). In each month sheet I want to create a change event that prompts a UserForm when they select "Yes" from a drop-down validation list if it happens to be a month prior to the current month. However it is not working, any help will be greatly appreciated!!!
This is the code for the change event:
Code:Private Sub Worksheet_Change(ByVal Target As Range) Dim Value1, Value2 As Integer Value1 = Sheets("P1").Range("I15").Value Value2 = Sheets("GlobalSettings").Range("B14").Value If Target.Address = "$K$14" Then If Range("K14") = "NO" Then End If Exit Sub If Range("K14") = "YES" And Value1 < Value2 Then UserForm2.Show End If End If End Sub
Last edited by gophins; 10-03-2009 at 12:21 PM. Reason: NEW ERROR CAME UP
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel consulting, free examples and tutorials visit Excel Consulting-Excel VBA
Check out the free Excel Toolbar
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Code Tags: Make your code easier for us to read
Hello gophins,
When posting, you should state what you expect and what is happening. Include error messages if there are any. I shortened your code. Try it out and let me know if you are still having problems. You may have to post your workbook for review if there any problems.
Code:Private Sub Worksheet_Change(ByVal Target As Range) Dim Value1 As Integer Dim Value2 As Integer Value1 = Sheets("P1").Range("I15").Value Value2 = Sheets("GlobalSettings").Range("B14").Value If Target.Address = "$K$14" And Target.Value = "YES" And Value1 < Value2 Then UserForm2.Show End If End Sub
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Thank you Leith Ross. It wasn't doing anything before, so I didn't have any error to report. The "and" statements must not have been working properly.
Again, THANKS!!!
Hi, the code that Leith provided works well. However, when I run the overal macro, it now generates an error. The overall macro creates a log of changes to the spreadsheet and emails this changes to me. The code imbedded in the page is to stop people from making changes to previous months.
A form will pop up warning people that this is not a good idea. Now, when the overall macro runs though and it is copying and pasting rabges as values to keep for the log, it stops and gives me the error in the first of the 12 sheets where "YES" has been selected (however this is late in the macro, it has done at least two passess through the sheets, evaluated the same cell, K14, sent and email, and then it prompts the DEBUG).Code:Private Sub Worksheet_Change(ByVal Target As Range) Dim Value1 As Integer Dim Value2 As Integer Value1 = Sheets("P11").Range("I15").Value Value2 = Sheets("GlobalSettings").Range("B14").Value If Target.Address = "$K$14" And Target.Value = "YES" And Value1 < Value2 Then UserForm2.Show End If End Sub
This is the CODE that prompts the error (from above code): If Target.Address = "$K$14" And Target.Value = "YES" And Value1 < Value2 Then
THANK YOU VERY MUCH FOR ANY ASSISTANCE
Code:'NEW VALUE COPY TO CALCULATE VARIANCE If Sheets("P12").Range("K14") = "YES" Then Sheets("P12").Range("C21:G21,C37:G37").Copy Sheets("P12").Range("C39").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If 'Sheets("P12").Range("K14") = "NO" If Sheets("P11").Range("K14") = "YES" Then Sheets("P11").Range("C21:G21,C37:G37").Copy Sheets("P11").Range("C39").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If 'Sheets("P11").Range("K14") = "NO" If Sheets("P10").Range("K14") = "YES" Then Sheets("P10").Range("C21:G21,C37:G37").Copy Sheets("P10").Range("C39").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If 'Sheets("P10").Range("K14") = "NO" If Sheets("P9").Range("K14") = "YES" Then Sheets("P9").Range("C21:G21,C37:G37").Copy Sheets("P9").Range("C39").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If 'Sheets("P9").Range("K14") = "NO" If Sheets("P8").Range("K14") = "YES" Then Sheets("P8").Range("C21:G21,C37:G37").Copy Sheets("P8").Range("C39").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If 'Sheets("P8").Range("K14") = "NO" If Sheets("P7").Range("K14") = "YES" Then Sheets("P7").Range("C21:G21,C37:G37").Copy Sheets("P7").Range("C39").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If 'Sheets("P7").Range("K14") = "NO" If Sheets("P6").Range("K14") = "YES" Then Sheets("P6").Range("C21:G21,C37:G37").Copy Sheets("P6").Range("C39").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If 'Sheets("P6").Range("K14") = "NO" If Sheets("P5").Range("K14") = "YES" Then Sheets("P5").Range("C21:G21,C37:G37").Copy Sheets("P5").Range("C39").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If 'Sheets("P5").Range("K14") = "NO" If Sheets("P4").Range("K14") = "YES" Then Sheets("P4").Range("C21:G21,C37:G37").Copy Sheets("P4").Range("C39").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If 'Sheets("P4").Range("K14") = "NO" If Sheets("P3").Range("K14") = "YES" Then Sheets("P3").Range("C21:G21,C37:G37").Copy Sheets("P3").Range("C39").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If 'Sheets("P3").Range("K14") = "NO" If Sheets("P2").Range("K14") = "YES" Then Sheets("P2").Range("C21:G21,C37:G37").Copy Sheets("P2").Range("C39").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If 'Sheets("P2").Range("K14") = "NO" If Sheets("P1").Range("K14") = "YES" Then Sheets("P1").Range("C21:G21,C37:G37").Copy Sheets("P1").Range("C39").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If 'Sheets("P1").Range("K14") = "NO"
Hello gophins,
If the second code box is in either a SelectionChange or Change event, you need to add 2 lines of code: one at the very top at one at the very bottom. These are...
Code:Application.EnableEvents = False 'At the top Application.EnableEvents = True 'At the bottom or before Exit from the Sub
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Star3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Thank you again Leith, that solved the problem!! My workbook is all finished, thank you very much.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks