VBA code for logging when a button is pressed


New Member
Sep 28, 2021
Office Version
  1. 365
  2. 2019
  1. Windows
Hi There,
I have several macro buttons, the majority being on one sheet within Excel called 'Control'.
What I want to achieve is when one of the buttons is clicked it registers the following on another sheet within the same workbook:
Date and Time
The Button Name
Who pressed the button
The actual reason (Name of the button "Email sent for TradeWeb")


The date and time and who pressed the button can go into the same cell as 'TradeWeb' along with the button name.
Also, I have another button 'Aladdin' which will relate to the same 'Fund', so I would like to press that and the information checks the row and if it relates to 'Fund ABC' the date/time and who pressed it, along with the button name goes into 'Aladdin'. Then if another Fund is added called DEF on the next line below, the code becomes dynamic.

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
post this code into a new module.
sheet called 'LOG' is used here to collect the click data.
at each button click, run: Post2Log "btnName","my reason"
Private Const kLOG = "Log"

Public Sub Post2Log(pvBtn, pvReason)
Dim vUser
vUser = Environ("Username")

ActiveCell.Offset(0, 0).Value = vUser
ActiveCell.Offset(0, 1).Value = pvBtn
ActiveCell.Offset(0, 2).Value = pvReason
ActiveCell.Offset(0, 3).Value = Now()
End Sub

Private Sub gotoNewRow()
Select Case True
    Case ActiveCell.Offset(1, 0).Value = ""
       ActiveCell.Offset(1, 0).Select 'next row
    Case Else
       ActiveCell.Offset(1, 0).Select 'next row
End Select
End Sub
Upvote 0
Thank you ranman256 (great photo - I'm a big Arnie fan) - It's sort of working, but not quite. I have this on the "Log" sheet:

EnteredEntered BybtnNameMyReasonFundLEITradeWebAladdinMWIRE BICDSMatchOTC BarcOTC Barc DelOTC JPMOTC JPM DelLCH TagICELink
12/21/2021 08:40:31Stephen PreeceButton 2ABC123456789

So, within the same workbook, I have the "Control", "Sheet2", "Logs" & "TradeWeb" sheets. In the "TradeWeb" sheet there is the fund name ABC is cell B1. In the "Control" sheet, I have a button that is named "Create TradeWeb Spreadsheet".

When clicked this takes the TradeWeb sheet and adds this to a new workbook and takes the fund name "ABC" and saves this within the title of the spreadsheet. At the same time, it takes the path and filename and adds this into "Sheet2" into a named range. Then in "Sheet2", there's a button called "Create TradeWeb Email". I click that and it creates an email with the spreadsheet as an attachment.

What I would like to be shown on the "Log" sheet is that when the "Create TradeWeb Spreadsheet" button is clicked, there is some logic to work out the fund name "ABC" is available and then add a comment to the column "TradeWeb" if "ABC" exists to say spreadsheet created by the person who clicked the button.

Then when this is done the email will need to be sent, so when the "Create TradeWeb Email" button is clicked in "Sheet2", this over-writes what was put in previously in the "TradeWeb" column in the "Logs" sheet (after the same check has been done e.g. if fund "ABC" is present) with email sent including who clicked the button and time etc...

I then want to extend this out to similar buttons created for the other headings in the "Logs" sheet.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back