Excel visual basic code works on one worksheet but not on another

truckexpert

New Member
Joined
Jul 30, 2019
Messages
2
This code works in one TAB (sheet) but not in another. Any help appreciated!

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from [URL]https://trumpexcel.com[/URL]
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 4 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
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,230
.
The only thing I can see from the code ... it will only work for one sheet at a time.

Explanation : If the code is presently in the Sheet Module for Sheet1, the code will only work for Sheet1.

If you wanted to have the code work in another sheet, the code will need to be copied and pasted into the
Sheet Module of that other sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,030
Messages
5,466,121
Members
406,468
Latest member
Toto Li

This Week's Hot Topics

Top