using multiple events private

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

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

resolvedResolved · 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 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:

Move data worksheet to worksheet

To move data from one worksheet to another, highlight the data.

Select and hold down the ALT key and position the mouse on the border of the selection until the mouse pointer displays four-headed arrows.

Drag the selection down to the destination worksheet tab.

When the arrow touches the tab, Excel switches to the desired worksheet. Now drag the selection to the correct position. Let go of the mouse and then the ALT key.

To copy data from one worksheet to another, select and hold down the CTRL+ALT keystroke combination and perform the steps above.

View all Excel hints and tips


Server loaded in 0.09 secs.