Msg box to auto generate invoice number

[chhavibhargava]

Hi all,

I am creating a form using the ActiveX buttons.
My form is already up and running. I only want one more addition to it.

When submitting the form the an automated invoice number gets generated in the database, which is protected sheet and cannot be viewed by anyone except me.
So, what I want is that when I click the submit button it shows a pop up msg box generating the same database invoice number, so that, people filling the form can note down the invoice numbers.

Please help with the VBA code for the same.

 

[mikerickson]

What is the sheet name and the cell address of the newly generated invoice number?
Wouldn't you want this number to also go onto the customer's copy of the invoice? If so, what sheet and cell is that?

 

[chhavibhargava]

What is the sheet name and the cell address of the newly generated invoice number?
Wouldn't you want this number to also go onto the customer's copy of the invoice? If so, what sheet and cell is that?

Hi,

sheet name is database and the cell address is B2:B1048576.

So, to explain my form.
I fill in a few details and when I click on submit all the data is saved in the database. There is one column for Invoice number, which is autogenerated in the database.
Now, I only need that invoice number to come a pop up box while I click the submit button.

It would be really great if you can help me with the same.

 

[mikerickson]

The invoice number you want is somewhere in Database!B2:B1048576, but which cell. The one last one in the column with only blank cells below it?
If so

MsgBox Range("Database!$B1048576").End(xlUp)

should get you your invoice number, but you probably want to put it somewhere else so you and your customer can reference it in the future
At some point, you will be generating an invoice to send to the customer. If I were the user, I would like to see that invoice rather than just a box with a multi-digit number that I have to either write down or mis-remember if I want to use it again.

 

[chhavibhargava]

The invoice number you want is somewhere in Database!B2:B1048576, but which cell. The one last one in the column with only blank cells below it?
If so

MsgBox Range("Database!$B1048576").End(xlUp)

should get you your invoice number, but you probably want to put it somewhere else so you and your customer can reference it in the future
At some point, you will be generating an invoice to send to the customer. If I were the user, I would like to see that invoice rather than just a box with a multi-digit number that I have to either write down or mis-remember if I want to use it again.

Thanks for the prompt response sir.

But this code is showing error. if you can elaborate the code a bit, that would be really helpful.

Also, there is a copy for the customers. This msg box is for some other purpose.

 

[mikerickson]

Try

MsgBox Sheets("DataBase").Cells(Rows.Count, 2).End(xlUp).Value