Merge aspects of 2 pieces of VBA Code

[kizzie37]

The following code works well to log any changes made in any tab of an excel workbook into a tab marked "Tracker":

VBA Code:
Option Explicit


Dim vOldVal 'Must be at top of module


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


Dim bBold As Boolean




If Target.Cells.Count > 1 Then Exit Sub
If ActiveSheet.Name = "Pricing" Then Exit Sub


'On Error Resume Next


    With Application
         .ScreenUpdating = False
         .EnableEvents = False


    End With


    If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
    bBold = Target.HasFormula
        With Sheets("Tracker")
            '.Unprotect Password:="Secret"
                If .Range("A1") = vbNullString Then
                    .Range("A1:H1") = Array("Cell Changed", "Old Value", _
                        "New Value", "Old Formula", "New Formula", "Time of Change", "Date of Change", "User")
                End If


            With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
                  .Value = ActiveSheet.Name & " : " & Target.Address
                  .Offset(0, 1) = vOldVal
            With .Offset(0, 2)
              If bBold = True Then
                .ClearComments
                .AddComment.Text Text:= _
                     "OzGrid.com:" & Chr(10) & "" & Chr(10) & _
                        "Bold value is the result of formula change"


              End If
                .Value = Target
                .Font.Bold = bBold
                
            End With
                .Offset(0, 5) = Time
                .Offset(0, 6) = Date
                .Offset(0, 7) = Application.UserName
            End With
            .Cells.Columns.AutoFit
            '.Protect Password:="Secret"
        End With


    vOldVal = vbNullString


    With Application
         .ScreenUpdating = True
         .EnableEvents = True
    End With
On Error GoTo 0
End Sub




Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    vOldVal = Target
End Sub


Private Sub test()
    Application.EnableEvents = True
End Sub

The only action missing from this code is instructions to capture any whole lines that are deleted from any of the tabs in the workbook. I have found some code online that captures any added rows or deleted rows but I need someone to write the required aspects into the above code so that I can also capture this action

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim What As String

If Target.Columns.Count = Columns.Count And _
WorksheetFunction.CountA(Selection) > 0 Then
    What = "Row " & Target.Row & " Deleted along with " & Target.Rows.Count - 1 & " additonal rows"
    Call DocumentChange(What)
End If

If Target.Rows.Count = Rows.Count And _
WorksheetFunction.CountA(Selection) > 0 Then
    What = "Column " & Target.Column & " Deleted along with " & Target.Columns.Count - 1 & " additonal columns"
    Call DocumentChange(What)
End If

End Sub

I can tweak code at a push but this one is way beyond my capabilities. Please can anyone assist?

 

[AntMac]

Could you make the 2nd one a separate macro on a module.
Put a call sub in your first one.

Maybe worst case you have to add passing variables between them. But it's very confusing to decipher the codes to find the ultimate outcomes of both and why you wouldn't be able to run both one after the other.

 

[kizzie37]

Could you make the 2nd one a separate macro on a module.
Put a call sub in your first one.

Maybe worst case you have to add passing variables between them. But it's very confusing to decipher the codes to find the ultimate outcomes of both and why you wouldn't be able to run both one after the other.

I tried that but I couldn't make it work, I'm sure someone else can, but I am not that good with code.

 

[AntMac]

So, let's start in simple user friendly version. What does each macro do for you?

 

[kizzie37]

So, let's start in simple user friendly version. What does each macro do for you?

Macro 1 records changes to any cells in any of the workbook sheet. specifically : the Old value, the new value, who made the change and the date and time and sets in bold any changes that were made to a formula.

Macro 2 records any row or columns numbers that have been deleted or added.

I would like Macro 2 to not only record the row number, but I would like it to record the value that was in A1 along with who deleted, the date and time in the same way that Macro 1 does

 

[AntMac]

Macro 1 records changes to any cells in any of the workbook sheet. specifically : the Old value, the new value, who made the change and the date and time and sets in bold any changes that were made to a formula.

Macro 2 records any row or columns numbers that have been deleted or added.

I would like Macro 2 to not only record the row number, but I would like it to record the value that was in A1 along with who deleted, the date and time in the same way that Macro 1 does

Ok that helps. Looks like the easier path would be to see what the change was then use the appropriate macro. Do both work individually?

 

[kizzie37]

Ok that helps. Looks like the easier path would be to see what the change was then use the appropriate macro. Do both work individually?

HI, Yes they do, I need Macro 1, I just want to include the functionality of Macro 2 within Macro 1 and have no idea how to insert the code, or where.