Toolbar Macros

I posted this in the Programming newsgroup, but was given a Macro solution to this 
problem.

So let me post again here.  I'm just looking for an explanation as to how this stuff works.

-----------------------------
I'm so confused!!!

Some history...

I have a workbook that I created maybe eight years ago, that has some simple macros
that are run ONLY from this workbook, and are accessed by clicking on Tools menu,
where the macros are listed at the bottom of the normal list.  This macros (and the items
on the Tools menu) are ONLY available for this particular workbook.

I have tried to duplicate this action in two ways:
1.  I have simply dragged an item from the toolbar customize box to the Tools menu, and
this works fine, but then it is present on any workbook I open from then on, and of
course, I only want it to be present for this workbook.

2.  I have saved the original workbook as a new workbook, then deleted everything in
the new workbook, including one of the Tools Menu items, and reassigned new macros
to the Tools Menu items.  I save this new workbook, close it, reopen it, and the Tools
menu looks just as it did without saving it.

What I want to do is create macros, assign them to a toolbar (especially if it is a Tools
menu item), and make these menu items (or toolbar buttons) available ONLY to this
workbook.  I am having no luck accomplishing this.

Can anyone explain why I'm having these problems?

Thanks,
Bernie


0
iamnu (61)
7/30/2003 5:55:58 PM
excel 39879 articles. 2 followers. Follow

2 Replies
849 Views

Similar Articles

[PageSpeed] 56

Unless you do something special, those changes you've made to the menubar will
be available for every workbook.  Just because you load them with a particular
workbook doesn't mean that they only work against that workbook.   (Well, that's
the way xl97 and up work.  I don't recall enough of xl95 to know how it worked.)

One way to stop them from working on a different workbook is something like this
at the top of each of your macros:

	sub mymac1()
           if activeworkbook.name <> thisworkbook.name then
                beep
                exit sub
           end if
        'rest of your code.

But the options still exist under the tools menu.

Anther way is to hide/delete those options when you leave this workbook.  Then
show them again when you activate this workbook.

That's what Ron de Bruin's code was trying to do for you in your post in
..programming.
http://google.com/groups?threadm=eLzeF2rVDHA.1680%40tk2msftngp13.phx.gbl



bw wrote:
> 
> I posted this in the Programming newsgroup, but was given a Macro solution to this
> problem.
> 
> So let me post again here.  I'm just looking for an explanation as to how this stuff works.
> 
> -----------------------------
> I'm so confused!!!
> 
> Some history...
> 
> I have a workbook that I created maybe eight years ago, that has some simple macros
> that are run ONLY from this workbook, and are accessed by clicking on Tools menu,
> where the macros are listed at the bottom of the normal list.  This macros (and the items
> on the Tools menu) are ONLY available for this particular workbook.
> 
> I have tried to duplicate this action in two ways:
> 1.  I have simply dragged an item from the toolbar customize box to the Tools menu, and
> this works fine, but then it is present on any workbook I open from then on, and of
> course, I only want it to be present for this workbook.
> 
> 2.  I have saved the original workbook as a new workbook, then deleted everything in
> the new workbook, including one of the Tools Menu items, and reassigned new macros
> to the Tools Menu items.  I save this new workbook, close it, reopen it, and the Tools
> menu looks just as it did without saving it.
> 
> What I want to do is create macros, assign them to a toolbar (especially if it is a Tools
> menu item), and make these menu items (or toolbar buttons) available ONLY to this
> workbook.  I am having no luck accomplishing this.
> 
> Can anyone explain why I'm having these problems?
> 
> Thanks,
> Bernie

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
7/30/2003 9:39:24 PM
Dave,
Ok, thanks...

I just looked at the link you povided, and see that Gord Dibben has provided the answer I 
was looking for.

The macros you and Ron have provided will require me to check them out.  From your 
description, I guess they will provide a work around to what I currently have.

Thanks again,
Bernie


