Confusing Code
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

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

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

    Default Re: Confusing Code

    Without knowing what your other code is, I've no idea.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Confusing Code

    Quote Originally Posted by Fluff View Post
    Without knowing what your other code is, I've no idea.
    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
    'Invoice Sent
    'First change
    If Target.Column = 14 Then
        ActiveSheet.Unprotect Password:="test"
        If Target.Offset(0, 1) = "" Then
            Target.Offset(0, 1) = Date + Time
            Target = ""
    'All other changes
        Else
            Target.Offset(0, 27) = Date + Time
            If Target = "" Then
                Target = ""
            Else
                Target = ""
            End If
        End If
    ActiveSheet.Protect Password:="test"
    End If
    'Payment Received
    'First change
    If Target.Column = 16 Then
        ActiveSheet.Unprotect Password:="test"
        If Target.Offset(0, 1) = "" Then
            Target.Offset(0, 1) = Date + Time
            Target = ""
    'All other changes
        Else
            Target.Offset(0, 26) = Date + Time
            If Target = "" Then
                Target = ""
            Else
                Target = ""
            End If
        End If
        ActiveSheet.Protect Password:="test"
    End If
    'Entered in GIS
    'First change
    If Target.Column = 19 Then
        ActiveSheet.Unprotect Password:="test"
        If Target.Offset(0, 26) = "" Then
            Target.Offset(0, 26) = Date + Time
            Target = ""
    'All other changes
        Else
            Target.Offset(0, 27) = Date + Time
            If Target = "" Then
                Target = ""
            Else
                Target = ""
            End If
        End If
        ActiveSheet.Protect Password:="test"
    End If
    'Plans Scanned
    'First change
    If Target.Column = 20 Then
        ActiveSheet.Unprotect Password:="test"
        If Target.Offset(0, 27) = "" Then
            Target.Offset(0, 27) = Date + Time
            Target = ""
    'All other changes
        Else
            Target.Offset(0, 28) = Date + Time
            If Target = "" Then
                Target = ""
            Else
                Target = ""
            End If
        End If
        ActiveSheet.Protect Password:="test"
    End If
    End Sub
    Do I or should I be using a lot of subs instead of 1? Does that cause it? Sorry all my tabs and spacing is gone in the paste!!! Looks bad.
    Last edited by Fluff; Jul 19th, 2019 at 01:51 PM. Reason: code tags

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

    Default Re: Confusing Code

    That code is not the problem. You have other event code such as a Worksheet_Change event
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Confusing Code

    Quote Originally Posted by Fluff View Post
    That code is not the problem. You have other event code such as a Worksheet_Change event
    This. And a few filters

    Private Sub Worksheet_Change(ByVal Target As Range)

    'Maximo Entered
    ActiveSheet.Unprotect Password:="test"
    If Target.Column = 18 Then
    If Target.Offset(0, 25) = "" Then
    Target.Offset(0, 25) = Date + Time
    Else
    Target.Offset(0, 26) = Date + Time
    End If
    End If
    ActiveSheet.Protect Password:="test"
    End Sub

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

    Default Re: Confusing Code

    That code is relocking the sheet, which is causing you the problem.
    Select this line in the doubleclick event
    Code:
       If Target.Column = 12 Then
    Then press F9, that line should get a brown highlight.
    Then on the sheet doubleclick in col L, the above line of code should be highlighted, press F8 to step through the code & you can watch what happens.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •