Results 1 to 4 of 4

VBA Code Stops Working when Protect Sheet Enabled

Threaded View

  1. #1
    Registered User
    Join Date
    03-23-2021
    Location
    New York
    MS-Off Ver
    13127
    Posts
    2

    VBA Code Stops Working when Protect Sheet Enabled

    My original spreadsheet had two columns that imported values based on VLookup functions. Both columns were locked for editing and protected to prevent users from changing data in those two columns. This works fine and as intended. I now need to add new columns that can contain one or more values, so I wrote some code to allow multiple entries for cells in the new column. That works too. However, when I protect the sheet, the locked columns work like before, but the multiple selections for the new column stops working. When I unprotect the sheet, everything works but I really need to protect the two original cells. I tried adding subs for unprotect and protect and call them before and after the script, but I get a compile error, Member already exists in an object module from which this object module derives. I am trying to solve this on my own and watched countless videos to no avail. I am hoping someone can point me in the right direction and either tell me what I am doing wrong and why, and what I need to research to resolve. Any help is greatly appreciated.

    Original Code - Works fine when the sheet is unprotected.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String
    Application.EnableEvents = True
    On Error GoTo Exitsub
    If Target.Column = 13 Or Target.Column = 8 Or Target.Column = 14 Or Target.Column = 15 Or Target.Column = 21 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
    Same Code using Protect/Unprotect Calls - This gives me the Compile error on Unprotect

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String
    
    unprotect
    
    Application.EnableEvents = True
    On Error GoTo Exitsub
    If Target.Column = 13 Or Target.Column = 8 Or Target.Column = 14 Or Target.Column = 15 Or Target.Column = 21 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
    
    protect
    
    End Sub
    
    Sub unprotect()
    
    End Sub
    
    Sub protect()
    
    End Sub
    This works based on guidance received.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String
    
    ActiveSheet.unprotect
    
    Application.EnableEvents = True
    On Error GoTo Exitsub
    If Target.Column = 13 Or Target.Column = 8 Or Target.Column = 14 Or Target.Column = 15 Or Target.Column = 21 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
    
    ActiveSheet.protect
    
    End Sub
    Last edited by NJA3; 03-24-2021 at 11:09 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Code stops working when count is zero
    By little_angel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2019, 07:47 AM
  2. [SOLVED] Code stops working
    By Excelski in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-27-2017, 12:23 PM
  3. Code stops working until I reboot
    By AndrewMac in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-15-2013, 07:46 PM
  4. Removing code line stops code working?!?!?
    By adamj1910 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2012, 02:36 PM
  5. [SOLVED] code stops working after X rows.
    By colwyn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-23-2008, 03:14 PM
  6. VBA code not working when I protect the sheet
    By ss_bb_24 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-10-2008, 04:47 AM
  7. VB Code stops working
    By Michelle K in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 04-28-2005, 05:06 PM

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