MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Can it be Done or have I found an Excel Shortcoming?!


Posted by Steven Beers on November 21, 2001 2:58 PM

I need to place a requisition form on the company network so that all departments can access the form and completesubmit it. The problem is that these forms need to have a serial number automatically assigned each time the form is accessed or completed. How can this be accomplished?


Posted by Tom Urtis on November 22, 2001 1:39 AM

Is this what you want?

If I understand your post correctly, you want to have a new, unique number assigned to your requisition form every time the form is accessed, or, in Excel terms, every time the worksheet holding the requisition form is activated.

Right-click on the worksheet tab, then left click on View Code, and paste this into the sheet module:

Private Sub Worksheet_Activate()
Range("A1").Value = Range("A1").Value + 1
End Sub

This code assumes your serial number is in cell A1, so modify the code as needed for the cell reference.

Any help?

Tom Urtis

Posted by Robb on November 22, 2001 1:46 AM

Steven

Assuming the serial numbers are consecutive and in, say, A1 on sheet1, this code
would work fi used in conjunction with whatever event(s) you choose:

With Worksheets("Sheet1")
.[A1] = .[A1] + 1
End with

This will simply add one (1) to the number in A1 each time it is triggered by
the chosen event(s).

Any help?

Regards

Posted by Steven Beers on November 22, 2001 11:53 AM

Re: Is this what you want?

Thank you, but not quite. Each time the spreadsheet is loaded the serial number needs to be incremented by one and saved, so that next time someone loads the spreadsheet, they have the next sequential number.

: I need to place a requisition form on the company network so that all departments can access the form and completesubmit it. The problem is that these forms need to have a serial number automatically assigned each time the form is accessed or completed. How can this be accomplished?

Posted by Tom Urtis on November 22, 2001 4:25 PM

Re: Is this what you want?

I'm sure we can find the solution, but I'm now I'm unsure of your problem.

The code Robb and I sent you would increase the value of your serial number by 1.

Maybe I don't understand what you mean by "load the spreadsheet". Please give an example of what an end user actually does with the file, or wants to do, so we can know how to make this happen.

Tom Urtis : If I understand your post correctly, you want to have a new, unique number assigned to your requisition form every time the form is accessed, or, in Excel terms, every time the worksheet holding the requisition form is activated. : Right-click on the worksheet tab, then left click on View Code, and paste this into the sheet module

:

Posted by Steven Beers on November 25, 2001 12:06 PM

Re: Is this what you want?

The spreadsheet file would be on the network. The user would click on the shortcut and Excel and the Purchase Requisition form would load. At this point the serial number would increment by one so that the user sees the next serial number in sequence on the Purchase Requisition form. The user would complete the form and email it to the purchasing agent. So, each time someone loaded/accessed the form, the next serial number would appear.

There will be numerous users. Each user would have a unique serial number on their purchase requisition (in order to track the requisition).

Thank you very much for your time and assistance. I'm sure we can find the solution, but I'm now I'm unsure of your problem. The code Robb and I sent you would increase the value of your serial number by 1. Maybe I don't understand what you mean by "load the spreadsheet". Please give an example of what an end user actually does with the file, or wants to do, so we can know how to make this happen. : Thank you, but not quite. Each time the spreadsheet is loaded the serial number needs to be incremented by one and saved, so that next time someone loads the spreadsheet, they have the next sequential number. :