VBA code for logging when a button is pressed

[StevieMP]

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")

Aladdin	Fund	TradeWeb
	ABC

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.

 

[ranman256]

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")
Sheets(kLOG).Activate
gotoNewRow

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()
Range("A1").Select
Select Case True
    Case ActiveCell.Offset(1, 0).Value = ""
       ActiveCell.Offset(1, 0).Select 'next row
   
    Case Else
       Selection.End(xlDown).Select
       ActiveCell.Offset(1, 0).Select 'next row
End Select
End Sub

 

[StevieMP]

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:

Entered

Entered By

btnName

MyReason

Fund

LEI

TradeWeb

Aladdin

MWIRE BIC

DSMatch

OTC Barc

OTC Barc Del

OTC JPM

OTC JPM Del

LCH Tag

ICELink

12/21/2021 08:40:31

Stephen Preece

Button 2

ABC

123456789

DEF

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.