On Wed, 30 Jul 2003 16:39:24 -0500, Dave Peterson <ec35720@msn.com> wrote:
> Unless you do something special, those changes you've made to the menubar will
> be available for every workbook.  Just because you load them with a particular
> workbook doesn't mean that they only work against that workbook.   (Well, that's
> the way xl97 and up work.  I don't recall enough of xl95 to know how it worked.)
> 
> One way to stop them from working on a different workbook is something like this
> at the top of each of your macros:
> 
> 	sub mymac1()
>            if activeworkbook.name <> thisworkbook.name then
>                 beep
>                 exit sub
>            end if
>         'rest of your code.
> 
> But the options still exist under the tools menu.
> 
> Anther way is to hide/delete those options when you leave this workbook.  Then
> show them again when you activate this workbook.
> 
> That's what Ron de Bruin's code was trying to do for you in your post in
> ..programming.
> http://google.com/groups?threadm=eLzeF2rVDHA.1680%40tk2msftngp13.phx.gbl
> 
> 
> 
> bw wrote:
> > 
> > I posted this in the Programming newsgroup, but was given a Macro solution to this
> > problem.
> > 
> > So let me post again here.  I'm just looking for an explanation as to how this stuff 
works.
> > 
> > -----------------------------
> > I'm so confused!!!
> > 
> > Some history...
> > 
> > I have a workbook that I created maybe eight years ago, that has some simple 
macros
> > that are run ONLY from this workbook, and are accessed by clicking on Tools menu,
> > where the macros are listed at the bottom of the normal list.  This macros (and the 
items
> > on the Tools menu) are ONLY available for this particular workbook.
> > 
> > I have tried to duplicate this action in two ways:
> > 1.  I have simply dragged an item from the toolbar customize box to the Tools menu, 
and
> > this works fine, but then it is present on any workbook I open from then on, and of
> > course, I only want it to be present for this workbook.
> > 
> > 2.  I have saved the original workbook as a new workbook, then deleted everything 
in
> > the new workbook, including one of the Tools Menu items, and reassigned new 
macros
> > to the Tools Menu items.  I save this new workbook, close it, reopen it, and the Tools
> > menu looks just as it did without saving it.
> > 
> > What I want to do is create macros, assign them to a toolbar (especially if it is a Tools
> > menu item), and make these menu items (or toolbar buttons) available ONLY to this
> > workbook.  I am having no luck accomplishing this.
> > 
> > Can anyone explain why I'm having these problems?
> > 
> > Thanks,
> > Bernie
> 
> -- 
> 
> Dave Peterson
> ec35720@msn.com



0
iamnu (61)
7/30/2003 10:37:34 PM
Reply:

Similar Artilces:

