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
Similar Threads
