macro change cell color - opens original template (unintended)

I created a template for a workschedule. In that template I made a macro, that changes the background color of selected cells. (user selected cells).
The user is supposed to save the template as a regular excel workbook with activated macros.
So far so good. When using the "colorchange macro", the cells change color correct, BUT at the same time my original template opens as well.
The template has other macros in it as well.
So, how do i prevent the template to open, as I only want the active workbook to be affected by my macro?

Here's the code Ive used:

Code:

---

Sub fillcolorblue()
    Selection.Interior.Color = RGB(204, 204, 255)
End Sub

---

Something in the template (macro, named range, validation, ...) probably has the template name / path hard coded

Post the template if you can

How are you running the macro? That is probably the source of the problem - i.e. it's linked to the macro in the template.

Attachment 32039
user enters wanted year in sheet "indtastÅr". This determines the name of day in each month, according to formula in hidden sheet "Ark1", row 20 etc.

When working with the sheets, they can select specific cells and change background color by using the macros yellow, green, red or blue.

When you activate the "color" macros, the original masterworkbook opens. ???

So my question is, why does that happen and how can I change that (dont want that to open.)

When I run the color macros, it does not seem to ask to open the template. Since I don't have the template, I expected a message

There are a lot of REF# errors so that might be a place to look further


Attachment 32040

The macro is not supposed to open the template, as it's open already.
And I don't get those ref errors on any of my computers.

Quote:

---

Originally Posted by chapel

The macro is not supposed to open the template, as it's open already.

---

Why is the template open? Close it and only use the actual workbook based on the template

Quote:

---

And I don't get those ref errors on any of my computers.

---

We all do as we don't have the information or cells that Ref error refers to.

Sorry, what I meant was, the macro is not supposed to open the template. But only work on selected cells in the open workbook (which is the one I posted here).
The ref-errors you get, is probably the result of the macro trying to open the original template, (which it does on my pc)
But the problem is, its not supposed to open anything. Its simply supposed to set the background color on selected cells in my active workbook.
So I dont understand why its trying to open the template with the macro code I posted earlier.

Sorry Chapel but right now you are talking in riddles. The template is something from which you develop a new workbook. The new workbook is something that you might use macros to adjust its limitations to suit the criteria for which it is suitable. Never run macros on your template. So I would advise you to Open your template, save it as a new workbook. Close the template, then run the macros on the newly created workbook to make it suitable for the conditions that you need to be applied.

I would think that the issue would be related to what Aflatoon was getting at in post 3, if the file that is opening the template has a macro that is mapped to the original template. This means that when the macro is run in the file that has been 'saved as' it has to open the template file in order to run the macro, as this is where the macro is located.

So in the below macro window in your template:
Attachment 32042
http://www.vbaexpress.com/forum/imag...AASUVORK5CYII=

Your macro:
'Kopi af Kopi af MASTER TIDSREGISTRERING m makroer1.xlsm'!blå.fillcolorblue

Should read:
fillcolorblue


See if it reads with the file name before the macro in your template file?

If it does, try amending them as above, save the file. Then try the save as again.

Quote:

---

Originally Posted by chapel

Sorry, what I meant was, the macro is not supposed to open the template. But only work on selected cells in the open workbook (which is the one I posted here).
The ref-errors you get, is probably the result of the macro trying to open the original template, (which it does on my pc)
But the problem is, its not supposed to open anything. Its simply supposed to set the background color on selected cells in my active workbook.
So I dont understand why its trying to open the template with the macro code I posted earlier.

---

We understand all that, but you need to get rid of what ever is causing the REF# errors


As I said before, without the TEMPLATE,xltm it's hard to see what is going on

OK. Heres the "master" I want to have the user to open, and then save as "whatever".

Quote:

---

Originally Posted by chapel

OK. Heres the "master" I want to have the user to open, and then save as "whatever".

---

1. Excel has an Excel definition of 'template' -- it's an .xltm or .xlts file. Just calling it a template doesn't make Excel think it's a XLTM file

Attachment 32045

2. I deleted all the REF# ranges since they were not doing anything anyway and saved your file as an XLTM file

3. Double clicking the XLTM creates a new file

4. The macros work without opening the original template file

5. The created file (whatever.xlsm) must be saved by the user as an XLSM file

Note XLTM files are not permitted to be uploaded, so save MASTER TIDSREGISTRERING2.xltm.zip and delete the .zip extension, then double click it

Hi Paul!
thank you for your efforts.
I must be doing something wrong, because I cannot get this to work.
So I did this:
downloaded your edited file. (the zip file)
deleted the zip-extension
opened the file and saved it under a new name.
closed the files
opened the new file
selected cells and activated the macro (change color to blue)
color changed - but the "template file" you created still opens?

Quote:

---

5. The created file (whatever.xlsm) must be saved by the user as an XLSM file

---

How did you open the files?

Did you save it as a XLSM file?

Quote:

---

So I did this:

---

OK - downloaded your edited file. (the zip file)
OK - deleted the zip-extension

Double click the un-zipped file XLTM file without opening it.

Save the file it creates under a new name, make sure it's macro enabled XLSM file

Yes, saved as xlsm.
The files that opens "unintentionally" is "MASTER TIDSREGISTRERING2.XLSB"

OK

Try opening the XLSB file and then FileSaveAs it an XLTM file like my picture in Post #13

Then double click the XLTM file

If that doesn't work, then I'm really stuck

i guess we're stuck, cause this didnt work either:dunno
But thanks for your time and efforts.
Cheers,Chapel

Did you try looking at the suggestion in post 10?

Quote:

---

Originally Posted by chapel

i guess we're stuck, cause this didnt work either:dunno
But thanks for your time and efforts.
Cheers,Chapel

---

Did you delete all the lines causing REF# errors in Post#5 before you saved the XLSB as an XLTM?

Yes, I tried #10. This is how it looks: Attachment 32049

seems like my reply got lost.
There was no rerence to anything that looked like the image in post 10, but ...
I suspect that the macro only activates through the original master (or template), because I opened an new workbook and ran the macro. It´s looking for the initial workbook template (master).
so I must have the connection wrong.
I want to be able to use the coloring macro to work on any new workbook related to the template (master), because i want the user of the template to be able to change the color of selected cells.
I dont know how to explain this in any other way:crying: