How do I assign a macro to a toolbar button in code? 2

[qjd2004]

Hi all,

I'v got some code that adds a toolbar button to a worksheet.

How do I assign a Sub/macro to this button in the code? Also, I'm currently opening this macro every time the workbook opens, but is it possible to run it only the 1st time the workbook opens?
Here's the macro

Option Explicit
Sub AddToolBarButton()
' Add A Tool Bar Button

    Application.CommandBars("Reviewing").Controls.Add Type:=msoControlButton, ID:=2950, Before:=12

End Sub

And here's the code on the worksheet itself

Private Sub Workbook_Open()
Worksheets(3).Activate
    Call StartupNote
    Call AddToolBarButton
End Sub

Can someone help me?

 

[Loomah]

Hi
This is an example of how to add the macro you want to your button.

Sub AddCommands()
Dim myCont
    Set myCont = CommandBars("Reviewing").Controls.Add(Type:=msoControlButton, ID:=2950) ', Before:=12)
        With myCont
            .OnAction = "Loom"
            ' other button properties
        End With
End Sub

Sub Loom()
MsgBox "l;jkahsdgfigyw"
End Sub

As for adding and removing command bars etc on opening and closing a file here is a sample of code. What it does is add a menu bar and change the way XL looks when the file is opened and resets it when it's closed. I've posted "as is", without customising for your specific needs. I'll send you a copy of the file if you post your email address.

In the WORKBOOK module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ExitView
End Sub

Private Sub Workbook_Open()
    InitView
End Sub

In an ordinary module

Sub InitView()
    SetWindow xlOn
    SetMenu
End Sub

Sub ExitView()
    SetWindow xlOff
    ZapMenu
End Sub

Sub SetMenu()
    Dim myBar As CommandBar
    Dim myButton As CommandBarButton
    ZapMenu
    Set myBar = CommandBars.Add(Name:="OCM", _
                Position:=msoBarTop, _
                MenuBar:=True)
    Set myButton = myBar.Controls.Add(msoControlButton)
        myButton.Style = msoButtonCaption
        myButton.Caption = "E&xit"
        myButton.OnAction = "ExitOCM"
        myBar.Protection = msoBarNoMove + msoBarNoCustomize
        myBar.Visible = True
End Sub

Sub ZapMenu()
    On Error Resume Next
        CommandBars("OCM").Delete
End Sub

Sub SetWindow(State)
    Static myOldState
    Application.ScreenUpdating = False
    On Error Resume Next
        If State = xlOn Then
            Application.DisplayFormulaBar = False
            Application.DisplayStatusBar = False
        'CHANGED TO SPECIFY XLOFF
        ElseIf State = xlOff Then
            Application.DisplayFormulaBar = True
            Application.DisplayStatusBar = True
        End If
End Sub

Sub ExitOCM()
    ' added lines to restore formula & status bars
    Application.DisplayFormulaBar = True
    Application.DisplayStatusBar = True
    ActiveWorkbook.Close (False)
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[qjd2004]

Hi Loomah,

That's fantastic, just what I needed! I'll be able to customise it to my needs very easily!

Thanks for your help! A brilliant post!