+ Reply to Thread
Results 1 to 1 of 1

Cell Range Lock Down Problem

Hybrid View

  1. #1
    Registered User
    Join Date
    10-20-2012
    Location
    salinas, ca
    MS-Off Ver
    Excel 2010
    Posts
    25

    Cell Range Lock Down Problem

    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...
    Last edited by Julez80s; 11-28-2012 at 07:47 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1