Microsoft Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using a repetitive form which was created in Excel. I would like to set one cell to act as a counter, so that each time the form was opened, the number would increase. Is there anyone with a solution?
Thank You
 
Hi

in the ThisWorkbook module (right mouse click on sheet tab, choose view
code, click on ThisWorkbook under your workbook on the left hand side of the
VB Editor) ... put something in similar to this
--start code--
Sub workbook_open()
Sheets("Sheet1").Range("A1") = Range("A1").Value + 1
End Sub
--end code--

where the cell you want to increment is in cell A1 of sheet1

hope this helps
Cheers
JulieD


Lineman116 said:
I am using a repetitive form which was created in Excel. I would like to
Click to expand...
set one cell to act as a counter, so that each time the form was opened, the
number would increase. Is there anyone with a solution?
 
Hi Julie,
I did what you specified but I got the following error:
Compile Error:
Syntax Error

Additional help is always appreciated.
Thanks, Steve
 
Hi
could you post your complete macro together with the line which was
highlighted by the debugger?
 
Frank, I copied and pasted what Julie wrote just to test the procedure. I got the previously mentioned error message with the "sheets" line highlighted.
Thanks,
Steve
 
Hi
o.k. try the following macro:
Sub workbook_open()
with me.Sheets("Sheet1").Range("A1")
.value=.value+1
end with
End Sub
 
A syntax error means that there's a problem with the way the line of
code was written, and it should change to red text when you move the
cursor to a different line of code.

Maybe there's a space before one of the periods, or an extra character
somewhere.

Also, the code, as written could give incorrect results if Sheet1 is not
selected when the workbook opens. It would add 1 to the value in cell A1
of the active sheet. To correct this, you could use the following code:

Sub Workbook_Open()
Sheets("Sheet1").Range("A1") = _
Sheets("Sheet1").Range("A1").Value + 1
End Sub
 
Back
Top