Shared Workbooks - creating a button using a macro

[CoastalPhil]

Hi, hope someone here can help!

I have a shared workbook which basically lists all the current orders in our factory. It's been decided that we want to be able to input more detail for each order, but without impacting on the main front sheet.

What I've been able to do so far is to write a macro that, when someone clicks on the cell containing the order number, unhides the sheet whose name is that order number, or if it doesn't exist yet, creates it.

The problem I've got is that when I create a new sheet, I want the macro to add a button to that sheet which simply runs another macro to hide the sheet, because I don't want thousands of unhidden tabs. But because the workbook is shared it won't let the first macro create a button.

Does anyone have either a way to allow the macro to create a button, or another way round this?

Thanks

Phil

 

[GTO]

I avoid shared wb's like the plague most generally, but still curious. When you say "create a sheet," do you mean a new/blank sheet?

 

[Andrew Poulsom]

Welcome to MrExcel.

I haven't tried it, but can your macro copy a sheet that already contains a CommandButton from the Control Toolbox?

 

[CoastalPhil]

I avoid shared wb's like the plague most generally, but still curious. When you say "create a sheet," do you mean a new/blank sheet?

I have a sheet called 'Template' - I basically create a blank sheet named with the order number and then copy the contents of 'Template' to the new sheet.

Welcome to MrExcel.

I haven't tried it, but can your macro copy a sheet that already contains a CommandButton from the Control Toolbox?

Only if the workbook isn't shared! Frustrating!

 

[GTO]

It is a ways ago, but the one time I tried this, a ****-awful workaround (if blond recollection serves) was to (in code) change to exclusive, make changes, change to shared (get past the warnings). I believe it was a terrible experience that required counseling...

 

[CoastalPhil]

It is a ways ago, but the one time I tried this, a ****-awful workaround (if blond recollection serves) was to (in code) change to exclusive, make changes, change to shared (get past the warnings). I believe it was a terrible experience that required counseling...

I haven't actually tried this, largely because whenever I change to or from shared, it saves the workbook, and I want to avoid doing that.

Do you know if it's possible to do that in code without it saving the workbook?

(Although from reading your post, I'm not sure I really want to try! )

 

[GTO]

Hopefully someone with better experience at shared workbooks (who isn't on a lot of meds) will stop by, but I do not believe you are getting past the saves when changing.

 

[CoastalPhil]

Hi, does anyone have any further advice for the above?

Thanks

Phil

 

[RoryA]

You're out of luck, I think. No way round the saving when switching modes. Could you not use a toolbar button that hides the active sheet?