Microsoft Excel

[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

 

[JulieD]

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

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?

 

[Guest]

Hi Julie,
I did what you specified but I got the following error:
Compile Error:
Syntax Error

Additional help is always appreciated.
Thanks, Steve

 

[Frank Kabel]

Hi
could you post your complete macro together with the line which was
highlighted by the debugger?

 

[Guest]

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

 

[Frank Kabel]

Hi
do you have a worksheet called 'sheet1' in your file?

 

[Guest]

Yes I do.

Hi
do you have a worksheet called 'sheet1' in your file?

 

[Frank Kabel]

Hi
o.k. try the following macro:
Sub workbook_open()
with me.Sheets("Sheet1").Range("A1")
.value=.value+1
end with
End Sub

 

[Debra Dalgleish]

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

 

[Guest]

Thank You, Thank You, Thank You All. Your assistance was invaluable.
Steve