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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
186
John_w
J
B
  • Question Question
Copy and paste from one worksheet into a new workbook and save to a particular directory
Replies
3
Views
574
Joe4
Joe4
R
  • Solved
VBA - Combine worksheets from multiple workbooks
Replies
5
Views
346
rachel06
R
R
  • Question Question
Copy and unfilter in VBA
Replies
1
Views
180
Alex Blakenburg
A
I
  • Question Question
Hide command button on worksheet depending on worksheet cell value
Replies
2
Views
271
ipbr21054
I

Forum statistics

Threads
1,223,900
Messages
6,175,276
Members
452,629
Latest member
SahilPolekar

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