Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Protect Cell (Excel 2003)

    Hi

    Is it possible to do the following?

    IF I7 = "No Disc" then lock cell J7 and copy H7 to M7,

    Otherwise every thing left as is to allow manual entry for discount %

    The sheet will be locked, but column J is unlocked.


    The range is from row 7 to row 596

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protect Cell (Excel 2003)

    It's not possible to lock/unlock cells using formulae as far as I know, but you can certainly do it in VBA.

    In the attacehd, I've used this code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 9 Then
    Sheet1.Unprotect
    If Target = "No Disc" Then
    Target.Offset(0, 4).Value = Target.Offset(0, -1).Value
    Target.Offset(0, 1).Locked = True
    Else
    Target.Offset(0, 4).ClearContents
    Target.Offset(0, 1).Locked = False
    End If
    Sheet1.Protect
    End If
    End Sub

    (If you're going to password-protect the sheet, add the password in quotes after the Sheet1.Unprotect and Sheet1.Protect statements).
    Waggers
    If at first you do succeed, you've probably missed something.

  3. #3
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protect Cell (Excel 2003)

    Ok, it's actually something fairly simple - you need to change Chemicals to Worksheets("Chemicals"):

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 9 Then
    Worksheets("Chemicals").Unprotect
    If Target = "No Disc" Then
    Target.Offset(0, 4).Value = Target.Offset(0, -1).Value
    Target.Offset(0, 1).Locked = True
    Else
    Target.Offset(0, 4).ClearContents
    Target.Offset(0, 1).Locked = False
    End If
    Worksheets("Chemicals").Protect
    End If
    End Sub

    So why, I hear you ask, did it work in the example sheet? Each worksheet actually has two names - an Excel name (that you see on the sheet tabs) and a VBA name (visible only in the VBE). In the example, both had the same name ("Sheet1"). In the attached screenshot, the sheet in question has Excel name "Chemicals" but the VBA name is still "Sheet1". My original code referred to the worksheet by it's VBA name rather than it's Excel name.

    You can, if you wish, change the VBA name of a worksheet from the properties window in the VBE (hit F4 to show this). You'll see that there are two names : Name and (Name). Name is the Excel name, (Name) is the VBA name.
    Waggers
    If at first you do succeed, you've probably missed something.

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Protect Cell (Excel 2003)

    Hi Waggers

    There is still a problem, when I select the code 105206 it puts "No Disc" in Cell which is my formula, then the macro doesn't run unless I manually unprotect the sheet then click in cell I7 then the macro runs.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protect Cell (Excel 2003)

    Sorry - try this instead:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim T As Range
    Set T = Cells(Target.Row, 9)
    Worksheets("Chemicals").Unprotect
    If T = "No Disc" Then
    T.Offset(0, 4).Value = T.Offset(0, -1).Value
    T.Offset(0, 1).Locked = True
    Else
    T.Offset(0, 4).ClearContents
    T.Offset(0, 1).Locked = False
    End If
    Worksheets("Chemicals").Protect
    End Sub
    Waggers
    If at first you do succeed, you've probably missed something.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Protect Cell (Excel 2003)

    Try using the Worksheet_Change event rather than the Worksheet_SelectionChange one. For the latter, Target is the cell you're moving to not from.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Protect Cell (Excel 2003)

    <img src=/S/clever.gif border=0 alt=clever width=15 height=15> Thanks Rory, I knew I was missing something somewhere!
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Protect Cell (Excel 2003)

    Hi Rory

    Sorry to be a drag, I did as you suggested and the macro will not run at all, even if I manualy unprotect and click in I7 as it did with Waggers suggestion.

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Protect Cell (Excel 2003)

    Screenshot deleted by HansV. Don't attach images larger than 640x480, please.

    Hi Waggers

    Thanks for your reply, the code works fine in the example worksheet, but it does not when I apply it to the Master Worksheet.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 9 Then
    Chemicals.Unprotect
    If Target = "No Disc" Then
    Target.Offset(0, 4).Value = Target.Offset(0, -1).Value
    Target.Offset(0, 1).Locked = True
    Else
    Target.Offset(0, 4).ClearContents
    Target.Offset(0, 1).Locked = False
    End If
    Chemicals.Protect
    End If
    End Sub

    I have made sure I used the same cells in the example so it wouid copy and paste nicely but for some reason it does not lock J7 or transfer H7 to M7 as you can see I changed the worksheet address from Sheet1 to Chemicals which is the name of the sheet, any suggestions please, see attachment to check the cells are the same.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Protect Cell (Excel 2003)

    Ah, sorry - I misread the original post as saying column I was unlocked. If you want it to happen automatically, you may need to start with columns I and J unlocked and just lock either I or J in each row as one or the other gets filled in? Would that be OK?
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protect Cell (Excel 2003)

    Try this. it puts the code in a change event instead.

    Steve

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rCell As Range
    Dim rng As Range
    Set rng = Intersect(Target, Range("I7:I596"))
    If rng Is Nothing Then Exit Sub
    Me.Unprotect
    For Each rCell In rng
    Application.EnableEvents = False
    With rCell
    If .Value = "No Disc" Then
    .Offset(0, 4).Value = .Offset(0, -1).Value
    .Offset(0, 1).Locked = True
    Else
    .Offset(0, 4).ClearContents
    .Offset(0, 1).Locked = False
    End If
    End With
    Application.EnableEvents = True
    Next
    Me.Protect
    End Sub

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Protect Cell (Excel 2003)

    Hi Steve

    Thanks for the reply, your formula locks the adjacent cell in Col J but does not transfer the contents of H7 to M7

    Regards

    Braddy
    If you are a fool at forty, you will always be a fool

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protect Cell (Excel 2003)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> it does in my tests.

    Perhaps I do not understand your setup, Could you elaborate?

    Steve

  14. #14
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Protect Cell (Excel 2003)

    Hi Steve

    I not sure I have the where withall to able to explain this, not only that but I have experienced another problem if I enter 105260 the macro runs and locks the adjacent cell in Col J, but If I delete this code and enter another one (say for a change of mind) Col J stil stays locked even if I7 doesn't contain "No Disc". I think I will have to give more thought to this, and come back when I can explain better.

    I am grateful for all the replies

    Braddy
    If you are a fool at forty, you will always be a fool

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Protect Cell (Excel 2003)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>Could you attach a sample sheet that displays the problem? The worksheet I created works as I expect it to do. It unlocks and locks column J whenever a different value is entered into I.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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