Macro disappearing in Excel template sheet

[Yoeri85]

Hello,

I have made a sheet which will be used for the information transfer about items/products from R&D to me. I will then enter all the new items and/or changes in the ERP system.
To make it as easy as possible for R&D to let them fill in the needed information I have added formulas and data validation in the sheet.
To make sure that R&D do not overwrite the original sheet I want to save it as a template 97-2003.
To make sure that R&D cannot overwrite the formulas and the data validations I have protected the sheet.
Not always every coloumn on this sheet has to be filled. Therefore I made 2 groups that you can extend when needed. The 1st problem I run into (at that time) was that I cannot open the groups while the sheet is protected. It is also not listed in list that allowes people to open the groups when you push on the Protect sheet button. Therefore I had to use VBA (were I am very unfamiliar with). So i looked that up and used the next code:

Private Sub Workbook_Open()
Dim wksht As Worksheet
For Each wksht In ThisWorkbook.Sheets
With wksht
.EnableOutlining = True
.Protect contents:=True, userInterfaceOnly:=True
End With
Next wksht
End Sub

So the problem with grouping and ungrouping is solved with this.
the problem I am asking help for is the following:

When I open the template there is a notification that allowes me to enable the macro. If I want to save the template as another template (lets say Template A1) and I close the template, evrything is still working as it should.
When I open the template again I see again the notification that allowes me to enable the macro. After I have made some changes in the template I would like to save it again as a template under the name Template A1. After this I close the template again.
But when I open the template this time the notification for enabling the macro has gone. the whole macro is gone when I try to look it up. Therefore I cannot open or close the goups that I have in the template.

Does anybody know how this can happen? I have tried several things to save the template as an macro enabled template for instants.
If there are any other ideas how I might be able to make it all still work after aving the template a coupple of times I would be glad to here them. When there are ideas that I might have already tried I will try them again, so please let me hear everything you know.

Best Regards,
Yoeri

 

[Jerry Sullivan]

What do you think of the idea that excel has a problem with the fact that I have data validation in my sheet and as soon as I start working with it that excel does not save the file in a proper way as it should be doing it?

Yes, I think that is a helpful observation. Those tests imply it has something to do with the change you made, but I don't know any reason you shouldn't be able to use DV and macros (I use that combination often).

What happens when you make a different change to the workbook- maybe just enter data in the Unlocked Cells? If you save that file with a new name are macros preserved?

In further reflection, adding the .Unlock step is necessary in order to get the macro to work correctly if the worksheet was previously protected....but not having that step should not cause your macros to go away.

I'd be glad to take a look at your workbook(s) if you will send them to me.

 

[Yoeri85]

About unprotecting the sheet:
The only issue is then that everybody that is working with this sheet can adjustify whatever he or she wants. They can also overwrite some formula's that I have in the template which should not be overwritten. To make that sure you have to lock these cells and protect the sheet to prevent over writing.
The cells that are unlocked are indeed free to write in (with exception of course of the cells with DV)

I am happy to send you the file. The only thing is that I am not allowed to place attachements to the massage. Perhaps you could send me your personal mail address to send the file to?

 

[Jerry Sullivan]

Hi Yoeri,

There's a few things I found in looking at the files you sent (MasterTemplate.xlt, TemplateA1.xlt, TemplateA11.xlt).

1. For all 3 files, there's a Workbook_Open Procedure in the ThisWorkBook Module. This will run each time the Workbook is opened with macros enabled; however the Workbook_Open is written to protect only one Worksheet and doesn't step through all worksheets.

2. For all 3 files, there's a Workbook_Open Procedure in Module1 (a Standard Code Module). This won't be run when the Workbook is opened because it's not in the ThisWorkbook Module. This version of Workbook_Open is written to step through all worksheets.

3. All 3 files prompted me to Enable Macros. The Macro worked to enable the use of the Expand/Collapse Outline Group tools.

So, I'm not sure why you weren't prompted to Enable Macros when opening TemplateA11.xlt, and were not able to find the Macros in that Workbook.
They are in the file - although you should clean those up so there is just one Workbook_Open Procedure and it is located in ThisWorkbook.

 

[Yoeri85]

Hi Jerry,

I am trying to understand what you say but I am not sure if I managed it.

For point 1 and 2:
DoI understand it correctly if I say that the macro in the ThisWorkBook Module should not be deleted and that the macro in Module1 should be deleted?

For point 3:
Could it has something to do with the fact that I might have multiple excel files opened at the same time?

 

[Jerry Sullivan]

For point 1 and 2:
DoI understand it correctly if I say that the macro in the ThisWorkBook Module should not be deleted and that the macro in Module1 should be deleted?

The macro in Module1 should be deleted. The module in ThisWorkBook Module should be modified to step through all worksheets.

For point 3:
Could it has something to do with the fact that I might have multiple excel files opened at the same time?

No, you should still get a warning asking if you want to enable macros.
More importantly, you thought that the macro was missing from TemplateA11. In the file you sent- it's there!

 

[Yoeri85]

Ok. What I have doen now is deleted the macro in Module1. I would think I can delete Module1 as well, right?

I have adjusted the macro in ThisWorkBook and have done some test with it and it is wroking like it should .

Many thanks for your help.

And about TemplateA11:
I think I looked at the wrong place but I am very happy that everything is now working.

This is now the code that I have in ThisWorkBook:

[code:]
Private Sub Workbook_Open()
Dim wksht As Worksheet
For Each wksht In ThisWorkbook.Sheets
With wksht
.Unprotect
.EnableOutlining = True
.Protect contents:=True, userInterfaceOnly:=True
End With
Next wksht
End Sub
[code:\]

The only thing that I need to look up is the code that people are allowed to delete and add rows in the sheet. I know you can check them in the small window you get when you are protecting the worksheet but when I have saved the file and reopen it again then it is not possible anymore.
So that is the final thing I have to look at and then the sheet is finished for used.

Once again: thank you for all your help and the time and effort you put in to. Really appreciated.

 

[Jerry Sullivan]

Glad we were able to figure out that mystery.

It's good housekeeping to delete Module1 if there is no code in there, but it won't affect your workbook.

The parameters to allow Insert/Deleting Rows/Columns are shown below.

.Protect contents:=True, userInterfaceOnly:=True, _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingRows:=True

Be aware that even with these settings set to True, Excel won't allow a User to Delete a Row or Column if there are any Cells Formatted as "Locked" on that Row or Column.

 

[Yoeri85]

Hi Jerry,

I like to have no data in a sheet that does not belong there.
Thank you for the code for inserting and deleting rows and coloumns.

I have now to adjust some little things to adjust in the sheet and to add some coloumns so that the people that has to work with it that they are also able to work with it. Then it is compleet!

I hope I may use your knowledge in the future again if needed.

Thanks!

 

[Jerry Sullivan]

You're very welcome Yoeri.