PDA

View Full Version : [SOLVED] increment a cell reference



rtlane
07-12-2017, 11:33 AM
Im new to VBA and just starting using it this morning.
I have this vba code in a workbook, lets call it "info", that saves the username and timestamp when a person opens it to a workbook called "counter". So with this code, it saves the username to A2 and the timestamp to B2 in the "counter" workbook. I got part of it working fine. I want the cells it saves the information to, to increment by one everytime someone opens the workbook. So when its opened again, it saves the username in A3 and timestamp in B3....then to A4 & B4.....and so on. Here is the current code:

Private Sub Workbook_Open()
['C:\Users\RTL\Documents\[Counter.xlsx]Sheet1'!A2] = Application.UserName
['C:\Users\RTL\Documents\[Counter.xlsx]Sheet1'!B2] = DateTime.Now
End Sub

I've tried creating variables and strings but could not get it to work.

Can you help me with this?

Also, currently I have to keep the counters workbook open for it to work. Is there a way to do it where I can leave the counter workbook closed?

Logit
07-12-2017, 12:00 PM
.

Paste in ThisWorkbook module :



Private Sub Workbook_Open()
'Paste this macro into the ThisWorkbook module
Dim LR As Long
With Sheets("Log")
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LR + 1).Value = Environ("username")
Range("B" & LR + 1).Value = Format(Now, "m/d/yy hh:mm:ss")
End With
End Sub




I prefer to format column B as :

CUSTOM

"m/d/yyyy h:mm:ss"

rtlane
07-12-2017, 01:30 PM
Thank you, this worked!

Logit
07-12-2017, 02:08 PM
.
You are welcome. Glad to help.