+ Reply to Thread
Results 1 to 6 of 6

Merging 2 Private Sub Worksheet_Change codes

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Merging 2 Private Sub Worksheet_Change codes

    Hey guys I need a simple help.
    Stuck with this.
    I have tried combining but it is not working

    Here are the 2 codes which I need to mearge :
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 3 Then
            Application.EnableEvents = False
            Cells(Target.Row, 17).Value = Date + Time
            Application.EnableEvents = True
        End If
        
    Dim ws      As Worksheet
    Dim shp     As Shape
    Dim addr    As Variant
    Dim i As Long
    Dim lastRow As Long
    Dim tRng    As Range
    Dim shpName As String
    Dim shpPath As String
    
    Set ws = ActiveSheet
    shpPath = filepath
    If Right(shpPath, 1) <> Application.PathSeparator Then shpPath = shpPath & Application.PathSeparator
    If Dir(shpPath) = "" Then MsgBox shpPath & " is invalid!", vbCritical, "INVALID PATH": Exit Sub
    
    lastRow = WorksheetFunction.Max(2, ws.Cells(Rows.Count, "A").End(xlUp).Row + 2)
    If Not Intersect(Target, Range("A2:A" & lastRow)) Is Nothing Then
        Set tRng = Cells(Target.Row, "T")
        If Target.Value = "" Then
            On Error Resume Next
            Me.Shapes("PictureAt" & tRng.Address).Delete
            Err.Clear
            On Error GoTo 0
        Else
            shpName = Target.Value
            If Len(Trim(shpName)) > 0 Then
                With Application
                    .ScreenUpdating = False
                    .EnableEvents = False
                End With
                With tRng
                    .RowHeight = 56
                    .ClearContents
                    On Error Resume Next
                    Me.Shapes("PictureAt" & .Address).Delete
                    On Error GoTo 0
                End With
                If Dir(shpPath & Target & ".jpg") <> "" Then   '*  verify that the file exists
                    With tRng
                        Set shp = Me.Shapes.AddPicture(shpPath & shpName & ".jpg", msoFalse, msoCTrue, .Left, .Top, .Width, .Height)
                        shp.Name = "PictureAt" & .Address
                    End With
                Else
                    tRng.Value = "NO IMAGE" & Chr(10) & "FOUND"
                End If
            End If
        End If
    End If
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    End Sub
    and
    Private Sub Worksheet_Change(ByVal Target As Range)
        If (Intersect(Target, Range("A1:G2")) Is Nothing) Then Exit Sub
        If Range("A7:Z500000").CurrentRegion.Rows.Count > 1 Then
            Range("A7:Z500000").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:G2")
        End If
    End Sub
    Last edited by anilpatni1234; 05-01-2019 at 10:06 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    81,666

    Re: Combining 2 codes together

    I have tried combining but it is not working
    Some clues about what "not working" means might be helpful ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Combining 2 codes together

    Hwy Ali sure,

    Here is what I did
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 3 Then
            Application.EnableEvents = False
            Cells(Target.Row, 17).Value = Date + Time
            Application.EnableEvents = True
        End If
        
    Dim ws      As Worksheet
    Dim shp     As Shape
    Dim addr    As Variant
    Dim i As Long
    Dim lastRow As Long
    Dim tRng    As Range
    Dim shpName As String
    Dim shpPath As String
    
    Set ws = ActiveSheet
    shpPath = filepath
    If Right(shpPath, 1) <> Application.PathSeparator Then shpPath = shpPath & Application.PathSeparator
    If Dir(shpPath) = "" Then MsgBox shpPath & " is invalid!", vbCritical, "INVALID PATH": Exit Sub
    
    lastRow = WorksheetFunction.Max(2, ws.Cells(Rows.Count, "A").End(xlUp).Row + 2)
    If Not Intersect(Target, Range("A2:A" & lastRow)) Is Nothing Then
        Set tRng = Cells(Target.Row, "T")
        If Target.Value = "" Then
            On Error Resume Next
            Me.Shapes("PictureAt" & tRng.Address).Delete
            Err.Clear
            On Error GoTo 0
        Else
            shpName = Target.Value
            If Len(Trim(shpName)) > 0 Then
                With Application
                    .ScreenUpdating = False
                    .EnableEvents = False
                End With
                With tRng
                    .RowHeight = 56
                    .ClearContents
                    On Error Resume Next
                    Me.Shapes("PictureAt" & .Address).Delete
                    On Error GoTo 0
                End With
                If Dir(shpPath & Target & ".jpg") <> "" Then   '*  verify that the file exists
                    With tRng
                        Set shp = Me.Shapes.AddPicture(shpPath & shpName & ".jpg", msoFalse, msoCTrue, .Left, .Top, .Width, .Height)
                        shp.Name = "PictureAt" & .Address
                    End With
                Else
                    tRng.Value = "NO IMAGE" & Chr(10) & "FOUND"
                End If
            End If
        End If
    End If
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    
    End If
    
        If (Intersect(Target, Range("A1:G2")) Is Nothing) Then Exit Sub
        If Range("A7:Z500000").CurrentRegion.Rows.Count > 1 Then
            Range("A7:Z500000").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:G2")
        End If
    End Sub
    It has not worked for me.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2406 (Windows 11 23H2 64-bit)
    Posts
    81,666

    Re: Combining 2 codes together

    It has not worked for me.
    In what way has it not worked for you?

    • Does the code run?
    • Does it run but do nothing?
    • Does it produce error messages? If so, what do the messages say?
    • Does it produce unexpected/wrong results? If so, how do the results differ from what you expect?
    • Does it hang?

  5. #5
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Combining 2 codes together

    Hey Ali,

    After i pasted the code on my excel file, the code did not work in the sense that it did not function at all.
    It dint show me any error at all and at the same time it wasnt functioning as well

  6. #6
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Merging 2 Private Sub Worksheet_Change codes

    hey guys,

    was anyone able to glance through ?

+ 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. Combining two codes into one
    By Aqmas in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-08-2017, 03:46 PM
  2. Call Two Codes from ComboBox
    By zplugger in forum Excel General
    Replies: 2
    Last Post: 01-24-2015, 03:02 PM
  3. Combining 2 VBA codes
    By watiwawa in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-09-2014, 02:20 AM
  4. Combining vba codes makes the previous codes broken !
    By MDPLUS in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-26-2013, 10:00 AM
  5. [SOLVED] Combining 2 codes?
    By bjcowen9000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-19-2013, 02:48 PM
  6. combining codes
    By tweety127 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-18-2008, 06:48 PM
  7. [SOLVED] combining 2 codes
    By steve in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-18-2006, 01:15 PM

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