Consulting

Results 1 to 3 of 3

Thread: Make a declared variable equal to values from multiple cells

  1. #1
    VBAX Newbie
    Joined
    Sep 2022
    Posts
    2
    Location

    Question Make a declared variable equal to values from multiple cells

    I have an Excel macro which is designed to create a new Excel tab ("Tracker") where changes made to any worksheet within the workbook are recorded, detailing "Cell Changed", "Old Value", "New Value", "Old Formula", "New Formula", "Time of Change", "Date of Change", and "User".

    The macro currently will not detail the "Old Value" of the change where the target of the change are multiple cells, and instead leaves that blank in the Tracker tab.


    From what I can deduce, I need the declared variable "vOldValue" to equal a string created from the values from the selection, however I don't know how to achieve this.


    Please see macro code below:

    Option Explicit
    Dim sOldAddress As String
    Dim vOldValue As Variant
    Dim sOldFormula As String
     
    Private Sub Workbook_TrackChange(Cancel As Boolean)
        Dim sh As Worksheet
        For Each sh In ActiveWorkbook.Worksheets
            sh.PageSetup.LeftFooter = "&06" & ActiveWorkbook.FullName & vbLf & "&A"
        Next sh
    End Sub
     
    Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    Dim wSheet As Worksheet
        Dim wActSheet As Worksheet
        Dim iCol As Integer
        Set wActSheet = ActiveSheet
    'Precursor Exits
         'Other conditions that you do not want to tracke could be added here
        'If vOldValue = "" Then Exit Sub 'If you comment out this line *every* entry will be recorded
    'Continue
    On Error Resume Next ' This Error-Resume-Next is only to allow the creation of the tracker sheet.
        Set wSheet = Sheets("Tracker")
         'Add the tracker Sheet if it does not exist
    If wSheet Is Nothing Then
            Set wActSheet = ActiveSheet
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Tracker"
        End If
        On Error GoTo 0
         'End of specific error resume next
    On Error GoTo ErrorHandler
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    With Sheets("Tracker")
    'This bit of code moves the tracker over a column when the first columns are full
            If .Cells(1, 1) = "" Then '
                iCol = 1 '
            Else '
                iCol = .Cells(1, 256).End(xlToLeft).Column - 7 
                If Not .Cells(65536, iCol) = "" Then '
                    iCol = .Cells(1, 256).End(xlToLeft).Column + 1 
                End If 
            End If 
    '.Unprotect Password:="Secret"
             
             'Sets the Column Headers
    If LenB(.Cells(1, iCol).Value) = 0 Then
                .Range(.Cells(1, iCol), .Cells(1, iCol + 7)) = Array("Cell Changed", "Old Value", _
                "New Value", "Old Formula", "New Formula", "Time of Change", "Date of Change", "User")
                .Cells.Columns.AutoFit
            End If
    With .Cells(.Rows.Count, iCol).End(xlUp).Offset(1)
    .Value = sOldAddress
    .Offset(0, 1).Value = vOldValue
                .Offset(0, 3).Value = sOldFormula
    If Target.Count = 1 Then
                    .Offset(0, 2).Value = Target.Value
                    If Target.HasFormula Then .Offset(0, 4).Value = "'" & Target.Formula
                End If
    .Offset(0, 5) = Time
                .Offset(0, 6) = Date
                .Offset(0, 7) = Application.UserName
                '.Offset(0, 7).Borders(xlEdgeRight).LineStyle = xlContinuous 'Adds a line at the end of the row
            End With
    '.Protect Password:="Secret"  'Uncomment to protect the "tracker tab"
    End With
    ErrorExit:
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    wActSheet.Activate
        Exit Sub
    ErrorHandler:
         'any error handling you want
         'Debug.Print "We have an error"
        Resume ErrorExit
    End Sub
     
    Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)
    With Target
            sOldAddress = .Address(external:=True)
    If .Count > 1 Then
    vOldValue = "Multiple Cells Selected" 'Change this to get value of each cell selected before the change
                sOldFormula = vbNullString
    Else
    vOldValue = .Value
                If .HasFormula Then
                    sOldFormula = "'" & Target.Formula
                Else
                    sOldFormula = vbNullString
                End If
            End If
        End With
    End Sub

    Any assistance would be greatly appreciated.


    Thank you.
    Last edited by Aussiebear; 09-26-2022 at 07:14 PM. Reason: Added code tags and cleaned up presentation

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,619
    How often do you think you will use these data ?
    Which actions do you plan as a result of these data ?
    Are you still working in Excel 2003 or earlier ?
    Have you ever progammed anything in VBA ?

  3. #3
    VBAX Newbie
    Joined
    Sep 2022
    Posts
    2
    Location
    Quote Originally Posted by snb View Post
    How often do you think you will use these data ?
    Which actions do you plan as a result of these data ?
    Are you still working in Excel 2003 or earlier ?
    Have you ever progammed anything in VBA ?
    This data will need to be accessed infrequently for auditing purposes.
    The idea behind the macro is to ensure the spreadsheets I apply it do adhere to the ALOCA+ principles.
    I am still using Excel 2003.
    I have not programmed anything in VBA before.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •