none
Record Macro Usage RRS feed

  • Question

  • Hi All......

    Very rusty on my VBA here....need help please.

    I have need for a subroutine macro to record in two columns on a Hidden Sheet, each time a sprcific REPORT macro has been run.  These are  large macros that generate specific Reports, and I need to know how often they are used. 

    The columns I want to populate are on a hidden sheet and start at REVISIONS!G12, with the NAME of the macros run, and REVISIONS!H12 with the DATE it was run.....and then put the next result in row 13, the next in 14, etc etc. without having to "GOTO" the REVISIONS sheet each time

    I will CALL the Subroutine in each REPORT Macro, and for itself, it must figure out the NAME of the macro it is being run in.

    Hope my explanation isn't too confuzing....

    I know how to place text in a cell on another sheet without going to the sheet, but I don't know how to FIND the last cell in a column and place the text in the next cell below that one without GOING to the sheet and I would prefer not to have to do that.....and I don't know how to FIND the name of the host macro from a subroutine.

    Thanks for any help

    Chuck, CABGx3


    Chuck, CABGx3

    Sunday, July 10, 2016 4:22 PM

Answers

  • A subroutine doesn't "know" if it has been called from another subroutine, so you'll have to pass the name.

    In the calling subroutines:

    Sub ReportMacro1()
        ...
        ...
        Call ListUse("ReportMacro1")
    End Sub
    
    Sub ReportMacro2()
        ...
        ...
        Call ListUse("ReportMacro2")
    End Sub

    etc. All these call the subroutine ListUse:

    Sub ListUse(MacroName As String)
        Dim r As Long
        With Worksheets("REVISIONS")
            r = .Range("G" & .Rows.Count).End(xlUp).Row + 1
            If r < 12 Then r = 12
            .Range("G" & r).Value = MacroName
            .Range("H" & r).Value = Date ' or Now if you also want the time
        End With
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by CABGx3 Sunday, July 10, 2016 7:50 PM
    Sunday, July 10, 2016 5:58 PM

All replies

  • A subroutine doesn't "know" if it has been called from another subroutine, so you'll have to pass the name.

    In the calling subroutines:

    Sub ReportMacro1()
        ...
        ...
        Call ListUse("ReportMacro1")
    End Sub
    
    Sub ReportMacro2()
        ...
        ...
        Call ListUse("ReportMacro2")
    End Sub

    etc. All these call the subroutine ListUse:

    Sub ListUse(MacroName As String)
        Dim r As Long
        With Worksheets("REVISIONS")
            r = .Range("G" & .Rows.Count).End(xlUp).Row + 1
            If r < 12 Then r = 12
            .Range("G" & r).Value = MacroName
            .Range("H" & r).Value = Date ' or Now if you also want the time
        End With
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by CABGx3 Sunday, July 10, 2016 7:50 PM
    Sunday, July 10, 2016 5:58 PM
  • Many thanks Hans......you are a Prince!
     
    Your code works exactly as I would like it to, and the best part is that you wrote and explained it in a manner that even I can understand it.
     
    Thanks again,
    Chuck, CABGx3

    Chuck, CABGx3

    Sunday, July 10, 2016 7:49 PM