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]

Hi Yoeri and Welcome to the Board,

What you describe should work as desired if you save the file in any of the formats that allow macros. (.xlt, .xls, .xlsm, .xltm).

What file extensions have you been using for your MasterTemplate and Template A1?

 

[Yoeri85]

Hi Jerry,

Thank you for the welcome and thank you for your reply.
The Mastertemplate is an xlt. So I try to keep Template A1 also in xlt.
I have tried so far to save template A1 in xlsm and xltm. Here I have the same results.

Could be that is because I have excel 2007 or has that nothing to do with it?

 

[Jerry Sullivan]

Having Excel 2007 should not cause this to happen.

If you save your MasterTemplate.xlt as TemplateA1.xlt, are you able to close and reopen TemplateA1.xlt repeatedly and have the macros present and functioning each time, provided that you don't resave the file as TemplateA1.xlt?

 

[Yoeri85]

I have just done the actions as you describt.
I took the MasterTemplate and saved it as TemplateA1.xlt. When I then close the template without having done anything (so just saving the template under another name, in this case TemplateA1.xlt) and reopen it again I see that I receive the option to enable the macros.
When I choose to enable this content I start to make a change in the sheet. I have a coloumn (E) that has a data validation to make sure people are not entering the wrong information. They can choose out of a list and that was what I have just done.
Now I have saved this file as TemplateA11 (that is what excel automaticly makes of it). I have closed the template and reopend again. And now I do not get the option to enable the macro.

 

[Jerry Sullivan]

Hmmm....I'm not able to replicate that problem.

By default, when doing a SaveAs on file TemplateA1.xlt, Excel 2010 puts in "TemplateA1.xlt" in the FileName box, and if you click ok it will save the file as:

TemplateA1.xlt.xlt

Is it possible that you have two versions of this file:
TemplateA1.xlt.xlt
TemplateA1.xlt

...and that you are opening an older version?
(That's a long-shot but I can't think of anything else at the moment.)

 

[Yoeri85]

No, I do not have an xlt.xlt. I have the set up on my computer that I can see what type of file I am working with. So after each file I have the type of .xlt for example. When the name is changed of the template (by excel or by me, that doe not really matter) it will always before the .xlt.

If you like I can send you the master template file to your mail.

 

[Yoeri85]

I have just done another action:
I took the MasterTemplate and saved it as TemplateA1.xlt. When I then close the template without having done anything (so just saving the template under another name, in this case TemplateA1.xlt) and reopen it again I see that I receive the option to enable the macros.
The previous time I started to make a change in the sheet: I have a coloumn (E) that has a data validation to make sure people are not entering the wrong information. They can choose out of a list and that was what I have just done. Now I did not make a change at all and saved the template again with the new name excel has come up with.
I have closed the template and reopend again. And now I do do get the option to enable the macro.

Could it have something to do that data validation and macros and template as a combination will not work together as it should?
<!-- / message -->

 

[Jerry Sullivan]

Ahhhh...that helps. Just realized the problem is that you need to Unprotect the sheet first if it is already protected.

Try....

Private Sub Workbook_Open()
    Dim wksht As Worksheet
    For Each wksht In ThisWorkbook.Sheets
         With wksht
      [COLOR="Blue"][B]      .Unprotect[/B][/COLOR]
            .EnableOutlining = True
            .Protect Contents:=True, UserInterfaceOnly:=True
        End With
    Next wksht
End Sub

 

[Yoeri85]

I have copy paste your code in the MasterTemplate in the ALT-F11 macro screen and saved it as MasterTemplate.xlt.
When I close the template and reopen it I receive the notification to enable the macro (content).
When I then save it ounder the name TemplateA1 without any changes and close the template and reopen it again I still get the notification to enable the macro (content). Only when I make a change in the template by choosing something out of a list that I have created in the sheet and save it again (name does not really matter) then next time I am opening the template the notification (and with that the macro) are gone.

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?