VB Form.. Submit.. Give ref number

[leighjones123]

Hi All

I was wondering if this is possible...

When my user submits a form in Excel, a unique ID number is displaued as a confirmation that they have submitted it, and to track it in future.

1. User fills in form
2. Clicks Submit
3. Excel returns a msg box to say your id number is xxx

Is there a way? I've scoured the internet, but can't seem to find anything.

Thanks in advance,

Leigh

 

[Trevor G]

If you retain the data that is on the userform it can give you the next number that is assigned and then show a message to that effect.

Are you logging the data from the userform currently? What sort of numbering system do you want to use?

 

[leighjones123]

It's a new form i'm designing, and to be honest i'm not great with VB/Excel - i do try though and can get my head around most things.

So currently, it's not logging any number. The way i've done it in the past when i've used userforms is just had a coloum next to the data which copies over, with ID Numbers pre-entered, and just done it that way.

I would want to use a 5 digit number, and the next number that is assigned would be good.

Thanks for your quick reply,

 

[Trevor G]

Take a look at this it is very rough but is working.

I have created a log sheet and added a dummy row of data with the first number it then captures data being added in a userform.

As it logs it will add the next row and also increment the number by 1 and also log the data, include date and time stamp

A message box is being displayed as well to confirm the number being added.

Excel Workbook
	A	B	C	D
1	ID Number	Data	Other	DateTime
2	10001	Something	Else	27/09/2011 18:33
3	10002	data	desk	27/09/2011 18:33
4
Data Log

I have then created a userform and on initialize have added this

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()<br>Range("A500").Select<br>Me.txtID = Selection.End(xlUp).Value<br><br>End <SPAN style="color:#00007F">Sub</SPAN></FONT>

With several textbox added and named then a command button with the code behind the button showign this

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> cmdSubmit_Click()<br>Sheets("Data log").Activate<br>Range("A2").Activate<br><br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> ActiveCell.Value = ""<br>ActiveCell.Offset(1, 0).Select<br><SPAN style="color:#00007F">Loop</SPAN><br>ActiveCell.Value = Me.txtID + 1<br>ActiveCell.Offset(0, 1).Value = Me.txtData.Value<br>ActiveCell.Offset(0, 2).Value = Me.txtOther.Value<br>ActiveCell.Offset(0, 3).Value = Now()<br>Me.txtID = ActiveCell.Value<br><br>MsgBox "The next number is " & ActiveCell.Value<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Next time I run the form and complete it all is saved and numbered accordingly.

 

[leighjones123]

Trevor,

Thanks so much - got that working perfectly.

Many thanks for your time and efforts,

Leigh

 

[Trevor G]

Pleased to read you have a working solution Leigh,

Thanks for the feed back.