98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Using multiple events in Private Sub Worksheet_Change
Using multiple events in Private Sub Worksheet_Change
Resolved · Medium Priority · Version 2010
Tania has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Using multiple events in Private Sub Worksheet_Change
Hi,
I have a Worksheet_change event which allows me to add more than one entry from a data validation list into the active cell in Column U. This works fine.
I also need to add a second change event so that if a cell is selected in Column A of the active sheet, it puts the value of that cell in B1 of the 'Pricing Calculator' sheet. On its own, that bit of code also works. However with the combined the code below, only the multiple drop down entries work, regardless of the order in which I have the two bits of code. I'm not seeing where it's preventing from running the 'transfer cell content to other sheet' bit.
Many thanks
Tania
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.EnableEvents = False
' To Select Multiple Items from a Drop Down List in Excel
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("U:U")) Is Nothing Then
If Target.Column = 21 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & "; " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
End If
'Take value in selected cell in Column A and put it in "B1" on another sheet
If Not Intersect(Target, Range("a:a")) Is Nothing Then
Sheets("Pricing calculator").Range("B1").Value = Target.Value
End If
Exitsub:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
RE: Using multiple events in Private Sub Worksheet_Change
Hi Tania,
Thank you for the forum question and I am sorry about the late answer.
Worksheet change events can be difficult to get right and by looking at your code I can not spot errors but you have the error handler which will by pass the second part of the event and also in the decision codes there are many ways of by passing the second part.
May I suggest that you test the code by stop through the lines, then you will be able to see if your code by pass the second part. To do this you will need to insert a BreakPoint in the code.
May I suggest the you insert a BreakPoint in front of the line Application.EnableEvents = True at the top of your code. Change something in column U and the code will stop at the BreakPoint. Then step through the rest of the code pressing F8. You will now be able to see if the code get to the second part.
If you want I can look at the file.
You can send it to: info@stl-training.co.uk
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Using multiple events in Private Sub Worksheet_Change
Hi, I figured out what was wrong. I needed to use a Private Sub worksheet_selectionchange() for the event referencing column A, and a Private Sub worksheet_Change() for the event referencing column U.
Many thanks
Tania
Training information:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Move data worksheet to worksheetTo move data from one worksheet to another, highlight the data. |