Worksheet- activate macro
 

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?

Worksheet- activate macro
 

You mean you want this to happen when you open the workbook--not activate a
sheet within that workbook?

If yes, this goes behind the ThisWorkbook module (not behind the worksheet, not
in a general module):

Option Explicit
Sub Workbook_Open()
with me.worksheets("whateversheetholdsthecounter").rang e("a1")
.value = .value + 1
.printout
end with

me.save
me.close savechanges:=false
End sub

This will open, add one to a cell, print that worksheet, save the workbook and
close the workbook.

You may not want to include the .close statement. It could make it difficult to
test/change.

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?




--

Dave Peterson

Worksheet- activate macro
 

How do I access the workbook module?
Sorry, this is pretty far above my skill level. But everytime I (or someone
else on my network) access the 'Proposal #2" I want the proposal number (cell
K2) to add one.

"Dave Peterson" wrote:

You mean you want this to happen when you open the workbook--not activate a
sheet within that workbook?

If yes, this goes behind the ThisWorkbook module (not behind the worksheet, not
in a general module):

Option Explicit
Sub Workbook_Open()
with me.worksheets("whateversheetholdsthecounter").rang e("a1")
.value = .value + 1
.printout
end with

me.save
me.close savechanges:=false
End sub

This will open, add one to a cell, print that worksheet, save the workbook and
close the workbook.

You may not want to include the .close statement. It could make it difficult to
test/change.

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?




--

Dave Peterson

Worksheet- activate macro
 

Open the VBE (where macros live)
Hit ctrl-r to see the project explorer (like windows explorer)
select your workbook's project
Use the + to expand the objects
look for ThisWorkbook and double click on it.

Paste the code in that newly opened code window.

sarah wrote:

How do I access the workbook module?
Sorry, this is pretty far above my skill level. But everytime I (or someone
else on my network) access the 'Proposal #2" I want the proposal number (cell
K2) to add one.

"Dave Peterson" wrote:

You mean you want this to happen when you open the workbook--not activate a
sheet within that workbook?

If yes, this goes behind the ThisWorkbook module (not behind the worksheet, not
in a general module):

Option Explicit
Sub Workbook_Open()
with me.worksheets("whateversheetholdsthecounter").rang e("a1")
.value = .value + 1
.printout
end with

me.save
me.close savechanges:=false
End sub

This will open, add one to a cell, print that worksheet, save the workbook and
close the workbook.

You may not want to include the .close statement. It could make it difficult to
test/change.

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?




--

Dave Peterson


--

Dave Peterson