What is the best way to have a form add the next number when opening the form?

Keep track of the previous number somewhere???

Maybe in A1 of a hidden worksheet?
Maybe in a text file?
Maybe in the windows registry?

It depends on what you need.

roses427 wrote:

What is the best way to have a form add the next number when opening the form?

--

Dave Peterson

If I were to use a hidden worksheet with the number, would I use the VBA
editor to have it increase the number each time the template is opened.

I am not an advanced excel user. Your suggestions 2 and 3 I wouldn't even
know what to do or where to start.

Basically I have a form that I want the report number to populate
automatically with the report number.

"Dave Peterson" wrote:

Keep track of the previous number somewhere???

Maybe in A1 of a hidden worksheet?
Maybe in a text file?
Maybe in the windows registry?

It depends on what you need.

roses427 wrote:

What is the best way to have a form add the next number when opening the form?

--

Dave Peterson

I don't know what your form looks like--I'm gonna assume that it's a worksheet
that looks like a form.

I'd put a button from the Forms toolbar on the worksheet that needs the form and
let the user click that button when they needed a new number.

I'd create a new worksheet named Log. I'd add some headers to row 1. A1
Contains Date, B1 contains UserName, C1 contains Number. Then have that button
run this macro:

Option Explicit
Sub UpdateLog()
dim wks as worksheet
Dim LastRow as Long

set wks = thisworkbook.worksheets("Log")

with wks
Lastrow = .cells(.rows.count,"A").end(xlup).row
with .cells(lastrow + 1,"A")
.value = application.username
with .offset(0,1)
.numberformat = "mm/dd/yyyy hh:mm:ss"
.value = now
end with
if lastrow = 1 then
.offset(0,2).value = 1 'first value
else
.offset(0,2).value = .offset(-1,2).value + 1
end if
activesheet.range("a1").value= .offset(0,2).value
end with
end with
end sub

This increments the counter in column C and puts that same number in A1 of the
activesheet (the sheet with that button)--I didn't know where you wanted to
display that number.

But it doesn't save the file. I'm not sure when the file should be saved--right
after they get a number or after some confirmation that they did what they
wanted???

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And if you want to look at how the registry could work--or a text file, then
take a look at JE McGimpsey's site:
http://mcgimpsey.com/excel/udfs/sequentialnums.html



roses427 wrote:

If I were to use a hidden worksheet with the number, would I use the VBA
editor to have it increase the number each time the template is opened.

I am not an advanced excel user. Your suggestions 2 and 3 I wouldn't even
know what to do or where to start.

Basically I have a form that I want the report number to populate
automatically with the report number.

"Dave Peterson" wrote:

Keep track of the previous number somewhere???

Maybe in A1 of a hidden worksheet?
Maybe in a text file?
Maybe in the windows registry?

It depends on what you need.

roses427 wrote:

What is the best way to have a form add the next number when opening the form?

--

Dave Peterson


--

Dave Peterson

Thank you. This will work.

"Dave Peterson" wrote:

I don't know what your form looks like--I'm gonna assume that it's a worksheet
that looks like a form.

I'd put a button from the Forms toolbar on the worksheet that needs the form and
let the user click that button when they needed a new number.

I'd create a new worksheet named Log. I'd add some headers to row 1. A1
Contains Date, B1 contains UserName, C1 contains Number. Then have that button
run this macro:

Option Explicit
Sub UpdateLog()
dim wks as worksheet
Dim LastRow as Long

set wks = thisworkbook.worksheets("Log")

with wks
Lastrow = .cells(.rows.count,"A").end(xlup).row
with .cells(lastrow + 1,"A")
.value = application.username
with .offset(0,1)
.numberformat = "mm/dd/yyyy hh:mm:ss"
.value = now
end with
if lastrow = 1 then
.offset(0,2).value = 1 'first value
else
.offset(0,2).value = .offset(-1,2).value + 1
end if
activesheet.range("a1").value= .offset(0,2).value
end with
end with
end sub

This increments the counter in column C and puts that same number in A1 of the
activesheet (the sheet with that button)--I didn't know where you wanted to
display that number.

But it doesn't save the file. I'm not sure when the file should be saved--right
after they get a number or after some confirmation that they did what they
wanted???

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And if you want to look at how the registry could work--or a text file, then
take a look at JE McGimpsey's site:
http://mcgimpsey.com/excel/udfs/sequentialnums.html



roses427 wrote:

If I were to use a hidden worksheet with the number, would I use the VBA
editor to have it increase the number each time the template is opened.

I am not an advanced excel user. Your suggestions 2 and 3 I wouldn't even
know what to do or where to start.

Basically I have a form that I want the report number to populate
automatically with the report number.

"Dave Peterson" wrote:

Keep track of the previous number somewhere???

Maybe in A1 of a hidden worksheet?
Maybe in a text file?
Maybe in the windows registry?

It depends on what you need.

roses427 wrote:

What is the best way to have a form add the next number when opening the form?

--

Dave Peterson


--

Dave Peterson