Help with VBA Excel project please!

[Icikle]

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.

[Tinbendr]

Welcome to the board!

WOW! You don't hold back, do you? :)

Can you upload a sample sheet with a few examples of Ref# and time stamps?

[Icikle]

Umm not sure what you mean, I havent come up with anything yet. I cant really do anything until I have some basic functionality to tell the interface to store textbox entries in the "next available" cell on spreadsheet. For example, a guy types in his national insurance number and presses submit, it gets stored in the next available cell in column A on the spreadsheet along with the time it was submitted (just the system time) in the adjacent cell in column B. Thanks.

[CodeNinja]

Icikle,
This sounds like a school project/homework assignment type thing, so I will only give you a couple of hints and starting points, and will not provide actual code.
You probably want to create a userform. In the VBE (press alt & F11 to get there), click insert and add a userform. You can then add controls to this form.


The project itself, involves recording reference numbers as an input from the user in a text box

So on your userform, add a text box control and a command button ... If you double click on the command button you will have an area to write your code for what happens when the person clicks on the command button. You can reference the textbox ie (userform1.textbox1.text) in the command button.


along with a timestamp in an adjacent cell

Use now() to get a time stamp


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.


Create 2 more command buttons one that looks for the previous (and one for the next) time stamp and displays this information somewhere (maybe a label caption) ...

Good luck with your project. This is not difficult stuff, but I think you will learn a lot doing it.

[Icikle]

Codeninja....no...im 25, very much employed, for HM Revenue and Customs...thanks.

Actually flabbergasted at the condescension in your post, thanks for that.

[CodeNinja]

Icikle,
Sorry you feel I am condescending towards you. It certainly was not the intention. Your request looks to me like something out of a school homework project. I tried to give you a way to get started since you seemed not to know where to start. I do not regret and will not apologize for being careful about subverting our educational system, however, I will apologize if you felt offended by my response. It was intended to help you get started.

[Icikle]

Your educational system?

Firstly I live in the UK, not your country. Secondly I am offended because I am asking for help and your attempting to justify not giving it by suggesting I am lying, who wouldnt be offended by that.

Also your response for me to get started WAS condescending, in the sense you think I dont know enough to even acess VBA itself within excel, or add buttons or the rest of what you offered. I have an advanced vocational certificate in IT, and VBA coding was one of the subjects, I graded A/A, two because it was worth 2 A Levels, but that was 7 years ago, and people forget when they dont use it every day.

If you can help me, then please do, otherwise...I dont know what else to say.

[Aussiebear]

Icikle, let me reassure you that CodeNinja was trying to assist you. No offence was intended in the comments posted. It is forum policy to not directly assist those seeking help to do assignments. So in light of that, lets just start again with the issue. If you could simply upload a sample workbook showing how you think this concept of yours might work, it will assist us to provide you with the required code. To upload a workbook, click on Go Advanced, scroll down to Manage Attachments and follow the prompts from there. Looking forward to seeing what could be a very good concept.

[Icikle]

Im not entirely sure what you want from me, and again with the calling me a liar saying this is an assignment. Do you want a copy of my driving licence to prove my age? What was a very simple request for help has turned into something ridiculously convoluted and insulting.

I will say again, there is no concept, there is no fancy design piece at possible work here. To elaborate, I work for HMRC, somtimes when we take reference numbers from our customers, we forget to write them down meaning follow up work cant be done unless we either call the customer back which relies on us having their number to begin with, or ask our manager to listen to our last call and give it to us. I wish to solve this issue by having a database that advisers fill in and it stores the national insurance number upon entered along with the time of the call. They can then retrieve this if required. I cannot EVEN START this project without a small line of code that looks at the first empty cell to store the national insurance number, then the next time it does it in the following empty cell.

Do you want me to send you my userform concept without any code written down? Because I would find that entirely pointless.

Honestly the lengths you have to go to these days just to get some help is beyond my comprehension. Does my language alone not tell you I aint some kid looking to score big on his homework by doing as little as possible?

If I could say to my customers, no you cant get a tax refund until you prove you were actually old enough to work and pay tax i dont think I would have a job for very long.

I do not care if he intended to be condescending, or you for that matter, the simple fact is, you both have been and continue to be so.

[Aussiebear]

This thread is now closed.