easy macro question

[chookers]

Hello, I have been working on my macro storage problem and I have noticed that the problem I was having of my macro button reassigning itself to the latest document based on the template (i.e. it would reassign itself from "template" to "Document1" and if "Document1" was ever deleted, the macro couldn't find the code and wouldn't work) and not staying with the template itself, does not happen if the macro is stored in Personal.xls (I had it in This workbook and I also tried all open workbooks with the same result)

I would love to understand why this is so, but barring that I would love to know if Personal.xls is an appropriate place to put macros that I need to have be used on other people's computers. I have to email the template out to a few different people, should this work?

Thanks for any clues! Excel help is hopeless on this subject I'm sorry to say.

Cheers!

 

[Legacy 98055]

You said this was gonna be an easy one!!!
I protest!!!

 

[chookers]

Mr T -

Would it matter at all that to make this code I went to Tools>Macros then I typed in the name and pressed the 'create' button? I know when you do that the choices to save it to are personal, this workbook, and all open workbooks.

When you go to Tools>Record Macro the choices are Personal, this workbook, and New workbook. Should I have done it there instead?? How did you do yours?

 

[chookers]

Mr. T so I can duplicate what YOU did that worked, can you tell me where you created yours? Where did you store it? I know I know, I THOUGHT is was easy! The only thing I can think to do is tomorrow a.m. (I'm in Australia so we're 18 hours out from US) is write it again from scratch.

If you can tell me where you stored yours I'll do exactly that and see what happens.

Cheers!

 

[Legacy 98055]

I have been trying every which way to figure this out.
I don't thing that re-creating the file is going to help.
The reason it woked earlier was because the macro was connected to a button which is referenced by a sheet class module.
The commandbar is an application level object.
I'm getting ready to search the net for an answer.
Probably a simple answer that will make us both sick!
Tom

 

[chookers]

Mr T Ae you insane? Don't you have anything better to do (Not that I am complaining mind you). So you think it may have something to do with...Oh God what was it? Jesus I cannot become any sicker than i am over this thing. I am beginning to feel that something in the nature of the combination of codes, toolbars and buttons just doesn't lend itself to normal operation for some reason.

Man oh man what have I done??? Ok well listen I have to leave work now, by the way what are you doing awake isn't it about midnight in Indiana? You are truly obsessed. I will check back in the morning and I will also see what I can come up with. I know it is one of those things you're dying to know what's up and you're so right it is probably something simple but maybe, just maybe NOT and we shall have to give up on the macro mystery.

Have a good night Mr T!

Lauren

 

[Legacy 98055]

Thanks to Juan Pablo, we have a solution!

This would probably work in your Autpen and Auto_Close macros, but why mess with it?<pre>

Option Explicit

Private Sub Workbook_Open()
Dim MyCustomBar, MyCustomBarControl
Set MyCustomBar = Application.CommandBars("SOSE Outcomes")
Application.CommandBars("SOSE Outcomes").Enabled = True
For Each MyCustomBarControl In MyCustomBar.Controls
Select Case MyCustomBarControl.Caption
Case "Paste to My Class"
MyCustomBarControl.OnAction = "SOSECutPaste"
Case "4 outcomes per page"
MyCustomBarControl.OnAction = "RH90"
Case "3 outcomes per page"
MyCustomBarControl.OnAction = "RH120"
End Select
Next
Application.CommandBars("SOSE Outcomes").Visible = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("SOSE Outcomes").Visible = False
Application.CommandBars("SOSE Outcomes").Enabled = False
End Sub</pre>

Send a thanks to Juan next time your on the board.
Tom

P.S.
I was advised by Juan to create your custom bars on the fly and then destroy them when the workbook closes.
This message was edited by TsTom on 2002-05-12 23:54

 

[Ivan F Moala]

Hi Chookers.

In your last thread I basically gave you the
answer that Tom has come up with.
If you look here you will see;

http://www.mrexcel.com/board/viewtopic.php?topic=7707&forum=2

Might pay to read the entire thread before
going off on another