How can I make cell value update automatically upon opening each time

[nickrussw]

In Microsoft excel, I'd like to enter an order number in one of the cells for each packing slip of each order.
What I'm wondering is, how would I make it so that value would automatically update everytime I open my excel template.

eg. after I finish a packing slip and the order# is 1001 I save and close then the next time I open the template the cell automatically updates to order#1002


Can this be done?


Any help I much appreciated

Thanks
Nick

 

[Rick Rothstein]

In Microsoft excel, I'd like to enter an order number in one of the cells for each packing slip of each order.
What I'm wondering is, how would I make it so that value would automatically update everytime I open my excel template.

eg. after I finish a packing slip and the order# is 1001 I save and close then the next time I open the template the cell automatically updates to order#1002

You can use event code to do that. Press ALT+F11 to bring up the VB editor... once up, look to its left side for a sub-window named "Project-VBAProject" and double click the item in its list named "ThisWorkbook" and then copy/paste this code into the code window that opens up (change the name of the sheet and the cell address to correspond to the cell where your order number is actually located)...

Private Sub Workbook_Open()
  Sheets("[COLOR=#FF0000][B]Sheet1[/B][/COLOR]").Range("[COLOR=#0000FF][B]A1[/B][/COLOR]").Value = Sheets("[COLOR=#FF0000][B]Sheet1[/B][/COLOR]").Range("[COLOR=#0000FF][B]A1[/B][/COLOR]").Value + 1
End Sub

Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[nickrussw]

thanks a lot