Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    43

    Cool Unanswered: Code Help: Locking Cells

    Please help,

    For some reason every time I try to lock a cell based on a check box it gives me a 1004 error. Any suggestions or help would be appreciated. Code is below.

    Private Sub chkCar_Click()

    If chkCar.Value = False Then

    Sheet1.Unprotect
    Sheet1.Cells(30, 3).Interior.ColorIndex = 15
    Sheet1.Cells(30, 3).Interior.Pattern = xlSolid
    Sheet1.Cells(30, 3).Font.ColorIndex = 2
    Sheet1.Cells(30, 3) = "LOCKED"
    Sheet1.Range("C30").Locked = True
    Sheet1.Protect

    ElseIf chkCar.Value = True And Application.UserName = "TestUser" Then

    Sheet1.Unprotect
    Sheet1.Cells(30, 3).Interior.ColorIndex = 2
    Sheet1.Cells(30, 3).Interior.Pattern = xlSolid
    Sheet1.Range("C30").Locked = False
    Sheet1.Protect

    End If


    End Sub

    Billy

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ive just tested your code and it seems to be working fine for me,
    Is there anything else which may be occuring, Also where are you obtaining the error it might give some clues to what the problem may be

  3. #3
    Join Date
    Oct 2003
    Posts
    43

    Code Error

    Code Error happens at the following lines:
    Sheet1.Range("C30").Locked = True
    Sheet1.Range("C30").Locked = False

    Could this be to security settings?

    Billy

  4. #4
    Join Date
    Oct 2003
    Posts
    43

    Pushing this up.

    Bumping this up because I am still having trouble with it.

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Sorry havn't been able to look at this for you yet as i have been too busy (snowed under at work) and don't have Excel at home, maybe someone else can jump in.

    A few questions for you to answer though

    What kind of permissions are on the sheet already, Does the code work from another workbook if so whats the difference between the 2,

    Have you tried some superficial changes like changing sheet1. to something like activesheet or worksheets("Sheet1"), I tend to use the later

    if you quote out the locked statement does the rest work such as the protecting

    try throwing an IF statement and seeing if an error is thrown something like

    if range("C30").locked = True then
    msgbox "locked"
    end if

    this will see if you can check the locked property ratherr than trying to change it

    ill see if i have time to look in the morning to see if i can help you further or if i can think of something ill post back later

    David

  6. #6
    Join Date
    Oct 2003
    Posts
    43

    Answer:

    Here is the answer for the code. Turns out I forgot to cover the other two cells in the merged cell area.

    Private Sub chkCar_Click()
    'If box is checked then the City/State area will fill in for the drop off and pick up with information from above.


    If chkCar.Value = False Then

    Sheet1.Unprotect
    Sheet1.Cells(30, 3).Interior.ColorIndex = 15
    Sheet1.Cells(30, 3).Interior.Pattern = xlSolid
    Sheet1.Cells(30, 3).Font.ColorIndex = 2
    Sheet1.Cells(30, 3) = "LOCKED"
    Sheet1.Range("C30:E30").Locked = True
    Sheet1.Protect

    ElseIf chkCar.Value = True And Application.UserName = "WDWoodhams" Then

    Sheet1.Unprotect
    Sheet1.Cells(30, 3).Interior.ColorIndex = 2
    Sheet1.Cells(30, 3).Interior.Pattern = xlSolid
    Sheet1.Range("C30:E30").Locked = False
    Sheet1.Protect
    End If



    End Sub

    Thank you for your time,

    Billy

Posting Permissions

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