How to Put a counter in Excel ?

maftoun

New Member
Joined
Apr 28, 2009
Messages
22
I would like to put a counter (number) in my excel sheet. Consider that like a bill number. Any time I open my Excel I want to have new number on top. For example 201, 202 so on and so forth. I would appreciate if anyone had this project before and willing share it with me.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Many Thanks<o:p></o:p>
<o:p> </o:p>
A Maftoun<o:p></o:p>
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hello and welcome to MrExcel.

Try this: right click the Excel logo
excellogofu8.gif
just to the left of File on the menu bar, select View Code and paste in

Code:
Private Sub Workbook_Open()
With Sheets("Sheet1").Range("A1")
    .Value = .Value + 1
End With
End Sub

Change Sheet1 and A1 to suit.
 
Upvote 0
You want the number in the same cell, and to increment every time the file is opened? Maybe something like

Code:
Private Sub Workbook_Open()
With Sheets("Sheet1").Range("A1")
    If Not IsNumeric(.Value) Then .Value = 1
    .Value = .Value + 1
End With
End Sub

But there might be a better solution if you can explain what the purpose of this is.
 
Last edited:
Upvote 0
Either of those macros will run automatically every time you open the workbook.
 
Upvote 0
Click the Office button
officebuttonxj7.png
then Save As and save as Excel Macro-enabled Workbook.

Then click the Office button again, click Excel Options and tick Show Developer tab in the Ribbon then click OK.

On the Developer tab click Macro Security and set it to Enable all macros.

Press ALT + F11 to open the Visual Basic Editor. In the Project - VBA Project window double click ThisWorkbook and paste in the code.

Save, close then re-open your workbook to see the effect.
 
Upvote 0
I can not to pass this step "Then click the Office button again, click Excel Options and tick Show Developer tab in the Ribbon then click OK."
Am I missing somthing? I can not find Options to continue.

Thanks for your help

Abdi Maftoun
 
Upvote 0
Thanks all

I am still working on this simple code. The first code does not work. when Debug it give this massege

Application.Run "Book1!Macro1".

the sesond code works but I have run it every time. there is away when i open my Excel templet just one number to the last one

thanks for your help

Abdi Maftoun
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top