Code to position toolbar PLEASE help

[slimimi]

Hi there - using excel 2003.

I have the following code to display a toolbar everytime my template is open

'Show Custom Toolbar "Trade Log" on Workbook Open
On Error Resume Next
With Application.CommandBars("Trade Log")
.Position = msoBarFloating
.Left = 5000
.Top = 600
.Visible = True
End With
End Sub

(hope i wrapped the code quotes correctly this time ))))

I noticed that when you drag a custom toolbar to the far right of your screen - it locks in to a vertical toolbar space.

How can i position my toolbar in this place automatically by modifying the above code please?

 

[DominicB]

Good afternoon slimimi

(hope i wrapped the code quotes correctly this time )

'Fraid not. Keep trying!

Try this modification instead :

'Show Custom Toolbar "Trade Log" on Workbook Open
On Error Resume Next
With Application.CommandBars("Trade Log")
.Position = msoBarRight
'.Left = 5000
'.Top = 600
.Visible = True
End With
End Sub

HTH

DominicB

 

[slimimi]

Thanks dominic - i have done that and it works great.
I can delete the Left = 5000 and Top = 600 lines now i guess.

I am having problems trying to Change the Button Size for my custom toolbar and also to enlarge the font for the button names. Would you happen to know how?

 

[DominicB]

Hi slimimi

You can indeed delete the superfluous lines.

As for the font and icon size, I'm afraid you can't really. The only way to control icon size is to go to Tools > Customize, Options and tick the Large Icons box.

There are however a number of styles that can be assigned to a button. This must be done on a button by button basis and goes in the same With .. End With construct that .FaceID, and .OnAction sit in. Use .Style (for some reason intellisense doesn't seem to know about this property - but, believe me, it's there) and assign one of these values to the property :
msoButtonAutomatic
msoButtonCaption
msoButtonIcon
msoButtonIconAndCaption
msoButtonIconAndCaptionBelow
msoButtonIconAndWrapCaption
msoButtonIconAndWrapCaptionBelow
msoButtonWrapCaption

That's about the best you can do I'm afraid.

HTH

DominicB

 

[slimimi]

Thank you dominic - really appreciate your help.
I have 2 command bar buttons now in this custom toolbar.

1st is called Get Chart Pictures (which i am really stuck on getting to work - never mind - thats on a "IF you have the time to take a look basis" ))) Let me know if you can so i can post it (have been stuck on it for 3 days now - sigh (((

2nd BUTTON is called Save Trade Log...

I have created a userform which i would like to open when a user clicks on this 2nd button. However - i cant, for the life of me, figure out how to assign the code to it.
I have tried right clicking on the button and selecting assign macro - but - i am not seeing anything in the list that relates to the new userform i have created.

Is there any chance you could give me a hand on this please? I know its probably really simply. I'll save the hard question for after (if you dont mind)... he he...
Sorry - i am being real cheeky here )

Thanks again for your help..

 

[DominicB]

Hi slimimi

Sorry - i am being real cheeky here )

You're not really being cheeky - that's the whole idea of these forums. You post a question that you don't know the answer to and someone who does know the answer helps out. Cool

To assign a macro to a toolbar built "on-the -fly" you need to use code. Have a look at the code below. This will build a simple toolbar with 2 buttons that docks on the right - the BuildTB macro does the meaty bit. The command that runs the macro when the button is clicked is the ".OnAction" property. Just put your code in there to open the userform and it will fire when the button is clicked.

Sub BuildTB()
Dim cb As CommandBar, ctrl As CommandBarControl
Name1 = "My Toolbar"
On Error Resume Next
Application.CommandBars(Name1).Delete
Set cb = Application.CommandBars.Add(Name:=Name1, temporary:=True)
With cb
.Visible = True
.Position = msoBarRight
End With
Application.CommandBars(Name1).Controls(1).ListIndex = 6
Set Btn = cb.Controls.Add(Type:=msoControlButton)
With Btn
.FaceId = 263
.OnAction = "MyMacro1"
End With
Set Btn = cb.Controls.Add(Type:=msoControlButton)
With Btn
.FaceId = 42
.OnAction = "MyMacro2"
End With
End Sub
Sub MyMacro1()
MsgBox "This is MyMacro1"
'UserForm1.Show
End Sub
Sub MyMacro2()
MsgBox "This is MyMacro2"
'UserForm2.Show
End Sub

Not sure what you're other problem is (or whether I'll be able to help) but stick it on the board if you like - it may or may not be me who answers - but hopefully someone will be know the answer and be able to help you out.

 

[slimimi]

Hi dominic - thank you for all your help.

where do i paste this code for this?

How about if i want to use the already created custom toolbar that i have? Is it possible to fire up my userform from that?

Thanks again - sorry if i have misunderstood anything.

 

[DominicB]

Hi slimimi

Open a new workbook, and then open the VBE (Alt + F11) and go to Insert > Module. Paste the code into the window that opens and then got to File > Close and Return to Microsoft Excel.

Then from Excel go to Tools > Macro > Macros, select BuildTB and click Run. That should open the toolbar.

If you're still struggling. then post the entire code you have so far, and I will make the adjustments for you.

HTH

DominicB

 

[slimimi]

Hi dominic. I think i can see now whats happening by looking at your code and pasting into new workbook. Just a bit of fine tweaking if you dont mind helping me on this please.

Your code works perfectly so i think i will just insert that as a module into my template.

How can i incorporate in the code to make these custom button names appear?
And also - i plan to use 2 images for the 2 custom buttons (providing that these will get embedded in the template so that any user on any local computer can see the images. How can i incorporate that into the code too please?

 

[slimimi]

Can you also help me to make a slight adjustment to your code please so that when Template closes or is deactivated - the toolbar closes. Alternatively the toolbar only shows when you open Mytemplate and when it is active.

Sorry to be a pain ))