UserForm

[pan_kaj]

I have a userform that I use to enter data for Calls to fix faults.

Once the form is filled and submit is pressed, I want a Call reference number to be generated. This call reference number is a 6 digit number starting 100001 onwards.

Once generated, it should store this number in the database. (i hv a column named Call_Ref_No for it)

Can someone please help?

Cheers
PJ

 

[Milo_Minderbinder]

After you have submitted the data, add this code with the correct column

CallRefNum = Range("A" & Range("a65536").End(xlUp).Row).Value + 1

Range("A" & Range("a65536").End(xlUp).Row).Offset(1, 0).Value = CallRefNum

 

[pan_kaj]

How do i get a msg box to appear which displays the following message:

Your call reference number is XXXXXX. Please note this for future reference.

XXXXXX being the number allocated by code given.

 

[Smitty]

If you're using Milo's code you can just use:

MsgBox "Your call reference number is " & CallRefNum & ". Please note this for future reference."

HTH,

 

[pan_kaj]

thanks! works just fine!

 

[pan_kaj]

Milo,

THe code provided stores the last value it passes. I want the starting value for CallRefno to be 100001 and when it allocates the next callrefno for a new call, it should check column R and assign the callrefno as 1 more than that greatest number it finds.

Can you help?

 

[Milo_Minderbinder]

the easiest way would be to prepopulate the first reference number in the appropriate column with 100000. Then the code will add 1 to that for the first entry.

 

[pan_kaj]

I tried that Milo, but the code somehow resets itself, and doesnt check the column! so if i shut the file down and restart the excel workbook, it starts again from 1. confusing........

 

[Milo_Minderbinder]

in what sheet and column are you storing the numbers? If they are on a different sheet, that is probably the problem.

Change Column and sheet names as applicable.

CallRefNum = Sheets("[B]Sheet2[/B]").Range("[B]A[/B]" & Sheets("[B]Sheet2[/B]").Range("[B]a[/B]65536").End(xlUp).Row).Value + 1

    
Sheets("[B]Sheet2[/B]").Range("[B]A[/B]" & Range("[B]a[/B]65536").End(xlUp).Row).Offset(1, 0).Value = CallRefNum