- Joined
- Jul 25, 2007
- Messages
- 26
- Reaction score
- 0
Hopefully to John Green and others
Following is John Greens Code for making a commandbar with a couple of bits I put in. Works fantastic in my Workbook titled 'VB code Etc'. But when I copy and paste it into another workbook and run it I get 'User defined type not defined'. The macro stops at 'Dim cb As CommandBar'. I have tried new modules, Dim this, Dim that, I've spent days trying to make it work on a different workbook other than 'VB code Etc'. It is difinately beyond my limited knowledge. Thanks Geoffrey
Sub ToolBarMake1()
Dim cbList As CommandBar
Dim lngLeft As Long
Dim lngTop As Long
Dim lngPosition As Long
Dim i As Integer
On Error Resume Next
''Name on toolbar = ActiveWorkbook.Name or "AnyName" in quotation marks
Set cbList = Application.CommandBars(ActiveWorkbook.Name)
On Error GoTo 0
If cbList Is Nothing Then
Set cbList = Application.CommandBars.Add(Name:=ActiveWorkbook.Name)
''The next line denotes how many buttons or images on toolbar
For i = 1 To 22
cbList.Controls.Add Type:=msoControlButton
Next i
End If
With cbList.Controls(1)
''OnAction = the macro assigned to button
.OnAction = "ImportOpeningPositions"
''FaceId = the number of the image
.FaceId = 270
''TooltipText = what text appears when cursor travels over button or image
.TooltipText = "Match 1"
End With
With cbList.Controls(2)
.OnAction = "TradesReport"
.FaceId = 195
.TooltipText = "Match 2"
End With
With cbList.Controls(3)
.OnAction = "Icon_MessageBoxAAB"
.FaceId = 2
.TooltipText = "Match 3"
End With
With cbList.Controls(4)
.OnAction = "blah blah"
.FaceId = 4
.TooltipText = "Match 4"
End With
With cbList.Controls(5)
.OnAction = "blah blah"
.FaceId = 5
.TooltipText = "Match 5"
End With
With cbList.Controls(6)
.OnAction = "blah blah"
.FaceId = 6
.TooltipText = "Match 6"
End With
With cbList.Controls(7)
.OnAction = "blah blah"
.FaceId = 71
.TooltipText = "Match 7"
End With
With cbList.Controls(8)
.OnAction = "blah blah"
.FaceId = 72
.TooltipText = "Match 8"
End With
With cbList.Controls(9)
.OnAction = "blah blah"
.FaceId = 73
.TooltipText = "Match 9"
End With
With cbList.Controls(10)
.OnAction = "blah blah"
.FaceId = 74
.TooltipText = "Match 10"
End With
With cbList.Controls(11)
.OnAction = "blah blah"
.FaceId = 75
.TooltipText = "Match 11"
End With
With cbList.Controls(12)
.OnAction = "blah blah"
.FaceId = 76
.TooltipText = "Match 12"
End With
With cbList.Controls(13)
.OnAction = "blah blah"
.FaceId = 77
.TooltipText = "Match 13"
End With
With cbList.Controls(14)
.OnAction = "blah blah"
.FaceId = 78
.TooltipText = "Match 14"
End With
With cbList.Controls(15)
.OnAction = "blah blah"
.FaceId = 79
.TooltipText = "Match 15"
End With
With cbList.Controls(16)
.OnAction = "blah blah"
.FaceId = 1820
.TooltipText = "Match 16"
End With
With cbList.Controls(17)
.OnAction = "blah blah"
.FaceId = 1820
.TooltipText = "Match 17"
End With
With cbList.Controls(18)
.OnAction = "blah blah"
.FaceId = 1820
.TooltipText = "Match 18"
End With
With cbList.Controls(19)
.OnAction = "blah blah"
.FaceId = 2
.TooltipText = "Match 19"
End With
With cbList.Controls(20)
.OnAction = "blah blah"
.FaceId = 2
.TooltipText = "Match 20"
End With
With cbList.Controls(21)
.OnAction = "blah blah"
.FaceId = 2
.TooltipText = "Match 21"
End With
With cbList.Controls(22)
.OnAction = "blah blah"
.FaceId = 2
.TooltipText = "Match 22"
End With
cbList.Enabled = True
cbList.Visible = True
With cbList
.Width = 120
End With
End Sub
Following is John Greens Code for making a commandbar with a couple of bits I put in. Works fantastic in my Workbook titled 'VB code Etc'. But when I copy and paste it into another workbook and run it I get 'User defined type not defined'. The macro stops at 'Dim cb As CommandBar'. I have tried new modules, Dim this, Dim that, I've spent days trying to make it work on a different workbook other than 'VB code Etc'. It is difinately beyond my limited knowledge. Thanks Geoffrey
Sub ToolBarMake1()
Dim cbList As CommandBar
Dim lngLeft As Long
Dim lngTop As Long
Dim lngPosition As Long
Dim i As Integer
On Error Resume Next
''Name on toolbar = ActiveWorkbook.Name or "AnyName" in quotation marks
Set cbList = Application.CommandBars(ActiveWorkbook.Name)
On Error GoTo 0
If cbList Is Nothing Then
Set cbList = Application.CommandBars.Add(Name:=ActiveWorkbook.Name)
''The next line denotes how many buttons or images on toolbar
For i = 1 To 22
cbList.Controls.Add Type:=msoControlButton
Next i
End If
With cbList.Controls(1)
''OnAction = the macro assigned to button
.OnAction = "ImportOpeningPositions"
''FaceId = the number of the image
.FaceId = 270
''TooltipText = what text appears when cursor travels over button or image
.TooltipText = "Match 1"
End With
With cbList.Controls(2)
.OnAction = "TradesReport"
.FaceId = 195
.TooltipText = "Match 2"
End With
With cbList.Controls(3)
.OnAction = "Icon_MessageBoxAAB"
.FaceId = 2
.TooltipText = "Match 3"
End With
With cbList.Controls(4)
.OnAction = "blah blah"
.FaceId = 4
.TooltipText = "Match 4"
End With
With cbList.Controls(5)
.OnAction = "blah blah"
.FaceId = 5
.TooltipText = "Match 5"
End With
With cbList.Controls(6)
.OnAction = "blah blah"
.FaceId = 6
.TooltipText = "Match 6"
End With
With cbList.Controls(7)
.OnAction = "blah blah"
.FaceId = 71
.TooltipText = "Match 7"
End With
With cbList.Controls(8)
.OnAction = "blah blah"
.FaceId = 72
.TooltipText = "Match 8"
End With
With cbList.Controls(9)
.OnAction = "blah blah"
.FaceId = 73
.TooltipText = "Match 9"
End With
With cbList.Controls(10)
.OnAction = "blah blah"
.FaceId = 74
.TooltipText = "Match 10"
End With
With cbList.Controls(11)
.OnAction = "blah blah"
.FaceId = 75
.TooltipText = "Match 11"
End With
With cbList.Controls(12)
.OnAction = "blah blah"
.FaceId = 76
.TooltipText = "Match 12"
End With
With cbList.Controls(13)
.OnAction = "blah blah"
.FaceId = 77
.TooltipText = "Match 13"
End With
With cbList.Controls(14)
.OnAction = "blah blah"
.FaceId = 78
.TooltipText = "Match 14"
End With
With cbList.Controls(15)
.OnAction = "blah blah"
.FaceId = 79
.TooltipText = "Match 15"
End With
With cbList.Controls(16)
.OnAction = "blah blah"
.FaceId = 1820
.TooltipText = "Match 16"
End With
With cbList.Controls(17)
.OnAction = "blah blah"
.FaceId = 1820
.TooltipText = "Match 17"
End With
With cbList.Controls(18)
.OnAction = "blah blah"
.FaceId = 1820
.TooltipText = "Match 18"
End With
With cbList.Controls(19)
.OnAction = "blah blah"
.FaceId = 2
.TooltipText = "Match 19"
End With
With cbList.Controls(20)
.OnAction = "blah blah"
.FaceId = 2
.TooltipText = "Match 20"
End With
With cbList.Controls(21)
.OnAction = "blah blah"
.FaceId = 2
.TooltipText = "Match 21"
End With
With cbList.Controls(22)
.OnAction = "blah blah"
.FaceId = 2
.TooltipText = "Match 22"
End With
cbList.Enabled = True
cbList.Visible = True
With cbList
.Width = 120
End With
End Sub