Automatically filling in a unique quote number on a form

[wattsup]

I have a form that is used to input information into a database, and as part of that form there is a quote number entry that must be filled in with a unique six digit number. These numbers are basically six digit numbers that go up in 10s, i.e.

If the last used number was 110010 then the next number will be 110020.

What I would like to do is when the form initialises that the next number is automatically placed in the quote number textbox. I have done a similar thing for todays date but I am new to VBA and am finding this quite tricky. Anybody have any ideas?

Thank you in advance

wattsup

 

[Gary McMaster]

Reading and writing the value to a "Custom Property" works OK for something like this.

Gary

 

[Sthrncali]

Where is the location of the last number entered?

The macro would simply need to know where to look for the last entered number, then add 10 to it right?

Example: If the entries were stored on Sheet 3, and the Qoute Number goes in Col A, then you could add this to the UserForm Initialize Event:

Dim QteNumber as Integer
 
QteNumber = Worksheets("Sheet 3").Range("A1").End(xlDown).Value
QteNumber = QteNumber + 10
UserForm1.TxtBox1.Value = QteNumber

 

[wattsup]

The problem is it won't always be the last numbered entered and the database will often be reordered as well. Is there a way I could find the max of the range of numbers (which I have named as QuoteNumberList) and then add 10 to it?

 

[Sthrncali]

Sure use this:


Dim i As Long

i = Application.WorksheetFunction.Max(Sheet1.Range("QteList"))

'Where Sheet1.Range("QteList") = your Defined Name & Sheet it resides on.

i = i + 10
UserForm1.TxtBox1.Value = i