I'm a newbie to vba so, i'm not sure how to place the coding.
Infact i'm not sure how to protect and unprotect with the user form. If
I understand your post correctly you were talking about protecting the
cells that the userform uses and leaving the cells that need to be
entered manually unprotected. So the user cant enter any data into the
cells unless they use the userform.
That's correct.
- Select the range of cells that the user CAN enter directly into.
- Use the menu Format -> Cells... -> Protection and uncheck the Locked
box.
- Go to the menu Tools -> Protection -> Protect Sheet, and enter a
password in. Let's say it's 12345.
Right at this moment, the user will only be able to enter data into
the cells that you've unlocked.
Here is my code for the userform, if you could lead me in the right
direction I would really appreciate it.
Your code below is triggered whenever the OK button is clicked. One of
the things it does is to write data into the sheet. Except that now
you'd get an error when it tries to do that because the sheet is now
protected. The solution is to simply unprotect the sheet before you
first try to write to it:
Private Sub OKButton_Click()
On Error Resume Next
Dim activeRow As Integer
Dim activeCol As Integer
Dim errMsg As VbMsgBoxResult
Dim lindex As Integer
Dim ntoins As String
Dim wtoins As String
Dim nfound As Boolean
Dim rrow As Integer
activeRow = ActiveCell.Row
activeCol = ActiveCell.Column
lindex = ListBox.ListIndex
ntoins = ListBox.Column(0, lindex)
wtoins = ListBox.Column(1, lindex)
nfound = False
'has to start in bay 1
If activeCol <> 2 Then
errMsg = MsgBox("Please select a cell in bay 1", vbExclamation,
"Wrong Cell")
GoTo end_handler
End If
Here's where you first write to the sheet, so at this point simply add
the code:
'Unprotect the sheet before writing to it.
'We check the current protection status simply as
'a precaution.
If ActiveSheet.ProtectContents = True Then
ActiveSheet.Unprotect Password:="12345"
End If
If frameBay.radio_1 = True Then
With ActiveSheet
Cells(activeRow, 2) = ntoins
Cells(activeRow, 7) = wtoins
End With
End If
If frameBay.radio_1 = True Then
rrow = 0
ElseIf frameBay.radio_2 = True Then
rrow = 6
ElseIf frameBay.radio_3 = True Then
rrow = 21
ElseIf frameBay.radio_4 = True Then
rrow = 40
End If
With ActiveSheet
If rrow <> 0 Then
While nfound = False
If .Cells(rrow, 12).Value = "" Then
nfound = True
'insert the data
Cells(rrow, 12).Value = ntoins
Cells(rrow, 17).Value = wtoins
Else
rrow = rrow + 1
End If
Wend
End If
End With
end_handler:
'do nothing
Instead of doing nothing, here we'd turn protection back on:
'Reenable protection
If ActiveSheet.ProtectContents = False Then
ActiveSheet.Protect Password:="12345"
End If
But just to be extra cautious, I'd also add protection enablement to a
procedure called QueryClose. This is triggered whenever the user form
is closed, whether by clicking the X in the top right corner, shutting
down Excel or shutting down Windows.
You can just copy the following straight in:
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
'Reenable protection
If ActiveSheet.ProtectContents = False Then
ActiveSheet.Protect Password:="12345"
End If
That's all there is to it. Hope this helps.
