+ Reply to Thread
Results 1 to 15 of 15

Combining two vba codes in the same sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    05-04-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2013
    Posts
    12

    Post Combining two vba codes in the same sheet

    Hello,
    I have two vba codes one for time stamping the other to lock cells after data input. How would I go about combining the two in the same worksheet?

    "Lock cells on data input"

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    On Error GoTo justenditall
    
    Application.EnableEvents = False
    If Not Intersect(Target, Range("A1:Z300")) Is Nothing Then
    If Target.Value <> "" Then
    ActiveSheet.Unprotect Password:="thepassword"
    Target.Locked = True
    End If
    End If
    
    ActiveSheet.Protect Password:="thepassword"
    
    justenditall:
    Application.EnableEvents = True
    End Sub
    "Time stamp"

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
            With Target
                If .Count > 1 Then Exit Sub
                If Not Intersect(Range("e2:e10500"), .Cells) Is Nothing Then
                    Application.EnableEvents = False
                    If IsEmpty(.Value) Then
                        .Offset(0, 3).ClearContents
                    Else
                        With .Offset(0, 3)
                            .NumberFormat = "hh:mm:ss"
                            .Value = Now
                        End With
                    End If
                    Application.EnableEvents = True
                End If
            End With
                 With Target
                If .Count > 1 Then Exit Sub
                If Not Intersect(Range("i2:i10500"), .Cells) Is Nothing Then
                    Application.EnableEvents = False
                    If IsEmpty(.Value) Then
                        .Offset(0, 2).ClearContents
                    Else
                        With .Offset(0, 2)
                            .NumberFormat = "hh:mm:ss"
                            .Value = Now
                        End With
                    End If
                    Application.EnableEvents = True
                End If
            End With
        End Sub

    I will greatly appreciate all the help given. Thanks
    Last edited by adriel19pr; 01-11-2013 at 09:11 AM. Reason: Code was not in brackets.

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Combining two vba codes in the same sheet

    Could you pls put your code between code brackets. See forum rule #1.

    As for your question:
    the quickest (and dirtiest) way is to change the first "end sub" into "exit sub" and you're done.

    Better is to decently structure your code like this:
    Start:
          'Put all the thing you alway need to do at the start of any procedure
    
    LockCells:
         If Not Intersect(Target, Range("A1:Z300")) Is Nothing Then
         and the rest of the code needed for this procedure
         ....
         Goto Finish
    
    TimeStamp:
         If Not intersect(Target, any relevant range here) is Nothing Then
         and the rest of the code needed for this procedure
         .....
         Goto Finish
    
    Finish:
         'Put all the thing you alway need to do at the end of any procedure
         ......
    
    end sub
    Of course there are more options like a select case construct.
    Last edited by Tsjallie; 01-10-2013 at 04:37 PM.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  3. #3
    Registered User
    Join Date
    05-04-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2013
    Posts
    12

    Post Re: Combining two vba codes in the same sheet

    Hello Tsjallie,

    Thanks for your advise about posting the code. I tried the quickest (and dirtiest) way first and it didn't work for me. What I'm trying to do is when I input data into a cell it will lock that cell and not let me write over it. Also will need a time stamp in certain cells. I have attached the worksheet any help will be greatly appreciated.

    Thanks,
    adriel19pr
    Attached Files Attached Files

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,627

    Re: Combining two vba codes in the same sheet

    Your first post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

  5. #5
    Registered User
    Join Date
    05-04-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Combining two vba codes in the same sheet

    Hello Zbor,

    Will re-post the code again. I attached the workbook where I need the codes in. Here are the codes I have.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    On Error GoTo justenditall
    
    Application.EnableEvents = False
    If Not Intersect(Target, Range("A1:Z300")) Is Nothing Then
    If Target.Value <> "" Then
    ActiveSheet.Unprotect Password:="thepassword"
    Target.Locked = True
    End If
    End If
    
    ActiveSheet.Protect Password:="thepassword"
    
    justenditall:
    Application.EnableEvents = True
    End Sub
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
    If .Count > 1 Then Exit Sub
    If Not Intersect(Range("e2:e10500"), .Cells) Is Nothing Then
    Application.EnableEvents = False
    If IsEmpty(.Value) Then
    .Offset(0, 3).ClearContents
    Else
    With .Offset(0, 3)
    .NumberFormat = "hh:mm:ss"
    .Value = Now
    End With
    End If
    Application.EnableEvents = True
    End If
    End With
    With Target
    If .Count > 1 Then Exit Sub
    If Not Intersect(Range("i2:i10500"), .Cells) Is Nothing Then
    Application.EnableEvents = False
    If IsEmpty(.Value) Then
    .Offset(0, 2).ClearContents
    Else
    With .Offset(0, 2)
    .NumberFormat = "hh:mm:ss"
    .Value = Now
    End With
    End If
    Application.EnableEvents = True
    End If
    End With
    End Sub
    Can you please help me with this. Did I post the code the correct way? I truly hope so because I read and learn a lot on this forum and would not like to break any of the rules.

    Thanks again.

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,627

    Re: Combining two vba codes in the same sheet

    Yea, but you could just go to first post, click edit, selecot code and click #

  7. #7
    Registered User
    Join Date
    05-04-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Combining two vba codes in the same sheet

    hello Zbor,

    Thanks again will do that now.

    Thanks,

  8. #8
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Combining two vba codes in the same sheet

    Hi Adriel,
    here's the workbook uploaded.
    I edited the worksheet_change procedure to fit your needs (I hope).
    And added some comments.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    05-04-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Combining two vba codes in the same sheet

    Hello Tsjallie,

    I've seen that it let's me do the first time stamp then it wont run the macro after that. I would like to freeze the whole table except columns A,B,C,E,F,I,J,L,N. I would need help running the time stamp also freezing the row after entering data. It freezes the cell but then the next time stamp won't run. Am I explaining myself correctly? Please help.

    Thanks
    adriel19pr,

  10. #10
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Combining two vba codes in the same sheet

    Hi Adriel,
    will look into it tomorrow.
    Really need some sleep now

  11. #11
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Combining two vba codes in the same sheet

    Hi Adriel,
    found the issue:
    the first time the macro generates a timestamp the workbook is unprotected, but the second time it is.
    When the macro wants to change the cell for the timestamp it runs into an error.
    However, because there is an instruction "on error goto justenditall" it just jumps to that section skipping the whole procedure without notification.

    Solution is to unprotect the workbook before the timestamp is entered and protect it again after that's done.

    ActiveSheet.Unprotect Password:="thepassword"
        .NumberFormat = "hh:mm:ss"
        .Value = Now
    ActiveSheet.Protect Password:="thepassword"

  12. #12
    Registered User
    Join Date
    05-04-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Combining two vba codes in the same sheet

    Hello Tsjallie,

    Thanks for all the help. Where about's do I paste your code?

    Thanks
    adriel19pr,

  13. #13
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Combining two vba codes in the same sheet

    Hi Adriel,
    here is the complete sub worksheet(change()
    Lines to be insterted ad marked in red.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    
    On Error GoTo JustEndItAll
    
    Start:
        Application.EnableEvents = False
        
    FormatCells:
        With Target
            If .Count > 1 Then GoTo JustEndItAll
            If Not Intersect(Range("e2:e10500"), .Cells) Is Nothing Then
    '            Application.EnableEvents = False <-obsolete. done in Start
                If IsEmpty(.Value) Then
                    .Offset(0, 3).ClearContents
                Else
                    With .Offset(0, 3)
                        ActiveSheet.Unprotect Password:="thepassword"
                        .NumberFormat = "hh:mm:ss"
                        .Value = Now
                        ActiveSheet.Protect Password:="thepassword"
                    End With
                End If
    '            Application.EnableEvents = True <-obsolete. done in justenditall
            End If
        End With
        With Target
            If .Count > 1 Then GoTo JustEndItAll
            If Not Intersect(Range("i2:i10500"), .Cells) Is Nothing Then
    '            Application.EnableEvents = False <-obsolete. done in start
                If IsEmpty(.Value) Then
                    .Offset(0, 2).ClearContents
                Else
                    With .Offset(0, 2)
                        ActiveSheet.Unprotect Password:="thepassword"
                        .NumberFormat = "hh:mm:ss"
                        .Value = Now
                        ActiveSheet.Protect Password:="thepassword"
                    End With
                End If
    '            Application.EnableEvents = True <-obsolete. done in justenditall
            End If
        End With
        'If LockCells is not to be executed uncomment the next line
        'Goto justenditall
        
    LockCells:
        'WARNING!
        'This section will not be executed if changed cell is in the range e301:e10500 or i301:i10500
        'If that's not intended to be so, then alter the range below to "A1:A10500"
        If Not Intersect(Target, Range("A1:Z300")) Is Nothing Then
            If Target.Value <> "" Then
                ActiveSheet.Unprotect Password:="thepassword"
                Target.Locked = True
            End If
            ActiveSheet.Protect Password:="thepassword"
        End If
        GoTo JustEndItAll 'Skip the rest
    
    JustEndItAll:
        Application.EnableEvents = True
    
    End Sub

  14. #14
    Registered User
    Join Date
    05-04-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Combining two vba codes in the same sheet

    Hello Tsjallie,

    Thank you very much worked perfect.

    Thanks again,
    adriel19pr,

  15. #15
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Combining two vba codes in the same sheet

    Hi Adriel,
    glad I could help.
    If this solved your problem pls mark your thread as solved (see thread tools).

+ 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