New Sheets Automatically use template and assigned to a new number

Hi there,

First time poster, longtime fan.

Goal:
*Create a purchase order log with a hyperlinked table of contents that when someone presses new tab a template pops up for them to fill.
*The new template automatically populates a hyperlink in the table of contents.
*Each new template sheet has a purchase order number assigned to it (ex. 0002) as the name of the sheet.

Any guidance would be really appreciated. I've been spinning my wheels here and have not gotten too far. Thank you!

double click the ThisWorkbook and update the NewSheet event code:

Please Login or Register  to view this content.

Although I do not recomend this for anything but a very small company since excel does not handle multiple users well. You'd be much better off with Front-end and Back-end MS-Access databses if more than one person is to use the file/data.

Thanks for your response. I agree that using a macro with multiple users is awkward because you cannot share the workbook across the network. What is Front-end, Back-end MS-Access?

Attached is a copy of what I've gotten so far. Fairly happy with it, now I just need the columns in the table of contents to populate automatically. Any thoughts to have this constantly upload?

Thanks again!

P.O. Log Copy.xlsm

here's my 2¢

oh, and a front-end/back-end is when you split a MS-Access database into two seprate database files, the one with all the data tables lives on the network, and each user has a copy of the other one where all the queries and GUI live.

That way if someone gets locked up on their front end that that doesn't hose everyone else on the back end.

Gregory,

Thank you so much. Seriously this helps more than you know, I don't know how you stumbled across this post specifically but I'm very happy you did.

Very last thing is I cant figure out how to change the order of the log so the most recent (highest number) is at the top of the list.

Gregory,

The very last thing you could hook me up with is correcting the hyperlink that appears on the TOC.

Right now any new item created will have a broken hyperlink, until I update the log by pressing F5 in the VBA Editor.

Again, thank you for the time you've put aside for this

Purchase Order Log.xlsm

Hi Gregory,

Thank you so much for helping put this together. It was running smoothly for a while except today I heard reports of it hiccuping. The New PO button does not work properly and any new sheet is named as (Template (#)).

Attached is the most updated file. I'm sure its a quick fix, but I couldnt find my way around your code.