Posts by onowahoo

onowahoo · Jul 27th 2006

is there an appropriate forum?

onowahoo · Jul 21st 2006

Re: Toolbars and Add-ins

Def take a look at addin, usefull for two screens. thanks to the guy from vba tips and tricks
What I wanted to accomplish:

1. Create an add-in with macros.
2. Create a toolbar attached to the add-in and customized.
3. Create a drop-down menu.
4. Have the Drop-down menu have the same ICON's as the toolbar i created.
Most of this was covered in the FAQs.

To add the toolbar to the add-in, i simply made "is addin" property false, attached the toolbar, and then made the workbook an addin.

To get the toolbar to come up when the addin is installed and leave when uninstalled i used:

Code

Private Sub Workbook_addinUninstall()
    Run "DeleteMenu"
        On Error Resume Next
        With Application
           .DisplayFullScreen = False
           .CommandBars("JHarrisPersonal").Visible = False
           .CommandBars("JHarrisPersonal").Enabled = False
        End With
    On Error GoTo 0
End Sub

and vice versa with Private Sub Workbook_addinInstall....

the dropdown menu i created using the code in the FAQ.

The trickier part was getting the dropdown menu to use the customized icons.

The entire code i have in "this workbook is below"

I took the codes i found in the FAQ and added to them to make them fit my needs (dropdown menus with custom icons WAHOOOOO)

simply put is it below

Code

Application.CommandBars("JHarrisPersonal").Controls(1).CopyFace
    With CommandBarControl.Controls.Add(Type:=msoControlButton)
                .Caption = "&VBA Formula"
                .OnAction = "Get_VBA_Formula"
                .PasteFace

I also attached the add-in becasue its pretty useful, and you can get a better feel for the code. It is mostly for handling two monitors.
I took the code from VBA tips and tricks site but updated/improved on it.

on the previous code, when you split a workbook into two screens the inactive sheets would default zoom 100, i changed this so that all the sheets will be the same as the original

take a look and get back if you have any questions comments.

Code

'Private Sub app_Workbookclose()
'    Run "DeleteMenu"
'End Sub
Private Sub Workbook_AddinInstall()
Dim oCommandBarButton As Office.CommandBarControl
Dim oCommandBar As Office.CommandBar
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl




    On Error Resume Next
    With Application
        .DisplayFullScreen = False
        .CommandBars("JHarrisPersonal").Enabled = False
        .CommandBars("JHarrisPersonal").Enabled = True
        .CommandBars("JHarrisPersonal").Visible = True
        Set oCommandBar = .CommandBars("JHarrisPersonal")
        Set oCommandBarButton = oCommandBar.Controls(1)
        oCommandBarButton.OnAction = "Get_VBA_Formula"
        Set oCommandBarButton = oCommandBar.Controls(2)
        oCommandBarButton.OnAction = "Dual_Screen_Full"
        Set oCommandBarButton = oCommandBar.Controls(3)
        oCommandBarButton.OnAction = "Dual_Screen_Split"
        Set oCommandBarButton = oCommandBar.Controls(4)
        oCommandBarButton.OnAction = "Left_Screen"
        Set oCommandBarButton = oCommandBar.Controls(5)
        oCommandBarButton.OnAction = "Right_Screen"
        Set oCommandBarButton = oCommandBar.Controls(7)
        oCommandBarButton.OnAction = "Right_Screen_Full"
        Set oCommandBarButton = oCommandBar.Controls(6)
        oCommandBarButton.OnAction = "Left_Screen_Full"
        On Error GoTo 0
    End With
    On Error GoTo 0


'ADDS THE MENUSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS




On Error Resume Next 'Deletes Menu if already Exists
Application.CommandBars("Worksheet Menu Bar").Controls("Menu &X").Delete
On Error GoTo 0


'(2)Set a CommandBar variable to Worksheet menu bar
 Set cbMainMenuBar = _
     Application.CommandBars("Worksheet Menu Bar")
     
'(3)Return the Index number of the Help menu. We can then use _
 this to place a custom menu before.
     
 'Set a CommandBarControl variable to it
Set cbcCutomMenu = _
     cbMainMenuBar.Controls.Add(Type:=msoControlPopup)
                  
        '(5)Give the control a caption
     cbcCutomMenu.Caption = "Menu &X"
     
        '(6)Working with our new Control, add a sub control and _
        give it a Caption and tell it which macro to run (OnAction).
        
    Application.CommandBars("JHarrisPersonal").Controls(1).CopyFace
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "&VBA Formula"
                .OnAction = "Get_VBA_Formula"
                .PasteFace
    End With
    Application.CommandBars("JHarrisPersonal").Controls(2).CopyFace
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "&Full Double"
                .OnAction = "Dual_Screen_Full"
                .PasteFace
    End With
    Application.CommandBars("JHarrisPersonal").Controls(3).CopyFace
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "&Two Sheets"
                .OnAction = "Dual_Screen_Split"
                .PasteFace
    End With
    Application.CommandBars("JHarrisPersonal").Controls(4).CopyFace
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "L&eft"
                .OnAction = "Left_Screen"
                .PasteFace
    End With
    Application.CommandBars("JHarrisPersonal").Controls(5).CopyFace
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "&Right"
                .OnAction = "Right_Screen"
                .PasteFace
    End With
    Application.CommandBars("JHarrisPersonal").Controls(6).CopyFace
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "&Left Full"
                .OnAction = "Left_Screen_Full"
                .PasteFace
    End With
    Application.CommandBars("JHarrisPersonal").Controls(7).CopyFace
    With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = "Ri&ght Full"
                .OnAction = "Right_Screen_Full"
                .PasteFace
    End With




End Sub

Display More

onowahoo · Jul 20th 2006

Re: Toolbars and Add-ins

i will put solution up, but i'm working on a way to put the icons from my toolbar into the menu bars...
when i do this i'll explain everything in this post

onowahoo · Jul 20th 2006

Re: Toolbars and Add-ins

I apologize for coming off with an attitude.

On a good note, I figured it out.

Thanks and i apologize again

onowahoo · Jul 19th 2006

Re: Toolbars and Add-ins

No it hasn't, this discusses creating menus using VBA, i am interested in attaching my "TOOLBAR"

With my pictures on them.

AND..........

i don't want it to be attached to a workbook, i would like toolbars to attached to an add-in.

onowahoo · Jul 19th 2006

Re: DO NOT PUSH THE BIG RED BUTTON!

is there anything else connected to this site besides this page?

what did i destroy (temporarily mind you)

onowahoo · Jul 19th 2006

I created a custom toolbar in excel. by right clicking on the toolbar, creating new, adding a couple buttons and assigning them to a couple macros.

I'd like to send this to people, how do i do this.
Obviously, the toobars's would have to reference the macros, these i have in an add-in.

Is there anyway that i could attach the toolbar's to the add-in so i could just send the add-in.

I also created a menu, is there anyway that i can put this into the Add-in also?

Where does the toolbar i created and the menu that i created save down. If i open a new excel without opening personal.xls, the toolbars will still be there (open one excel, then open another and it tells me that my personal.xls is in use by jason harris, i hit cancel so it opens without my personal workbook)

Lastly, if I open excel, then go into VB, I can save my personal workbook. How do I save this as or save a copy?