SetWarning in macro
I set my SetWarnings to Yes for an import and append Macro. The dialog box pops up and works correctly. If I chose no. the macro will not fuction. However my MsgBox still appears with the message that I input for when the macro works. How can I keep that msgbox from showing up if I select no when my SetWarnings dialog box answer is no? If this is Access 2007, you need to trust the location where your database resides. To trust your folder, click: * Office Button (top left) * Access Options (bottom of dialog) * Trust Center (left) * Trust Center Settings (bu...

Toolbar Macros
I posted this in the Programming newsgroup, but was given a Macro solution to this problem. So let me post again here. I'm just looking for an explanation as to how this stuff works. ----------------------------- I'm so confused!!! Some history... I have a workbook that I created maybe eight years ago, that has some simple macros that are run ONLY from this workbook, and are accessed by clicking on Tools menu, where the macros are listed at the bottom of the normal list. This macros (and the items on the Tools menu) are ONLY available for this particular workbook. I have tried ...

Can't remove "Visual Basic" toolbar & cannot add toolbars thru cus
Hi, I Can't remove the "Visual Basic" toolbar which has positioned itself right in the centre of the screen; it can't be removed or moved I also cannot add toolbars through the customise menu. Thanks. ...

Copying Macro
Hi I run Win XP with Excel 2K I have a worksheet with a pivot table on it. I would like to copy the grand totals of the pivot table into another worksheet. What i need is to have a macro that highlights the range in the pivot table and copies it to the next worksheet. The copied data should go into the next available blank row of the work sheet. I have tried recording this macro and it works fine the first time I run it but the next time I run it it copies over the last entry instead of going to the next blank row. This is despite the fact that when I record the macro I go to the col...

macros disappear in Excel 2003
Macros I created using VBA code disappear in future sessions. They appear to have been created initially in the Personal Macro Workbook Does Personal.xls not open when you start Excel? Look for it under Unhide in Windows>Unhide. Is it there? If not, check out Help>About>Disabled Items Has Personal.xls been disabled? If so, enable it and see if your macros are available. And.................are you sure the macros were created in Personal Macro Workbook? Gord Dibben MS Excel MVP On Mon, 15 Mar 2010 12:53:01 -0700, Ellen <Ellen@discussions.microsoft.co...

ToolBar
Hi, I would like to have text on right on my toolbar. But only at some items. Like IE. I tryed it, but I had the space for text on the right at all items. And the space was long as the longest lext. Can U advise me, where I can find how to do this? Thanks a lot. Robert Have you looked at example MFCIE in MSDN which shows how to use MFC to create IE. I think what you need is individual sizing of each of the buttons. This sample should show it. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Robert Camsky" <robertcamsky@atlas.cz> wrote in message news:OKuWLdf6DHA.226...

Enlarge QAT (Quick Action Toolbar) icons
I've looked for a way (& through this forum) to enlarge the icons in 'my' QAT, but this does not appear to be possible. Am I overlooking some tweak that can do this or is the size of those icons really fixed? Thanks in advance for your comments. To get bigger icons, I believe you will have to change the screen resolution in Windows. -- Stefan Blom Microsoft Word MVP "PLDV" <PLDV@discussions.microsoft.com> wrote in message news:63C6AAD4-5308-4183-B227-BFED12196793@microsoft.com... > I've looked for a way (& through this forum...

Macro to copy macros
Hello, Is there a way to copy a macro from one workbook to another using a macro? Bill See Chip Pearson's site Bill http://www.cpearson.com/excel/vbe.htm -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Bill" <sorry_no_spam@aol.com> wrote in message news:403a4800$1@usenet01.boi.hp.com... > Hello, > > Is there a way to copy a macro from one workbook to another using a macro? > > Bill > > Thanks Ron. It seems to work, though it is coincidental with my use of this method that I was warned of a "Bloodhound" v...

Remove a third party (PDF) toolbar item from Outlook's Untitled message toolbar
How does one remove a third party (Adobe PDF) toolbar item from Outlook's Untitled message toolbar permanently. Where is this info stored? sounds like you have Adobe Acrobat installed. Why not simply un-install it. Hope this is useful to you. Let us know. rms SBCM wrote: > How does one remove a third party (Adobe PDF) toolbar item > from Outlook's Untitled message toolbar permanently. > Where is this info stored? ...

Run macro on selected sheets
Hi I have a workbook with 103 sheets, I want a macro to run on 100 of the sheets. Here is the macro, can anyone help me with this? Sub ReconsileStockCard() Range("D3").Select ActiveCell.FormulaR1C1 = "=SUM(R[4]C[-2]:R[97]C[-2])" Range("D3").Copy Range("B7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A7:A36,B8:B36").ClearContents Range("D3").ClearContents Range("D7").ClearContents...

Drawing from Toolbar Crashes Excel 2008
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I am trying to view &quot;Drawing Toolbar&quot; for Excel. When I go to View, Toolbars, Drawing, Excel 2008 becomes totally unresponsive and I have to kill the application. I simply cannot view this tool. <br><br>Has anybody experienced a similar problem? If yes, how do I overcome this issue? <br><br>Thanks! While Excel is not running go to: UserName/Library/Preferences/Microsoft/Office 2008 Delete the Excel Toolbars (12) file, then launch Excel. HTH |:>) Bob Jones ...

How to Create an Excel Macro to send a meeting request.
I want to try and setup a Macro to send meeting requests to my teams calendar. What we do is use it to book are equipment setups. Right now we copy and past the requests in to meeting request to an outlook calendar and send them to ourselfs. This way we see who is working on what. What i want to do is put basic information in to an excel form. The form is going to have the following. Who the request is for. Who is assigned to the request. The date and time needed. The date and time returnd. Type of equipment request. Ticket number from our call logging system. Where the equipment is ...

Cannot delete a table with a macro action
In Access 2003, I am developing a database and create a temporary table with a make table query. Once I am done with the table and have closed all objects based on that table, Access will not alow me to delete the table with a macro action "DeleteObject". It tells me that the data base engine could not lock the table because another user is accessing it. There is no other user and no object is open which is connected with that table. I have tried everything I could think of, in vain. When I close the error mesage and select the table in the database window, I can delete it. I ...

MSCRM 4.0 ISV Custom buttons not visible in Contact Detail Toolbar
Hello, After enabline isv integration in 4.0, I can see in the Accounts detail level toolbar the sample isv buttons, but when I goto Contacts detail toolbar, none are visible. I just added a button to the detail level toolbar in CRM 4.0. It was added basically to the same place as my old 3.0 in the isv.config, but after adding it wasn't visible. I then went to the ReadyServer image from Microsoft, and noticed that the isv.config was not present! (isv.xsd was present and accounted for). Since I could see the isv buttons in the account entity detail toolbar, I added my custom butto...

Learning VBA & Macros
Dear All, I am begining to learn the VBA aspect of Excel in order to develop applications in Excel. Any suggestions how I may proceed with this effectively. Thanks vvraj -- vvraj ------------------------------------------------------------------------ vvraj's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33100 View this thread: http://www.excelforum.com/showthread.php?threadid=529106 vvraj, here are some places to start, Have a look at the links posted on this site, under the heading of, Excel Lessons & Tutorials (#tutorials) http://www.mvps.org/dmc...

Remove macro warning
Hi - I have a spreadsheet that I take some raw data that I run a macro on to clean the data then (once the macro is complete) I send it on to managers in my department. I don't want to send the file with the macro held within it, so I remove it from the copy I send out. The thing is, even with the macro deleted, when people open the file, they still get a message offering them the choice to enable macros, even though there is no macro. Can anyone tell me how I go about removing the reference to the macro so that this message no longer appears? Thanks Neil Hi, Neil Perry Delete ...

Size of toolbar buttons
Hello All How can I set the different size to different button of toolbar ( Exactly like Internet Explorer..The Width of Back and forward buttons is different ) What is style to be set of toolbat to achieve this? You probably want TBSTYLE_AUTOSIZE to do this. Here is a complete example. You probably need to look only at LoadMenu part. It should give you what you want: http://www.microsoft.com/msj/0199/c/c0199.aspx -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Roland" <ajay.sonawane@gmail.com> wrote in message news:69c0226e.0410050313.f223e9@posting.google.com... > Hel...

Problem with adding toolbar items
I'm having an odd problem with the AddCommandToCmdBar function with GP 8.0 SP4 (haven't checked other versions). When I set the CloneCmd parameter to true, and create toolbar items for all users, it works fine. But when I set that parameter to false, it doesn't create toolbar items for any user, including the currently logged in one. When I check the toolbar item table in SQL (SY07125), I see that it added the items, but with a blank USERID. I have used the Login_GetInfo function to confirm that at the time we run our toolbar creation code, there is a logged in user. S...

