Confusing Code
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Confusing Code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2016
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Confusing Code

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)



    'Plan Rejected
    If Target.Column = 12 Then
    ActiveSheet.Unprotect Password:="test"
    Target.Offset(0, -6) = 0
    'Target.Offset(0, 1) = Date + Time
    Target = ""
    'Range("b" & Target.Row & ":bz" & Target.Row).Locked = True
    ActiveSheet.Protect Password:="test"
    End If

    I am confused by the following code. The two rows that you see as commented out do not run when they are uncommented. If I switch the two target.offset codes the first one always runs and the second one does not. The locked range used to work until I added the second offset and now it does not run even if bad code gets commented out.

  2. #2
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,662
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Confusing Code

    Kevin, be more specific and use... Target.Value = "" and that should hopefully do it.
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  3. #3
    New Member
    Join Date
    Mar 2016
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Confusing Code

    Quote Originally Posted by Snakehips View Post
    Kevin, be more specific and use... Target.Value = "" and that should hopefully do it.
    That line of code works. But I tried it anyway and no change.
    Last edited by Kevin0427; Jul 19th, 2019 at 01:24 PM.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,585
    Post Thanks / Like
    Mentioned
    444 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Confusing Code

    You're code works perfectly well for me.
    Do you have any other Event code working on that sheet?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Mar 2016
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Confusing Code

    Yes. Lots.

  6. #6
    New Member
    Join Date
    Mar 2016
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Confusing Code

    Can I use this code?

    target.row.locked = true

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,585
    Post Thanks / Like
    Mentioned
    444 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Confusing Code

    In that case try
    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Plan Rejected
       If Target.Column = 12 Then
          Cancel = True
          Application.EnableEvents = False
          ActiveSheet.Unprotect Password:="test"
          Target.Offset(0, -6) = 0
          Target.Offset(0, 1) = Date + Time
          Target = ""
          Range("b" & Target.Row & ":bz" & Target.Row).Locked = True
          ActiveSheet.Protect Password:="test"
       End If
       Application.EnableEvents = True
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    4,941
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Confusing Code

    try this update

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    
        On Error GoTo exitsub
        Me.Unprotect Password:="test"
        
    'Plan Rejected
        With Target
            If .Column = 12 Then
            Application.EnableEvents = False
            Cancel = True
                .Offset(0, -6).Value = 0
                .Offset(0, 1).Value = Now()
                .Value = ""
            End If
        End With
        Me.Range("b" & Target.Row).Resize(, 77).Locked = True
        
    exitsub:
        Me.Protect Password:="test"
        Application.EnableEvents = True
    End Sub
    Dave
    Last edited by dmt32; Jul 19th, 2019 at 01:34 PM.

  9. #9
    New Member
    Join Date
    Mar 2016
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Confusing Code

    That worked. Why do I not need those changes on all the other events?

  10. #10
    New Member
    Join Date
    Mar 2016
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Confusing Code

    Quote Originally Posted by Fluff View Post
    In that case try
    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Plan Rejected
       If Target.Column = 12 Then
          Cancel = True
          Application.EnableEvents = False
          ActiveSheet.Unprotect Password:="test"
          Target.Offset(0, -6) = 0
          Target.Offset(0, 1) = Date + Time
          Target = ""
          Range("b" & Target.Row & ":bz" & Target.Row).Locked = True
          ActiveSheet.Protect Password:="test"
       End If
       Application.EnableEvents = True
    End Sub
    I meant to quote this. It worked. Why do I not need it in the rest of the event codes?
    Last edited by Kevin0427; Jul 19th, 2019 at 01:40 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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