MrExcel.com - Your One Stop for Excel Tips & Solutions

2 marcos into one

Posted by Thomas Venn on January 18, 2000 1:47 PM

Hi all, I am currently using 2 macro buttons to do 2 different things.
Is there any way for me to combine this into one macro button?
kind of like the "full screen" button, where i press it once, it goes
into full screen, then i press it again, it goes back to normal screen.

Sub Tool_Bars_unhide()
Application.CommandBars("Custom_Tool_bar_1").Visible = True
Application.CommandBars("Custom_Tool_bar_3").Visible = True
End Sub


Sub Tool_Bars_hide()
Application.CommandBars("Custom_Tool_bar_1").Visible = False
Application.CommandBars("Custom_Tool_bar_3").Visible = False
End Sub

Thanks

Posted by Tom Morales on January 18, 2000 3:33 PM

I think what you need are a series of conditional statements in your macro, such as:
If Application.CommandBars _("Custom_Tool_bar_1").Visible = True Then _
Application.CommandBars("Custom_Tool_bar_1").Visible = False
' and so on.


Tom


Posted by FrankC on January 18, 2000 8:32 PM

simple:

when you perform the first macro, put a value of 1
in some cell that you are not using, like cell(111,111).
If you are worried that users might see this value, make it white
on white.
When you run the macro the second time, put a value of 0 in that cell.


So when you run the dual-function macro, cell the value of that cell. If its 0 then
you have to do the first macro, if its 1 run the other macro.

Posted by Chris on January 19, 2000 5:29 AM

I usually have the macro change the text of the macro button. In your case, you could have the text change between "Unhide Toolbars" and "Hide Toolbars." Then in the procedure you could check the text of the button with an if statement to determine what to do.

This makes the code easy, and it makes things more clear for the user.

HTH,
Chris

Posted by Thomas Venn on January 19, 2000 9:51 AM

I appreciate everyone's effort. However, I have
no idea on how to write VB. I just point and click,
and record the macro.

Can anyone help me write this one?

Regards,

Thomas


Posted by Tom Morales on January 20, 2000 6:27 AM

Tom - You're on the right track. Record the changes you want to make (including the button caption change suggested by Chris. If you want to dazzle your friends, you can even change the color for each caption) Next, read in the on-line help for VB Excel "If - then - Else" statements. It will have examples that will let you put your recorded statements into a conditional context.

You've encountered what teachers call "a learning moment". Enjoy it.
Tom

Posted by Thomas Venn on January 24, 2000 5:21 PM

Hi Tom,

Thank you for your advice, but my head is spinning, as i cannot seem to figure this out. I you know what i need, i would really appreciate any further information.

Cheers,

Thomas


Posted by Celia on January 25, 2000 12:28 AM

Thomas
I'm not sure I understand exactly what you need but if you want to combine the two macros you provided into one which toggles between hiding and unhiding the two "Custom_Tool_bars", then the following would do it (although it is not very elegant).
Celia

Sub ToggleToolBars()
Dim toolBar1
Dim toolBar2
toolBar1 = Application.CommandBars("Custom_Tool_bar_1").Visible = True
Application.CommandBars("Custom 1").Visible = True = Not toolBar1
toolBar2 = Application.CommandBars("Custom_Tool_bar_2").Visible = True
Application.CommandBars("Custom 2").Visible = True = Not toolBar1
End Sub


Posted by Tom Morales on January 25, 2000 11:51 AM

Tom - I believe this is what you're trying to do:
Sub toolchange()
If Application.CommandBars("Custom_Tool_bar_1").Visible = True Then
Application.CommandBars("Custom_Tool_bar_1").Visible = False
Application.CommandBars("Custom_Tool_bar_3").Visible = False
Else
Application.CommandBars("Custom_Tool_bar_1").Visible = True
Application.CommandBars("Custom_Tool_bar_3").Visible = True
End If
End Sub
Give it a shot, and see if it works.
Tom