VBA Userform Question

[laurawr]

Hi,

I am fairly new to VBA and im sorry if it seems a simple question,

I have created a userform of which the user fills in and it can be submitted, the data is applied to the spreadsheet as it is submitted.

However I have a button of which when I press it i would like it to read the spreadsheet e.g. Column A named "Ref" and look at the next unique reference number that can be generated and place it into a text box named "hpin" on the userform for submitting.

My references should go as follows
e.g. HP/IN/0001
HP/IN/0002
and so on

I really hope that makes sense and if you can help please do so

Thank you so much

L.

 

[RoryA]

Do you actually need to store the "HP/IN/" part in the cells or is it purely for display?

 

[laurawr]

Yes the "HP/IN/" is a compulsery

L.

 

[RoryA]

That's not really what I was asking. Would it be OK to store 1,2,3 etc in column A but have them displayed as HP/IN/0001 etc? If not, then you could use code like:

me.hpin.text = "HP/IN/" & Format(sheets("Sheetname").evaluate("LOOKUP(9E305,--substitute(A2:A1000,""HP/IN/"",""""))")+1, "0000")

 

[rs2k]

Hi Laura, welcome to the board,

Another way is to have your numbers in Col A, in B1 the formula '=MAX(A:A)' which will return the highest number and then in C1 the formula '=B1+1' thus giving you the next available number.

When you click your form button, point the code to look at D1 to retrieve the next available number and when you transfer the data back to a worksheet add an additional line to copy the number used into Col A, which will then trigger B1 to change (to the MAX number) and C1 will update ready for the next new number.
As Rory was implying, the 'HP/IN/' bit can be written in the code when transfered to the ws, so you will have a record of it.
HTH
Colin

 

[laurawr]

Ahh Yes I See now where Rory was coming from :D

(sorry please note: i am fairly new at this)

I will give that a go and see what happens

Thanks for your quick replies

L.