Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keep Running Tab
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keep Running Tab
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 "sarah" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keep Running Tab
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. "Otto Moehrbach" wrote: 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 "sarah" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Keep Running Tab
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 On Wed, 21 Mar 2007 05:05:33 -0700, sarah wrote: 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. "Otto Moehrbach" wrote: 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 "sarah" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running averages | Excel Discussion (Misc queries) | |||
Running Averages | New Users to Excel | |||
Running Add-In from VBA | Excel Discussion (Misc queries) | |||
RUNNING TOTAL | Excel Worksheet Functions | |||
Running Total | Excel Worksheet Functions |