Recording macro usage

[LiAD]

Hi,

is there the functionality in excel to record when a macro form button is
pushed?

I have, or will have a sheet with about 20 macros on it, all of which
perform simple + and - operations. I would like excel to record which macro
(by the macro name), the date and time at which any button was pushed, just
in one big long list one after another.

Is this possible?

Thanks

 

[Jacob Skaria]

You can write a separate procedure say WriteLog() to record the activity to
an external file or a hidden worksheet and call that from each click event of
the command button.

Private Sub CommandButton1_Click()
Call WriteLog()

End Sub


If this post helps click Yes

 

[LiAD]

Nice thats its possible. I tried to paste in the code but can't find how to
get it to run.

I have two macros in sheet one and two in sheet 2, all four I would like to
record in the same output table for macro usage.

Thanks again for your help

 

[Jacob Skaria]

Paste the below function which writes the log file and call writelog as shown
below from all macros you want to record

Call WriteLog("Procedure 1")


Function WriteLog(ByVal strMsg As String)
intFile = Freefile
Open "c:\MacroLog.txt" For Append As #intFile
Print #intFile, strMsg & "," & Now
Close #intFile
End Function

 

[LiAD]

Sorry but I still can't get it.

Like I'm said I'm completely useless in VBA.

I pasted the function below into a module then tried to change the macro
name from Cancel 15 for example to Writelog("Cancel 15). Is this not
correct?

I have attached the macro I'm using below, its just a +1 for this one.

Thanks again.
Sub WriteLog("Cancel 15")
With Sheet1 ' Changer to suit
If Range("i38").Value <> "" Then ' Change I38 to suit
Range("I38").Value = Range("I38").Value + 1
End If
End With
End Sub

 

[Jacob Skaria]

Function should be as such.

In your exiting procedure

Sub Procedure()
WriteLog "Procedure"
'Your code begins here


End Sub

Add the line Writelog "..." to all existing procedures/./

Function WriteLog(ByVal strMsg As String)
intFile = Freefile
Open "c:\MacroLog.txt" For Append As #intFile
Print #intFile, strMsg & "," & Now
Close #intFile
End Function

 

[LiAD]

Sorry for asking again but I can't get exactly how this is supposed to work.

I have pasted in the code. The +1 -1 bit of the macro is working fine but I
can't find any sign of the output registering when the macro was used. Do I
need to set something else up before I get his output? I assume output is
supposed to be a text file on my C drive?

Sorry for the dumb questions but I'm way out of my depth when it comes to
macros and programming.

Thanks a lot for your help.
The exact code I am using is-

Sub Procedure()
WriteLog "Procedure"

Sub Cancel15()
With Sheet1 ' Changer to suit
If Range("i38").Value <> "" Then ' Change I38 to suit
Range("I38").Value = Range("I38").Value + 1
End If
End With
End Sub

Function WriteLog(ByVal strMsg As String)
intFile = FreeFile
Open "c:\MacroLog.txt" For Append As #intFile
Print #intFile, strMsg & "," & Now
Close #intFile
End Function

 

[Jacob Skaria]

Something like this

Sub Cancel15()
WriteLog "Cancel 15 : " & Now()

With Sheet1 ' Changer to suit
If Range("i38").Value <> "" Then ' Change I38 to suit
Range("I38").Value = Range("I38").Value + 1
End If
End With
End Sub

Like this you need to copy the Write log line to all procedures...

 

[LiAD]

Sorted.

Thanks a lot

Something like this

Sub Cancel15()
WriteLog "Cancel 15 : " & Now()

With Sheet1 ' Changer to suit
If Range("i38").Value <> "" Then ' Change I38 to suit
Range("I38").Value = Range("I38").Value + 1
End If
End With
End Sub

Like this you need to copy the Write log line to all procedures...