Formula is disappearing after I have applied a macros

kleclark

New Member
Joined
Nov 26, 2012
Messages
12
Hi,
I recently added a macro published online from Contextures (Select Multiple Items from Excel Data Validation List | Contextures Blog) that allows multiple items to be selected from a dropdown menu into one cell, however I specified this was only to be applied to one column. Now when I enter a formula in any other column the formula disappears after a value is calculated.

I am not familiar with macros/vba and do not know how to recognise the component of the coding that is causing this. Any ideas?

Best,
Kerri
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Post the code and tell us what column you want it applied to.
 
Upvote 0
Great - thank you.

I want the macro to only apply to column 26.

' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 26 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
lUsed = InStr(1, oldVal, newVal)
If lUsed > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
Else
Target.Value = Replace(oldVal, newVal & ", ", "")
End If
Else
Target.Value = oldVal _
& ", " & newVal
End If

End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub


Thank you for your help.

Best,
Kerri
 
Last edited:
Upvote 0
Try changing this line:
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)



To this:
Set rngDV = Columns("Z:Z").SpecialCells(xlCellTypeAllValidation)
 
Upvote 0
Try changing this line:
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)



To this:
Set rngDV = Columns("Z:Z").SpecialCells(xlCellTypeAllValidation)

Thank you very much JoeMo. It appears to be working. I only say appears as I removed the macro before and added the VLookup formula to the column. I then applied the macros modified as you suggested and the formula is still in the cells.

Thank you again. I greatly appreciate your time and advice.

Best,
Kerri
 
Upvote 0
Thank you very much JoeMo. It appears to be working. I only say appears as I removed the macro before and added the VLookup formula to the column. I then applied the macros modified as you suggested and the formula is still in the cells.

Thank you again. I greatly appreciate your time and advice.

Best,
Kerri

Hi JoeMo,

I have just discovered that when I protect my worksheet the macros no longer works. Is there a way around this please?

Best,
Kerri
 
Upvote 0
Hi JoeMo,

I have just discovered that when I protect my worksheet the macros no longer works. Is there a way around this please?

Best,
Kerri
Try adding the line in red with your password inserted between the quote marks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
Me.protect password:= "Your pswd here", UserInterfaceOnly:= True
If Target.Count > 1 Then GoTo exitHandler
 
Upvote 0
Try adding the line in red with your password inserted between the quote marks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long
Me.protect password:= "Your pswd here", UserInterfaceOnly:= True
If Target.Count > 1 Then GoTo exitHandler

Thank you very much! The formula works. Yet again you have provided me with invaluable help. Your site is wonderful!

Forever grateful,
Kerri
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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