Solved: creating a custom toolbar in VBA

[SUJU]

Hi
("Still trying to figure out this minefield that is VBA...")

I am trying to create a custom toolbar "Trading." The toolbar should have a drop down control with options such as "Bull", "Bear", and "Neutral". This value will then be added into my active cell in the worksheet. The toolbar must be usable with other spreadsheets later.

If the word Bull is clicked, this text "Bull" should appear in green (4), "bear" in red (3) and "Neutral" in (46).

I am sure its easy and has been done....any ideas please!!

Thanks
Suju

[Bob Phillips]

Add this code to the ThisWorkbook code module


Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Trading").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Trading").Delete
On Error GoTo 0
With Application.CommandBars.Add(Name:="Trading", temporary:=True)
With Application.CommandBars("Trading").Controls.Add( _
Type:=msoControlDropdown)
.Caption = "Market Type"
.BeginGroup = True
.OnAction = "MarketType"
.AddItem "Bull"
.AddItem "Bear"
.AddItem "Neutral"
.ListIndex = 1
End With
.Visible = True
End With
End Sub


and then add this to a standard code module


Sub MarketType()
With Application.CommandBars.ActionControl
ActiveCell.Value = .List(.ListIndex)
Select Case .List(.ListIndex)
Case "Bull": ActiveCell.Font.ColorIndex = 4
Case "Bear": ActiveCell.Font.ColorIndex = 3
Case Else: ActiveCell.Font.ColorIndex = 46
End Select
End With
End Sub

[SUJU]

Thanks XLD

Works like a charm

[onlyadrafter]

Hello,

Try the following,

VIEW --> TOOLBARS --> CUSTOMIZE, select the CUSTOMIZE tab, and press NEW, name it as required.

Drag the toolbar into the toolbar area, from the CUSTOMIZE window, select the COMMANDS tab, scroll down to NEW-MENU, drag into the new toolbar. Right click on the word NEW_MENU and rename as required.

Click on the down arrow in the new toolbar from the CUSTOMIZE window, in the COMMANDS tab, select FILE, drag CLOSE into the new toolbar. Right click on these and rename. Repeat this for all three.

In VB in your personal file add a module and insert the following code

Sub bear()
If Selection.Cells.Count > 1 Then
End
Else
ActiveCell.Value = "Bear"
ActiveCell.Font.ColorIndex = 3
End If
End Sub
Sub bull()
If Selection.Cells.Count > 1 Then
End
Else
ActiveCell.Value = "Bull"
ActiveCell.Font.ColorIndex = 4
End If
End Sub
Sub NEUTRAL()
If Selection.Cells.Count > 1 Then
End
Else
ActiveCell.Value = "Neutral"
ActiveCell.Font.ColorIndex = 0
End If
End Sub

In Excel, select VIEW, TOOLBARS, CUSTOMIZE, click the down arrow on the toolbar, rightclick over one of the drop downs, and select ASSIGN MACRO, and find the appropriate macro in the personal book. Repeat for all 3.

Is this OK? understandable?