Creating a command button on the Excel tool bar to run a macro 2

[VYM]

Hi,

I have a hopefully quick question...this is similar to what engineer2100 asked but different....

I want to create a command button in an excel workbook, but place it on the title bar(that is where you have all the shortcuts to print, open a new sheet etc.) - if possible.

I want the user to be able to click the command button and it will run the macro I created on that sheet. So basically instead of the user going Tools -> Macros -> run Macro ... they click that button and it does those steps.

I hope I am being clear enough.

PHV... it seems like you know alot about excel and all this VB stuff which I have never done before ...so maybe you can help?

Thanks in advance!

-V

 

[PHV]

VYM, it's unfair for all the other members to ask someone personally.

 

[ETID]

Tools>Customise...then from the commands tab look for macro in the list, then drag the smiley up into the menu bar that you desire.


when it's there, click it and assign a macro to it.

 

[phil009]

ETID-I may not have asked the question, but your answer was very helpful to me.

Thanks,
Phil

 

[ETID]

Thanks for the nod...

 

[Loomah]

Hi
Just for the hell of it, here's how you could create a custom button and add it to the end of the standard toolbar on the fly. Useful if the button is only required for a specific workbook as you can delete it after use.

Sub AddButton()
'use Workbook_Open() event
Dim myCont As CommandBarControl

With CommandBars("Standard")
    'make sure the button doesn't already exist
    If Not .Controls(.Controls.Count).Caption = "myButton" Then
        'create the button
        Set myCont = .Controls.Add(Type:=msoControlButton, _
            before:=.Controls.Count + 1)
        'set it's properties etc
        With myCont
            .FaceId = 38    'up arrow
            .TooltipText = "Scroll Cell to Top Left"
            .Caption = "myButton"
            .OnAction = "movecelltotop"
        End With
    End If
End With
End Sub

Sub DeleteButton()
'use Workbook_BeforeClose(Cancel As Boolean) event
On Error Resume Next
    CommandBars("Standard").Controls("mybutton").Delete
End Sub

Sub MoveCellToTop()
'code that runs when new custom button is clicked
With ActiveWindow
    .ScrollColumn = ActiveCell.Column
    .ScrollRow = ActiveCell.Row
End With
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

 

[VYM]

Hey Thanks to all for the replys... I found out about the smiley control in the commands or the forms tab... but all your quick responses are REALLY appreciated...

PHV...*WOW* sorry if I offended you...(my fault) I had seen a previous thread on a topic similar to mine and your responses to that ...so I figured that you may be able to answer quicker....

I hope I didn't offend anyone else.

-V