Hello everyone,
I would like to get the opinion of forum members on a problem I'm having with a sheet. There is a range of cells (A9-A360 to J9-J360 (or A9-J360)) in the protected sheet, the range was locked and the sheet password-protected, which receives information from a userform. The process works fine: the userform delivers the data to the range everytime the "Submit" button is pressed, and the range stays locked even after the userform is dismissed. The problem comes when the user (me in this case) selects or clicks a few of the cells that are not locked down, that's when parts of the protected range become editable without notice.
I've tried clicking on the protected range before clicking anywhere else and the range remains locked, even to the point that I'm not able to select the cells in that range; but that same range becomes editable as soon as I click three or four of the unlocked cells in the sheet.
Could someone help me resolve this problem?
This is part of the code I'm using to send the data from the userform to the sheet:
Private Sub cmdSubmit_Click()
Dim resNextRow As Long
Dim ws As Worksheet
Dim celLock As Range
Dim Target As Range
Application.ScreenUpdating = False
Set ws = ActiveWorkbook.Worksheets("Sheet1")
If Len(txtItemCode.Text) = 0 Then Exit Sub
With ws
Set Target = .Range("A9:J360")
.Unprotect Password:="password"
.Cells.Locked = False
For Each celLock In Target
If celLock.Value <> "" Then
celLock.Locked = True
End If
Next celLock
resNextRow = WorksheetFunction.CountA(.Range("A:A")) + 7
.Cells(resNextRow, 1) = Time
.Cells(resNextRow, 2) = cboTechnicianName.Value
.Cells(resNextRow, 3) = cboRounds.Value
.Cells(resNextRow, 4) = cboSpaceNumber.Value
' several lines of code to place data... then
.Protect Password:="password"
End With
Application.ScreenUpdating = True
end sub
Does anyone know if I'm missing something in the code? Is there another reason as to why the range would become available like that? I have not locked down the entire sheet because there are cells which the user is supposed to edit; but the data in the range is to be left alone until a supervisor sees it and approves it.
Any help would be appreciated immensenly. Thank you in advance...
Bookmarks