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?

 

[slimimi]

Hi dominic, i got this code to populate a listbox on a userform which is activated by My Macro 1 (which you provided me with) - remember - the other macro i mentioned to you earlier (the get Chart Pictures) button.

What happens is that an autoform is fired and the user is told that they are about to insert Chart X and Y as a hyperlink into their Trade Log.

I used 2 listboxes for Chart X and Chart Y and wanted those listboxes to be populated by using the following code.

userform1.listbox1.value = sheets("Sheet1").range("B" & activecell.row).value

The problem is that i really dont know where to paste this. I have tried pasting it as follows: but i am getting Runtime error 9. Subscript out of range. This is where i pasted it :

Sub MyMacro1()
frmChartPics.ListBox1.Value = Sheets("Sheet1").Range("B" & ActiveCell.Row).Value
frmChartPics.Show
End Sub

Can you just tell me if i have put it in the right place or do i need to paste it elsewhere. Thanks in advance. Hope i haven't interrupted dinner

 

[DominicB]

Hi slimimi

Never been to Brunei but I've got a rough idea where it is and I know it's about 7/8 hrs ahead of GMT.

All the code I gave you before needs to go in your userform - just right click a blank area of your form and select View Code. However the code in the sub called SaveAndClose needs to go into it's button click event (without the Sub ... End Sub bits). To open the button click event, double click on the button in your userform design environment.

To add items to a listbox, you need to use something like this :

Private Sub UserForm_Initialize()
With ListBox1
.AddItem Sheets("Sheet1").Range("B" & ActiveCell.Row).Value
End With
End Sub

As you can see, this code is in the UserForm_Initialize sub, which means it is run when the form is opened. If you like you can attach it to any other event, such as a button click, but you would normally want it to populate when the form opens.

HTH

DominicB

 

[slimimi]

Hi dominic. I don't actually have a userform initialize procedure so far. Should i create one? and where would i create it?

All i have is the code you gave me earlier which creates a command bar with 2 command bar buttons with MyMacro1 and MyMacro2 assigned to it. I changed the MyMacro1 and MyMacro2 to MacroGetChartPics and MacroSaveTradeLog.

You created a With Btn and an On Action command.
Then you created 2 subs. I have just put the code frmChartPics.show under the 1st sub (which i have now renamed to SubMacroGetChartPics.

So i am not really sure where to initialize this userform.
This is the entire code i have for the module - if it makes things easier:

Sub BuildTB()
Dim cb As CommandBar, ctrl As CommandBarControl
Name1 = "Trade Log"
On Error Resume Next
Application.CommandBars(Name1).Delete
Set cb = Application.CommandBars.Add(Name:=Name1, temporary:=True)
With cb
.Visible = True
.Position = msoBarRight
.Top = 1000
.Protection = msoBarNoMove
End With
Application.CommandBars(Name1).Controls(1).ListIndex = 6
Set Btn = cb.Controls.Add(Type:=msoControlButton)
With Btn
.FaceId = 433
.OnAction = "MacroGetChartPics"
.Caption = "Get CHART PICTURES"
.Style = msoButtonIconAndCaption
End With
Set Btn = cb.Controls.Add(Type:=msoControlButton)
With Btn
.FaceId = 3
.OnAction = "MacroSaveTradeLog"
.Caption = "Save TRADE LOG"
.Style = msoButtonIconAndCaption
End With
End Sub
Sub MacroGetChartPics()
frmChartPics.Show
End Sub
Sub MacroSaveTradeLog()
frmSAVElog.Show
End Sub
 
Sub DeleteTB()
On Error Resume Next
Application.CommandBars("Trade Log").Delete
On Error GoTo 0
End Sub

 

[DominicB]

Hi slimimi

OK. The code that creates the toolbar needs to go in a normal module.

The code that handles the toolbar when the workbook is opened / closed or loses focus and intercepts the calls to save / close the workbook must all go in the ThisWorkbook module.

Any code that has anything to do with a UserForm (except opening the userform) must go in the useforms own module. In this module there will be two dropdowns at the top of the screen. The left hand one will list all the objects in your form. The right hand one will list all the events that can be attached to any of these objects.

Click the left hand one and select "CommandButton1", or whatever your buttons are called. From the right hand one select "Click", and you'll notice some code appear in your module. Something like :

Private Sub CommandButton1_Click()
 
End Sub

depending on what your buttons called. Anything you put between these two lines will be executed when you click the button. Stick this betweeen the two lines and press F5 to run the form :

MsgBox "Hello world"

See what I mean?

Now select "UserForm" (or whatever you have called your form) from the left hand box, and "Initialize" from the right hand box, and something like this should appear :

Private Sub UserForm_Initialize()
 
End Sub

Anything you put in here will execute as soon as the form opens. Try putting this line in there :

MsgBox "Form just opened"

Not all objects are going to need events setting up, you just need to decide what happens with your controls and when.

HTH

DominicB

Edit : There's a good tutorial here for building a userform.

 

[slimimi]

Thats a great explanation thank you - i have tried all that out and i feel enlightened.
However, when i left click the Lefthand dropdown - my frmChartPics does not appear. I only have the option to select from the other controls and Userform but not "frmChartPics". I went ahead and selected Userform and then initialize. Pasted the code in there and it looked like this:

Private Sub UserForm_Initialize()
With ListBox1
.AddItem Sheets("Sheet1").Range("B" & ActiveCell.Row).Value
End With
End Sub

but when i run it i am getting the following message still :

Run-Time error 9 - Subscript out of range

I guess i must be missing something crucial but i just dont see it ((

 

[DominicB]

Hi slimimi

but when i run it i am getting the following message still :

Run-Time error 9 - Subscript out of range

I guess i must be missing something crucial but i just dont see it

You would get this message if "Sheet1" didn't exist. You do have a "Sheet1" don't you?

HTH

DominicB

 

[slimimi]

Hi dominic - you are probably gonna not want to hear from me after this (smiles)

With the userform in question (frmGetChartPics)
Is it possible to only open form (upon macro2button being clicked) IF there is an entry in Sheet1 : Range B & active cell row? IF no entry - msgbox"I can't look for Chart Pics unless you enter a Ticket/Trade Number" ?

Also - i basically have 2 checkboxes on this form telling users what they need to have done before clicking the OK button to get Chart Pictures.
Is it possible to make the command OK button look to see if Both Checkboxes have been checked before proceeding? If not - msgbox"Please complete the above steps before Clicking OK"?

Please just let me know if you need me to stop asking all these questions. I feel real bad now and i am sure you have much more better things to do with your time too...

 

[slimimi]

I do have a sheet 1.
This is whats listed in the VBAproject window exactly as it appears:

Microsoft Excel Object
Chart3 (Visual Summary)
Sheet1 (MiniForex)
Sheet2 (Trade Summary)
Sheet3 (Enter Details)
Thisworkbook

then underneath that is FORMS and then underneath that is MODULES

 

[slimimi]

Hi Dominic - i got it. I needed to rename the Sheet1 to "Mini Forex" which i have done now and it works perfectly. Thank you. The fontsize appears quite small in the box though is there a code to change font size? could i simply type something like .font=x??? after the .additems?

 

[DominicB]

Hi slimimi

I'll come to your other questions a bit later. For the font one - quick answer. Click the listbox, then look for Font in the properties window on the left hand side of the screen (if it's not open, go to View > Properties Window). Set it to whatever you like

HTH

DominicB