VB Editor question - help with project

Hello,

I am using the editor within Excel, not VB itself to create this so Im unsure if this is the correct place to post but be gentle as it is my first post on these forums. :)

Anyway here goes, I have come up with a new project for work, fairly simple but something I am not entirely familiar with. The extent of my knowledge in VB is upto IF and WHEN statements, some DIM and searching functionality but thats about it.

The project itself, involves recording reference numbers as an input from the user in a text box, when the user hits submit it then records that number in a cell on a spreadsheet along with a timestamp in an adjacent cell. The user then continues this function and it keeps recording the numbers and timestamps in cells below the last, keeping a history etc.

Another function I want to include is when the user hits a back arrow button, it will show the last reference number they submitted along with the timestamp, repeated clicking of the backbutton will continue to show the prior reference and timestamp and so on. Similarly a forward button to go back to the most recent and then stopping with a messagebox when theyre viewing the most recent.

Although not essential I would also like to include a search function where they can input a time and it will show as a result, the closest timestamp/reference number to the time they input.

I know it sounds like im asking for help in the entire project and not just a particular piece of coding, but to start off with I would simply just be grateful if someone could tell me the snippet of code that allows me to record the reference they input into a cell, and then the next one goes into the cell below and so on and so on.

Thanks.

Welcome to VBForums :wave:

Thread moved to the 'Office Development/VBA' forum... note that while it certainly isn't made clear, the "VB Editor" in Office programs is actually VBA rather than VB, so the 'VB6' forum is not really apt

assuming you are using a userform to input the data

at the very top of the userform code page

vb Code:

dim lastrecord as long

in the userform initialize event

vb Code:

lastrecord = cells(rows.count, 1).end(xlup).row    ' find last record on sheet from previous

in the save button click event (or what ever you want to use to save record)

vb Code:

cells(lastrecord + 1, 1).value = textbox1   ' reference to column 1
cells(lastrecord + 1, 2).value = now     ' timestamp
lastrecord = lastrecord  + 1          ' increment counter

to use the arrow keys, you can put code in the keydown (or keyup) event of the textbox that recognises the arrow keys

you can use excels find method to find records by timestamp

the above code samples do not specify which sheet to work with and always assumes the activesheet, it is better practice to always fully qualify the range (specify which sheet)