Results 1 to 8 of 8

Help: Macro NOT Working After Protecting Sheet

Threaded View

  1. #1
    Registered User
    Join Date
    03-09-2012
    Location
    Phoenix, Arizona
    MS-Off Ver
    Excel 2007
    Posts
    31

    Question Help: Macro NOT Working After Protecting Sheet

    Hello,

    I have a workbook that I don't want the users to be able to change anything of the format, columns, rows, etc. so I protected the worksheet.
    I have a macro saved in the sheet itself and not as a module that would allow users to select more than one option in the data validation drop-down lists. When I protect the worksheet I am not able to select more than one entry from the drop-down list. Hence, the macro is not working.

    See attached workbook for more information

    My question is: How can I protect the worksheet and at the same time have the macro work so I can select more than one option.

    thanks for your help

    below is the code that allows multiple selection

    
    Option Explicit
    ' 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
    
    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 = 13 Or 14 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
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1