macro storage help saga!

[chookers]

Hi again.

Well I have emailed myself the template and here is what's happeneing.

Background is I made a template, attached a toolbar to it with 5 macro buttons. Stored the macro VBA code in 'This Workbook', attached the code to the buttons, and the toolbar to the workbook, no worries.

Here's what it's doing. Let's say I open the template, and create a document called 'test'. I save 'test', and close Excel. But later, I realise I don't need 'test', and delete it. So far so good.

BUT the next time I use 'template' and use a macro button, I get the message 'test1 (or whatever).xls could not be found. It's like the last document the macros were used in HAS to be on the computer. If it's deleted, or in my case now if I email it out, the macros cannot be found without that last document they were used in. The toolbar is programmed to open and close upon the template opening and closing, but they just don't seem to stay with the template, they go to 'doc1 based on the template', and won't work at all if 'doc 1 based on the template is deleted or emailed out or whatever. I have to reassign the macros to 'this workbook' each time, as they become assigned to 'doc1 based on the template' and so on.

I am totally stumped how these storage locations work. What is the BEST place for a macro that needs to be attached to a worksheet and mailed out as an attachment. Is it All open workbooks, Personal.xls, or this workbook. I have tried to use help for this, but unfortunately, it isn't any help at all for this.

Feedback appreciated/needed!!!!

Many thanks, Lauren

 

[James]

A complete stab in the dark, if you set-up your template as Read-Only and were forced to save the new book as a differant name keep all intact?

James

 

[chookers]

Thanks James, I'll try that but I have my doubts. I've noticed this sort of thing on other projects but never had to worry about fixing it cuz I was the only one using the program. Now they like the program and want other people at other schools to use it and I am freaking out! Everything works great, it just seems I've got the bloody thing stored in the wrong place or something. Any other advice much needed!!

Thank you!

 

[chookers]

Tried the read only - no luck. When I look under assign macros it just shows the last doc the macro was used in (i.e. CWindowsdesktoptest.xls!MacroInQuestion )and the one I originally assigned to the button (ie just MacroInQuestion) is somewhere down the list and I have to keep reassigning it if 'test' is deleted . It doesn't matter where I put the macro, personal, this workbook or all open workbooks, this still happens.

Grrr.............

 

[James]

Hi Lauren,

Would you care to post your code? Maybe that would make it clear as to why it's not working.

James

 

[Ivan F Moala]

Macros you Create and attach to a toolbar are assigned to that particular book
what ever the name, and is stored in the
.xlb file as such. Changing the name of the
workbook does not change the reference for
the Toolbar macros which are stored in this xlb file. (the positions of the toolbars by the
way are stored in the .pip file).
Best way around this is to dynamically create your tollbar @ startup THEN delete
when the workbook closes.

 

[chookers]

Hi James, okay here it is.

Just now I went to Explorer>Profiles>Application Data>Templates and opened the template that way THEN Right clicked on the toolbar, clicked my CutPaste Button to look at 'assign macro' and 'SOSE1.xls!SOSECutPaste' was assigned to the button (basically document1 based on the template) instead of the original CutPaste macro named below instead of the original macro. If I now delete SOSE1 and try to use the template as the base for another document, I can't use my cutpaste macro without reassigning it from SOSE1.xls!SOSECutPaste to just SOSECutPaste. This is the case with all the codes assigned to my buttons, aside from my AutoOpen and AutoClose which open the toolbar. (well, at least something works!)

Here is code. I hate this I always do projects in Access which I feel quite clever in and had this one thrown at me in Excel which I haven't studied that much so am feeling so LAME with a lot of things that seem to me they should be simple!

God help me!

Sub SOSECutPaste()
' CutPaste Macro
' Macro recorded 26/02/2002 by The Principal
'

'
Dim shtOriginal As String
Application.ScreenUpdating = False
shtOriginal = ActiveSheet.Name
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Copy
Sheets("My Class").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
With Selection.Font
.Name = "Arial"
.Size = 11
.ColorIndex = 0
End With
Selection.Columns("D:AF").Select
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Columns("A:A").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Columns("A:A").Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(0, -3).Range("A1").Select
Sheets(shtOriginal).Select
ActiveCell.Offset(1, 1).Activate
Application.ScreenUpdating = True

End p.s. seeing that address of yours makes me homesick! Am from So Cal and have been living in Australia for 5 years - missing the good old US of A!

It seems to me the problem maybe isn't so much in the code as what I've done to get it attached to the button (although that is all pretty straightforward, ain't it?)

See any problems???

Thanks mate(s)!