VBA Noobie Question about cut/paste VBA code and saving.

raisedwell

New Member
Joined
Mar 4, 2010
Messages
22
I do not have much experience with VBA and I have a worksheet where I need to have a specific cell (A2) to be formatted with a data validation list to allow the user to select multiple items from the validation list and the cell should return a list of all of the selections within the cell (A2) on it's own line. I was able to locate the following VBA code to accomplish this that I have cut/pasted into the specific worksheet using the Developer button:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Oldvalue As String
Dim Newvalue As String

Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("A2")) Is Nothing 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 & vbNewLine & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True

End Sub

While this seems to accomplish the goal, when I save it as a .xlsm file and re-open it, the cell no longer allows for multiple selections and only returns a single item from the list.

Other than cut/paste the code and saving, is there something I am doing wrong? Am I missing a step?

Any help provided will be greatly appreciated!

Thanks in advance.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Forum statistics

Threads
1,214,965
Messages
6,122,499
Members
449,089
Latest member
Raviguru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top