Trying to set a cell to locked or unlocked and I get an error '5' message?

S

Strider89

New Member
Joined
Dec 28, 2017
Messages
22
I have a checkbox that does a lot of work on a worksheet depending on if its value is true or false. It was working fine until today, I decided that I wanted to have the checkbox also Lock or Unlock a cell dependent on its value.

The whole code is as follows:

Code: 
[INDENT=2]Private Sub CheckBox4_Click()[/INDENT]
[INDENT=2]Application.ScreenUpdating = False[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]Dim ws As Worksheet[/INDENT]
[INDENT=2]Dim sheetName As String[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]If CheckBox4.Value = True Then[/INDENT]
[INDENT=2]    ActiveWorkbook.Unprotect "Gravestrider89"[/INDENT]
[INDENT=2]    Worksheets("SUMMARY").Unprotect "Gravestrider89"[/INDENT]
[INDENT=2]    sheetName = Sheets("Control").Cells(16, "I")[/INDENT]
[INDENT=2]    If sheetName = "" Then[/INDENT]
[INDENT=2]        MsgBox "You must enter a valid Allowance descriptor. No entry was detected."[/INDENT]
[INDENT=2]        CheckBox4.Value = False[/INDENT]
[INDENT=2]        Exit Sub[/INDENT]
[INDENT=2]    End If[/INDENT]
[INDENT=2]    If Len(sheetName) > 31 Then[/INDENT]
[INDENT=2]        MsgBox "Worksheet tab names cannot be greater than 31 characters in length."[/INDENT]
[INDENT=2]        Application.EnableEvents = False[/INDENT]
[INDENT=2]        Sheets("Control").Cells(16, "I").ClearContents[/INDENT]
[INDENT=2]        Application.EnableEvents = True[/INDENT]
[INDENT=2]        CheckBox4.Value = False[/INDENT]
[INDENT=2]        Exit Sub[/INDENT]
[INDENT=2]    End If[/INDENT]
[INDENT=2]    Dim IllegalCharacter(1 To 7) As String, i As Integer[/INDENT]
[INDENT=2]    IllegalCharacter(1) = "/"[/INDENT]
[INDENT=2]    IllegalCharacter(2) = "\"[/INDENT]
[INDENT=2]    IllegalCharacter(3) = "["[/INDENT]
[INDENT=2]    IllegalCharacter(4) = "]"[/INDENT]
[INDENT=2]    IllegalCharacter(5) = "*"[/INDENT]
[INDENT=2]    IllegalCharacter(6) = "?"[/INDENT]
[INDENT=2]    IllegalCharacter(7) = ":"[/INDENT]
[INDENT=2]    For i = 1 To 7[/INDENT]
[INDENT=2]        If InStr(sheetName, (IllegalCharacter(i))) > 0 Then[/INDENT]
[INDENT=2]            MsgBox "You used a character that violates sheet naming rules. Please refrain from the following characters: / \ [ ] * ? : "[/INDENT]
[INDENT=2]            Application.EnableEvents = False[/INDENT]
[INDENT=2]            Sheets("Control").Cells(16, "I").ClearContents[/INDENT]
[INDENT=2]            Application.EnableEvents = True[/INDENT]
[INDENT=2]            CheckBox4.Value = False[/INDENT]
[INDENT=2]            Exit Sub[/INDENT]
[INDENT=2]        End If[/INDENT]
[INDENT=2]    [/INDENT]
[INDENT=2]    Next i[/INDENT]
[INDENT=2]    If Sheets("Control").Range("I16") = Sheets("Control").Range("I17") Then[/INDENT]
[INDENT=2]        MsgBox "There is already an Allowance with this name. Please choose a different name."[/INDENT]
[INDENT=2]        Application.EnableEvents = False[/INDENT]
[INDENT=2]            Sheets("Control").Cells(16, "I").ClearContents[/INDENT]
[INDENT=2]            Application.EnableEvents = True[/INDENT]
[INDENT=2]            CheckBox4.Value = False[/INDENT]
[INDENT=2]            Exit Sub[/INDENT]
[INDENT=2]        End If[/INDENT]
[INDENT=2]        If Sheets("Control").Range("I16") = Sheets("Control").Range("I18") Then[/INDENT]
[INDENT=2]        MsgBox "There is already an Allowance with this name. Please choose a different name."[/INDENT]
[INDENT=2]        Application.EnableEvents = False[/INDENT]
[INDENT=2]            Sheets("Control").Cells(16, "I").ClearContents[/INDENT]
[INDENT=2]            Application.EnableEvents = True[/INDENT]
[INDENT=2]            CheckBox4.Value = False[/INDENT]
[INDENT=2]            Exit Sub[/INDENT]
[INDENT=2]        End If[/INDENT]
[INDENT=2]        If Sheets("Control").Range("I16") = Sheets("Control").Range("I21") Then[/INDENT]
[INDENT=2]        MsgBox "There is already an Other Item with this name. Please choose a different name."[/INDENT]
[INDENT=2]        Application.EnableEvents = False[/INDENT]
[INDENT=2]            Sheets("Control").Cells(16, "I").ClearContents[/INDENT]
[INDENT=2]            Application.EnableEvents = True[/INDENT]
[INDENT=2]            CheckBox4.Value = False[/INDENT]
[INDENT=2]            Exit Sub[/INDENT]
[INDENT=2]        End If[/INDENT]
[INDENT=2]        If Sheets("Control").Range("I16") = Sheets("Control").Range("I22") Then[/INDENT]
[INDENT=2]        MsgBox "There is already an Other Item with this name. Please choose a different name."[/INDENT]
[INDENT=2]        Application.EnableEvents = False[/INDENT]
[INDENT=2]            Sheets("Control").Cells(16, "I").ClearContents[/INDENT]
[INDENT=2]            Application.EnableEvents = True[/INDENT]
[INDENT=2]            CheckBox4.Value = False[/INDENT]
[INDENT=2]            Exit Sub[/INDENT]
[INDENT=2]        End If[/INDENT]
[INDENT=2]        If Sheets("Control").Range("I16") = Sheets("Control").Range("I23") Then[/INDENT]
[INDENT=2]        MsgBox "There is already an Other Item with this name. Please choose a different name."[/INDENT]
[INDENT=2]        Application.EnableEvents = False[/INDENT]
[INDENT=2]            Sheets("Control").Cells(16, "I").ClearContents[/INDENT]
[INDENT=2]            Application.EnableEvents = True[/INDENT]
[INDENT=2]            CheckBox4.Value = False[/INDENT]
[INDENT=2]            Exit Sub[/INDENT]
[INDENT=2]        End If[/INDENT]
[INDENT=2]    If WorksheetExists(sheetName) Then[/INDENT]
[INDENT=2]        Worksheets(sheetName).Visible = -1[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Rows("47").EntireRow.Hidden = False[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 2).Value = "ALL 1:"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 3).Value = "='Control'!I16"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 3).NumberFormat = "General"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 4).Value = "='Control'!K16"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 5).Value = "='Control'!L16"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 6).Value = "=" & sheetName & "!$H$69"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 7).Value = "=" & sheetName & "!$J$69"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 8).Value = "=" & sheetName & "!$N$69"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 9).Value = "=" & sheetName & "!$P$69"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 10).Value = "=SUM(F47:I47)/D47"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 11).Value = "=L47/F3"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 12).Value = "=" & sheetName & "!$U$69"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 13).Value = "=L47/$K$57"[/INDENT]
[INDENT=2]        ActiveWorkbook.Protect "Gravestrider89", True, True[/INDENT]
[INDENT=2]        Sheets(sheetName).Protect "Gravestrider89", True, True[/INDENT]
[INDENT=2]        Sheets("SUMMARY").Protect "Gravestrider89", True, True[/INDENT]
[INDENT=2]        Exit Sub[/INDENT]
[INDENT=2]    Else[/INDENT]
[INDENT=2]        Set ws = Sheet34[/INDENT]
[INDENT=2]        ws.Name = sheetName[/INDENT]
[INDENT=2]        ws.Protect "Gravestrider89", True, True[/INDENT]
[INDENT=2]        ws.EnableSelection = xlUnlockedCells[/INDENT]
[INDENT=2]        Application.CutCopyMode = False[/INDENT]
[INDENT=2]
[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Rows("47").EntireRow.Hidden = False[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 2).Value = "ALL 1:"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 3).Value = "='Control'!I16"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 3).NumberFormat = "General"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 4).Value = "='Control'!K16"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 5).Value = "='Control'!L16"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 6).Value = "=" & ws.Name & "!$H$69"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 7).Value = "=" & ws.Name & "!$J$69"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 8).Value = "=" & ws.Name & "!$N$69"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 9).Value = "=" & ws.Name & "!$P$69"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 10).Value = "=SUM(F47:I47)/D47"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 11).Value = "=L47/F3"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 12).Value = "=" & ws.Name & "!$U$69"[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Cells(47, 13).Value = "=L47/$K$57"[/INDENT]
[INDENT=2]        [/INDENT]
[INDENT=2]        ActiveWorkbook.Protect "Gravestrider89", True, True[/INDENT]
[INDENT=2]        Sheets(sheetName).Protect "Gravestrider89", True, True[/INDENT]
[INDENT=2]        Sheets("SUMMARY").Protect "Gravestrider89", True, True[/INDENT]
[INDENT=2]        [/INDENT]
[INDENT=2]        Worksheets("Control").Activate[/INDENT]
[INDENT=2]        [/INDENT]
[INDENT=2]        Worksheets("Control").Cells("I16").Locked = True[/INDENT]
[INDENT=2]        [/INDENT]
[INDENT=2]    End If[/INDENT]
[INDENT=2]     [/INDENT]
[INDENT=2]End If[/INDENT]
[INDENT=2]If CheckBox4.Value = False Then[/INDENT]
[INDENT=2]    ActiveWorkbook.Unprotect "Gravestrider89"[/INDENT]
[INDENT=2]    Worksheets("SUMMARY").Unprotect "Gravestrider89"[/INDENT]
[INDENT=2]    sheetName = Sheets("Control").Cells(16, "I")[/INDENT]
[INDENT=2]    [/INDENT]
[INDENT=2]    If WorksheetExists(sheetName) Then[/INDENT]
[INDENT=2]        Worksheets(sheetName).Visible = 2[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Rows("47").EntireRow.ClearContents[/INDENT]
[INDENT=2]        Worksheets("SUMMARY").Rows("47").EntireRow.Hidden = True[/INDENT]
[INDENT=2]        ActiveWorkbook.Protect "Gravestrider89", True, True[/INDENT]
[INDENT=2]        Sheets(sheetName).Protect "Gravestrider89", True, True[/INDENT]
[INDENT=2]        Sheets("SUMMARY").Protect "Gravestrider89", True, True[/INDENT]
[INDENT=2]    [/INDENT]
[INDENT=2][COLOR=#ff0000]    Worksheets("Control").Cells("I16").Locked = False[/COLOR][/INDENT]
[INDENT=2]    [/INDENT]
[INDENT=2]    End If[/INDENT]
[INDENT=2]    [/INDENT]
[INDENT=2]End If[/INDENT]
[INDENT=2]    Application.ScreenUpdating = True[/INDENT]
[INDENT=2]End Sub[/INDENT]
The code highlighted in red is where I am running into an issue. For some reason, Excel is saying this is an invalid procedure or argument. Now, I am still an amateur when it comes to VBA excel, so I apologize if this is a simple fix, but I simply cannot figure this out. I have tried setting a worksheet unprotect and protect feature before and after the line of code, respectively, and still to no avail.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
Hi & welcome to the board
That line be either
Code: 
Worksheets("Control").Range("I16").Locked = False
or
Code: 
Worksheets("Control").Cells(16, "I").Locked = False
You've basically used a range address, with cells
 
Upvote 0
Strider89 said:
Code: 
[table="width: 500"]
[tr]
	[td]Dim IllegalCharacter(1 To 7) As String, i As Integer
IllegalCharacter(1) = "/"
IllegalCharacter(2) = "\"
IllegalCharacter(3) = "["
IllegalCharacter(4) = "]"
IllegalCharacter(5) = "*"
IllegalCharacter(6) = "?"
IllegalCharacter(7) = ":"
For i = 1 To 7
    If InStr(sheetName, (IllegalCharacter(i))) > 0 Then
        MsgBox "You used a character that violates sheet naming rules. Please refrain from the following characters: / \ [ ] * ? : "
        Application.EnableEvents = False
        Sheets("Control").Cells(16, "I").ClearContents
        Application.EnableEvents = True
        CheckBox4.Value = False
        Exit Sub
    End If
Next i[/td]
[/tr]
[/table]
Click to expand...
Fluff's reply above should answer your question, but I just wanted to point out that a quick look at your code suggests that you can replace the above snippet of code from your original post with the following...
Code: 
[table="width: 500"]
[tr]
	[td]If sheetName Like "*[/\[*?:]*" Or sheetName Like "*]*" Then
  MsgBox "You used a character that violates sheet naming rules. Please refrain from the following characters: / \ [ ] * ? : "
  Application.EnableEvents = False
  Sheets("Control").Cells(16, "I").ClearContents
  Application.EnableEvents = True
  CheckBox4.Value = False
  Exit Sub
End If[/td]
[/tr]
[/table]
 
Upvote 0
Thank you for your response! I adjusted the code as you suggested, and while it does address the issue of the Error message, the code does not adjust the T/F value of the Locked feature of the cell. The cell simply stays as it is.
 
Upvote 0
I can't see anything in the code that would account for that.
The easiest way to debug the code is if you step through the code line by line (using F8) & see what happens.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Ottsel
  • Question Question
Create & Secure PDFS with Excel
Replies
3
Views
272
John_w
J
I
  • Question Question
Unable to run VBA code
Replies
6
Views
165
RoryA
RoryA
A
  • Question Question
VBA help - Locking & unlocking on certain times of day
2
Replies
17
Views
469
dmt32
D
B
  • Question Question
Copy and paste from one worksheet into a new workbook and save to a particular directory
Replies
3
Views
731
Joe4
Joe4
S
  • Question Question
Excel VBA Help
Replies
1
Views
102
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,021
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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