Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

using multiple events private

Forum 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

ResolvedVersion 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 courses

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Fill formulae across a sheet

To copy a formula down a spreadsheet where there is data underneath, to the left or to the right of the formula, double-click on the fill handle. The fill handle is the little black cross that appears in the bottom right-hand corner of the formula cell. Unfortunately, no similar facility exists to copy formulae across the sheet.

One reasonably quick way to copy an existing formula across a sheet is to select the formula and the cells on the right to which you want to copy it. Then press Ctrl+R to copy the formula across the selected range, or, if you are menu-minded, use the Edit|Fill|Right command.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.