Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Formula is disappearing after I have applied a macros

  1. #1
    New Member
    Join Date
    Nov 2012
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula is disappearing after I have applied a macros

    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

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,720
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Formula is disappearing after I have applied a macros

    Post the code and tell us what column you want it applied to.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    New Member
    Join Date
    Nov 2012
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula is disappearing after I have applied a macros

    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 by kleclark; Nov 27th, 2012 at 11:37 PM. Reason: Mod to macro

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,720
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Formula is disappearing after I have applied a macros

    Try changing this line:
    Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)



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

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    New Member
    Join Date
    Nov 2012
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula is disappearing after I have applied a macros

    Quote Originally Posted by JoeMo View Post
    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

  6. #6
    New Member
    Join Date
    Nov 2012
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula is disappearing after I have applied a macros

    Quote Originally Posted by kleclark View Post
    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

  7. #7
    MrExcel MVP
    Join Date
    May 2009
    Posts
    15,720
    Post Thanks / Like
    Mentioned
    23 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Formula is disappearing after I have applied a macros

    Quote Originally Posted by kleclark View Post
    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
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  8. #8
    New Member
    Join Date
    Nov 2012
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula is disappearing after I have applied a macros

    Quote Originally Posted by JoeMo View Post
    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •