+ Reply to Thread
Results 1 to 2 of 2

combine two Private Sub Worksheet_Change

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,054

    combine two Private Sub Worksheet_Change

    i would like to combine between the following two code
    HTML Code: 
    and
    HTML Code: 

  2. #2
    Valued Forum Contributor Neil_'s Avatar
    Join Date
    04-19-2013
    Location
    Yorkshire
    MS-Off Ver
    Office 365 Enterprise E3 2013 / 2016
    Posts
    479

    Re: combine two Private Sub Worksheet_Change

    Private Sub Worksheet_Change(ByVal Target As Range)
      If Target.Row = 1 Then Exit Sub
    
      On Error GoTo ErrHandler
      Application.EnableEvents = False
      If Target.Column = 1 Then
         Call Add_name(Target)
      End If
    ErrHandler:
      Application.EnableEvents = True
      Err.Clear
    
        
        On Error GoTo ReEnable
        
        If Target.Cells.Count = 1 Then
        
            If Not Intersect(Target, Range("I:AF")) Is Nothing Then 'Test if Target is in columns I:AF
                If Target.Row Mod 5 = 2 Then                        'Test if Target is in rows 2,7,12,17...
                
                    Application.EnableEvents = False
                    'Allow entries based on column E value
                    If Not Intersect(Target, Range("I:AF").Resize(, Range("E" & Target.Row).Value)) Is Nothing Then
                        'Date stamp
                        If Target.Value > 0 Then Target.Offset(4, 0).Value = Date
                    Else
                        'Clear entry
                        MsgBox "Not Allowed", vbExclamation, "Invalid Entry"
                        Target.Value = ""
                    End If
                    Application.EnableEvents = True
                    
                End If
            End If
        End If
        
    ReEnable:
        Application.EnableEvents = True
        If Err.Number <> 0 Then MsgBox Err.Description, vbCritical, "Error " & Err.Number
        
    End Sub
    Frob first, tweak later

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Combine Private Sub Worksheet_Change(ByVal Target As Range) VBA Code on same worksheet
    By mark_luke in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2022, 04:48 PM
  2. Combine two Private Sub Worksheet_Change(ByVal Target As Range) on same sheet
    By carl2942 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-25-2016, 01:48 AM
  3. [SOLVED] Need help to combine 2 Private Sub Worksheet_Change(ByVal Target As Range)
    By sylvainsyl20 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-11-2016, 11:45 AM
  4. [SOLVED] How to Combine two Private Sub Worksheet_Change ?
    By stoicy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-17-2016, 04:34 PM
  5. Combine Private Sub Worksheet_Change VBA Code on same worksheet
    By stanlelma in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-06-2013, 12:42 PM
  6. Combining two Private Sub Worksheet_Change
    By bdf0827 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-14-2011, 08:58 AM
  7. Private Sub Worksheet_Change
    By VicM in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-15-2008, 01:28 AM

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