Keep Running Tab

[Guest]

I have a Proposal sheet saved in Excel. We use this to create price
quotations for our customers. One field I'm having trouble with is the
Proposal Number. I'd like it to give a unique number each time the sheet is
opened. Can I referense another sheet and somehow make it use the next number
on the list?

 

[Otto Moehrbach]

Sarah
Taking you literally, this macro will increment the number in A1 each
time the sheet is activated. This means each time the sheet is selected.
If you didn't mean each time the sheet is selected, come back and tell us
what you meant.
Note that this macro is a sheet macro. As such, it must be placed in
the module for the Proposal sheet. To access that module, right-click on
the sheet tab, select View Code, and paste this macro into that module. "X"
out of the module to return to your sheet. HTH Otto
Private Sub Worksheet_Activate()
Range("A1").Value = Range("A1").Value + 1
End Sub

 

[Guest]

Each time the sheet is activated I want it to return the next proposal
number. So that I open it, type proposal (number 1234)- print and exit. Then
later I open the sheet again and the proposal number changes to 1235. I hope
this is more clear. I tried the macro. Copied and pasted it into the code as
you explained. It didn't work though.

 

[Gord Dibben]

sarah

You may be confused about what a "sheet" is.

Excel files are known as Workbooks.

Each workbook can have multiple Worksheets.

Do you want the number changed on a particular Worksheet whenever the Workbook
is opened or whenever the Worksheet is selected?

Otto's sheet event code will change the number each time the Worksheet is
selected, meaning if you select another sheet tab then re-select the first sheet
tab, the number will update.

If you want the number updated when the Workbook is opened you would place this
code in Thisworkbook Module.

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

Right-click on the Excel Icon left of "File" on the menu bar or on the Excel
Icon on the title bar if sheet is not maximized.

Select "View Code" and paste the above code into that module.

Edit "Sheet1" to whatever your sheetname is.


Gord Dibben MS Excel MVP