Counters

[JPA]

Have created a user form in Excel with several drop down combo boxes and a
command button which writes the choices ("Y" or "N") from the combo boxes to
a worksheet.

I would like to put a counter on the form showing the user how many entries
have been written to the worksheet. The theory for this is simple enuff -
simply count the number of rows of data on the worksheet and display it on
the form - but I haven't been able to figure this out!

Help!

JPA.

 

[Chip Pearson]

There are a few ways to do this. The simplest would be to have a variable
(type Long or Integer) within the form and then increment that variable
every time the user clicks the CommandButton. If the range on the worksheet
that has the list of choices is entirely surrounded by blank cells, you
could use the CurrentRegion.Cells.Count to return the number of cells that
have values.

Dim Counter As Long
Counter = ActiveSheet.Range("H6").CurrentRegion.Cells.Count


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)