Changing button function on the standard toolbar

Dave · Mar 14th, 2002, 06:36 PM

Hello All:
I've made a production sheet for work that has several command buttons (View instructions, print and save, etc.). The problem I'm having is several of my coworkers seem to want to print the data entry sheet from the toolbar rather than use the command button to print the actual production sheet (this button also saves the workbook as their name + the day of the week).

My question:
Is there any way to change the function of the buttons on the standard toolbar (the print and the close window buttons in particular) only when this workbook is opened. I know I can at least remove the print button from the toolbar, but it stays removed when using excel for anything else (this workbook is shared on a network). Any advise would be welcome.

Thanks in advance

Dave

Anne Troy · Mar 14th, 2002, 06:52 PM

Welcome to MrExcel, Dave. You obviously have code that loads when you open the file. And you obviously know how to create code to take that button off. What you want to do in your code is then take your menu off and put that button back on in the Before_Close event.

Mar 15th, 2002, 05:09 AM

Hhhmmmm, hadn't thought of that one. Thanks Dreamboat

Just for kicks though, is there any way to change the function of the button? Would be nice to have the print button open a message box and tell them to use the buttons on the form. I'm hoping that something along this line might make them more aware of the macro buttons.

Thanks Again

Dave

Dave Hawley · Mar 15th, 2002, 05:26 AM

Hi Dave (great name)

try:

Sub DoiT()
Application.CommandBars("Worksheet menu bar").Controls("File").Controls("Print...").OnAction = "myMacro"
End Sub

Sub MyMacro()
MsgBox "I have desingned a button for this, USE IT!!"
End Sub

Sub PutBack()
Application.CommandBars("Worksheet menu bar").Reset
End Sub

BUT a word of warning, unless you use the correct Events (similar to tuggies suggestions) you will not win any friends with the network administrators. You should always return toolbars back to their normal state, not only when you close your Workbook, but also should the user activate another.

At the risk of scaring you off VBA for life I have an example of how this can be done here: http://www.ozgrid.com/VBA/ExcelWorkbookEvents.htm
It's designed for a custom toolbar, but it should get you started.

_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training

[ This Message was edited by: Dave Hawley on 2002-03-15 04:27 ]

Dave · Mar 16th, 2002, 06:28 PM

Wow, another Dave (imagine that

Thanks for the reference to that web page, I'll have to look real close at your code (seems to have lots of potenial for what I'm wanting to do).

Yes, I need to keep Admin happy at all costs!

As for scaring me off VBA, NO WAY! To much fun to play with

Thanks for the help.

Dave

Dave Hawley · Mar 16th, 2002, 06:59 PM

My pleasure Dave (I really like that name).

I have had a change of heart about system administrators, they don't deserve any friends

Dave · Mar 17th, 2002, 07:32 AM