Adding a button to the toolbar
I'm using Visio 2003 and frequently use the Visio Add-on (through Visio Extras) for numbering my objects. Instead of clicking through 3 submenus, I wish to put the "number shapes" Extra on my toolbar for easy access. Thank you! ...

Macro/Word converting double-quote marks into angle quotation marks
Hi all, I've scripted a vba macro that opens html pages in txt format and adds the following line after <BODY> (through a search and replace): <link rel="stylesheet" type="text/css" href="Output.css" /> The problem I'm having is that Word or the macro (don't really know) is converting the double-quote marks into angled quotation marks (ASCII 147 and 148), which makes the line above unreadable by the browser. I've tried using Chr(34): "<BODY><link rel=" & Chr(34) & "stylesheet" & ...

Default toolbars and toolbar buttons seen instead of customization #2
Hi, I just got excel 2003. After I customize my toolbar close excel and then reopen the default toolbar reappears and my customization is lost. Any one know how to solve it? Hagai ...

Custom Toolbars and Relative Macros
Hi I've made a custom toolbar, EGI, which has two buttons on it. The toolbar is attached to the workbook. However, the workbook is only a template workbook, so many copies of it will be made. When this happens the toolbar buttons relate to the original (template) workbook. How can I make the buttons be 'relative' to the workbooks? Thanks! Assign the macros in the Workbook_Open event Private Sub Workbook_Open() With Application.Commandbars("MyBar") .Controls("Ctl1").OnAction = "myMacro1" .Controls("Ctl2").OnAction...

No CRM toolbar in Appointment windows
Hello, I have a problem. I do not see CRM toolbar in Outlook Appointment window. In other windows everything is OK . CRM addon is working. I use Office 2007 SP2 with CRM 4.0 UR7 and Windows 7. Any suggestions ? B.I. Not sure if its too late for this reply. I had the same problem, and the fix was as simple as this: 1. Go to Outlook Menubar -> CRM -> Options. 2. On the 'Set Personal Options' dialog, go to the Synchronization tab. 3. Check the 'Appointments I'm attending' checkbox and click OK. The CRM toolbar will be available in Appointments from now on...

Toolbars
Out of sudden, my toolbar showing "File,Edit,View,..." goes missing. Tried removing and reinstalling Office but still the same. Action buttons "Send,Delete,Reply,.." still there. But duplication appears when I switch views between folders. Anyone who knows how to solve??.. ...

Reviewing toolbar
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have to do reviewing on many documents. It is extremely annoying that I cannot set the Reviewing Toolbar to always be on. For each document, I have to into the VIew menu and open it. There should be a way to set your default Toolbars to whatever you want! Unfortunately that's how it's designed to work. If you open a file which contains tracked changes or comments the bar will automatically launch, but if neither are present it not only won't launch but will be taken away. There is no Prefer...