I am working on an Excel form to allow users to submit data about decisions made and topics covered in meetings as part of a larger project. I will have a lot of different people entering data in this Excel file, so I want to lock as many cells as I can to make sure things aren't accidentally deleted, and users can only type in the cells that correspond to the form. I have three buttons on this form; "Submit Decision", "Save, and "Reset". I successfully protected the workbook upon opening using this code:
Private Sub Workbook_Open()
Dim frm As Worksheet
Set frm = ThisWorkbook.Sheets("Form")
With frm
.Range("I6:L6").Locked = False
.Range("I8:L11").Locked = False
.Range("I13:L13").Locked = False
.Range("I15:L15").Locked = False
.Range("I24:L27").Locked = False
.Range("I29:L32").Locked = False
.Range("I34:L37").Locked = False
.Range("I39:L39").Locked = False
.Protect UserInterfaceOnly:=True
End With
End Sub
But now my issue is that the buttons don't allow you to click on them. I've tried unprotecting the cells behind the buttons (.Range("K17:L17"), .Range("J41:L41")), but this doesn't work. Is there something I'm missing to allow these buttons to be clicked but still protect the rest of the worksheet? And, while I'm at it, is there a way to lock the size of the buttons so users can't shrink them accidentally? (This is a problem I ran into when allowing a few test users access to the file....). I have included the code for the three buttons below. Thanks in advance!
Private Sub cmbDecision1_Click()
Dim msgValue As VbMsgBoxResult
msgValue = MsgBox("Do you want to save this decision?", vbYesNo + vbQuestion, "SubmitDecision")
If msgValue = vbYes Then
Call SubmitDecision
End If
End Sub
Private Sub cmdReset_Click()
Dim msgValue As VbMsgBoxResult
msgValue = MsgBox("Do you want to reset the form?", vbYesNo + vbQuestion, "Reset")
If msgValue = vbYes Then
Call Reset
End If
End Sub
Private Sub cmdSave_Click()
If Validate = True Then
Dim msgValue As VbMsgBoxResult
msgValue = MsgBox("Do you want to save the status report?", vbYesNo + vbQuestion, "Save")
If msgValue = vbYes Then
Call Save
Call Reset
End If
End If
End Sub
Private Sub Workbook_Open()
Dim frm As Worksheet
Set frm = ThisWorkbook.Sheets("Form")
With frm
.Range("I6:L6").Locked = False
.Range("I8:L11").Locked = False
.Range("I13:L13").Locked = False
.Range("I15:L15").Locked = False
.Range("I24:L27").Locked = False
.Range("I29:L32").Locked = False
.Range("I34:L37").Locked = False
.Range("I39:L39").Locked = False
.Protect UserInterfaceOnly:=True
End With
End Sub
But now my issue is that the buttons don't allow you to click on them. I've tried unprotecting the cells behind the buttons (.Range("K17:L17"), .Range("J41:L41")), but this doesn't work. Is there something I'm missing to allow these buttons to be clicked but still protect the rest of the worksheet? And, while I'm at it, is there a way to lock the size of the buttons so users can't shrink them accidentally? (This is a problem I ran into when allowing a few test users access to the file....). I have included the code for the three buttons below. Thanks in advance!
Private Sub cmbDecision1_Click()
Dim msgValue As VbMsgBoxResult
msgValue = MsgBox("Do you want to save this decision?", vbYesNo + vbQuestion, "SubmitDecision")
If msgValue = vbYes Then
Call SubmitDecision
End If
End Sub
Private Sub cmdReset_Click()
Dim msgValue As VbMsgBoxResult
msgValue = MsgBox("Do you want to reset the form?", vbYesNo + vbQuestion, "Reset")
If msgValue = vbYes Then
Call Reset
End If
End Sub
Private Sub cmdSave_Click()
If Validate = True Then
Dim msgValue As VbMsgBoxResult
msgValue = MsgBox("Do you want to save the status report?", vbYesNo + vbQuestion, "Save")
If msgValue = vbYes Then
Call Save
Call Reset
End If
End If
End Sub