Ok Dave (boy that name just kinda stands out doesn't it):

I saw on your web site the code for extracting data from closed workbooks.

The question:
Can this code extract from more than one workbook? Since my production sheets all save as the operator name+dayoftheweek I would assume (and we know how that works) that I could use the day of the week as a key. I.e.: have one workbook with a command button to extract all the data from mondays (or what ever day of the week) production, compile it into one sheet, and print only one report vs. the 10-15 I get now (major paper and ink savings over time).
Seems to me that this would require a counter to advance the paste area on the open workbook (to avoid overwriting data), and a means to search for all the operator names (yes, i know you dislike loops, gesh I have yet to get one to even run right!). And if that weren't enough, it also needs the ways and means to search the cells on the Production_Sheet page of the closed workbooks to find only the one with values (I think I see an inkling of where to start with this).
Boy, talk aobut thinking yourself around the corner and down the road...kinda makes my brain hurt.

Any thoughts, hints or comments (maybe a good cussing?)

Thanks

Dave
p.s. Should I repost this as a new thread?

Dave Hawley · Mar 23rd, 2002, 07:50 PM

Hi Dave

The link was to actually show you how to hide all commandBars on activating a Workbook containing a Custom Command bar. I see you have focused on the Extract from closed Workbook code!

In regards to overwriting existing data, I cannot see why, just pull the data in on a Hidden sheet then simply cut and paste it to the sheet that it should be on.

Sheets("MyHiddenSheet").UsedRange.Cut Destination:=Sheets("CorrectSheet").Range("A65536").end(xlup).offset(1,0)

For the problem of searching for the needed data, just pull the lot in, then use AutoFilter or Find (or if you must a loop) to get in data you need.

Mar 14th, 2002, 06:52 PM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	Welcome to MrExcel, Dave. You obviously have code that loads when you open the file. And you obviously know how to create code to take that button off. What you want to do in your code is then take your menu off and put that button back on in the Before_Close event. __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 14th, 2002, 06:36 PM	#1
Dave New Member Join Date: Mar 2002 Location: Tennessee Posts: 25	Hello All: I've made a production sheet for work that has several command buttons (View instructions, print and save, etc.). The problem I'm having is several of my coworkers seem to want to print the data entry sheet from the toolbar rather than use the command button to print the actual production sheet (this button also saves the workbook as their name + the day of the week). My question: Is there any way to change the function of the buttons on the standard toolbar (the print and the close window buttons in particular) only when this workbook is opened. I know I can at least remove the print button from the toolbar, but it stays removed when using excel for anything else (this workbook is shared on a network). Any advise would be welcome. Thanks in advance Dave

Mar 15th, 2002, 05:09 AM	#3
Guest Posts: n/a	Hhhmmmm, hadn't thought of that one. Thanks Dreamboat Just for kicks though, is there any way to change the function of the button? Would be nice to have the print button open a message box and tell them to use the buttons on the form. I'm hoping that something along this line might make them more aware of the macro buttons. Thanks Again Dave

Mar 15th, 2002, 05:26 AM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Dave (great name) try: Sub DoiT() Application.CommandBars("Worksheet menu bar").Controls("File").Controls("Print...").OnAction = "myMacro" End Sub Sub MyMacro() MsgBox "I have desingned a button for this, USE IT!!" End Sub Sub PutBack() Application.CommandBars("Worksheet menu bar").Reset End Sub BUT a word of warning, unless you use the correct Events (similar to tuggies suggestions) you will not win any friends with the network administrators. You should always return toolbars back to their normal state, not only when you close your Workbook, but also should the user activate another. At the risk of scaring you off VBA for life I have an example of how this can be done here: http://www.ozgrid.com/VBA/ExcelWorkbookEvents.htm It's designed for a custom toolbar, but it should get you started. _________________ Kind Regards Dave Hawley OzGrid Business Applications Microsoft Excel/VBA Training [ This Message was edited by: Dave Hawley on 2002-03-15 04:27 ]

Mar 16th, 2002, 06:28 PM	#5
Dave New Member Join Date: Mar 2002 Location: Tennessee Posts: 25	Wow, another Dave (imagine that Thanks for the reference to that web page, I'll have to look real close at your code (seems to have lots of potenial for what I'm wanting to do). Yes, I need to keep Admin happy at all costs! As for scaring me off VBA, NO WAY! To much fun to play with Thanks for the help. Dave

Mar 16th, 2002, 06:59 PM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	My pleasure Dave (I really like that name). I have had a change of heart about system administrators, they don't deserve any friends

Mar 17th, 2002, 07:32 AM	#7
Dave New Member Join Date: Mar 2002 Location: Tennessee Posts: 25	Ok Dave (boy that name just kinda stands out doesn't it): I saw on your web site the code for extracting data from closed workbooks. The question: Can this code extract from more than one workbook? Since my production sheets all save as the operator name+dayoftheweek I would assume (and we know how that works) that I could use the day of the week as a key. I.e.: have one workbook with a command button to extract all the data from mondays (or what ever day of the week) production, compile it into one sheet, and print only one report vs. the 10-15 I get now (major paper and ink savings over time). Seems to me that this would require a counter to advance the paste area on the open workbook (to avoid overwriting data), and a means to search for all the operator names (yes, i know you dislike loops, gesh I have yet to get one to even run right!). And if that weren't enough, it also needs the ways and means to search the cells on the Production_Sheet page of the closed workbooks to find only the one with values (I think I see an inkling of where to start with this). Boy, talk aobut thinking yourself around the corner and down the road...kinda makes my brain hurt. Any thoughts, hints or comments (maybe a good cussing?) Thanks Dave p.s. Should I repost this as a new thread?

Mar 23rd, 2002, 07:50 PM	#8
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Dave The link was to actually show you how to hide all commandBars on activating a Workbook containing a Custom Command bar. I see you have focused on the Extract from closed Workbook code! In regards to overwriting existing data, I cannot see why, just pull the data in on a Hidden sheet then simply cut and paste it to the sheet that it should be on. Sheets("MyHiddenSheet").UsedRange.Cut Destination:=Sheets("CorrectSheet").Range("A65536").end(xlup).offset(1,0) For the problem of searching for the needed data, just pull the lot in, then use AutoFilter or Find (or if you must a loop) to get in data you need.