ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Subscript out of range error? (https://www.excelbanter.com/excel-programming/426572-subscript-out-range-error.html)

salgud
Subscript out of range error?
 
I'm not too familar with this error, and new to working with toolbars in
VBA, so I'm lost on even what to look for on this error. The code so far
is:

Option Explicit
Public Const runCreatetribalTR As String = "RunCreateTribalTR"

Sub Auto_Open()
Call CreateMenubar
With CommandBars("CreateTR")
.Enabled = False
.Visible = False
End With

End Sub

Sub Auto_Close()
Call RemoveMenubar
End Sub

Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(runCreatetribalTR).Delete
On Error GoTo 0
End Sub

Sub CreateMenubar()

Call RemoveMenubar

With Application.CommandBars.Add
.Name = runCreatetribalTR
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarTop
.Left = CommandBars("RunCreateTribalTR").Left +
CommandBars("RunCreateTribalTR").Width
.RowIndex = CommandBars("RunCreateTribalTR").RowIndex

With
CommandBars("RunCreateTribalTR").Controls.Add(Type :=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "CreateTR"
.Caption = "CreateTR"
.Style = msoButtonCaption
.TooltipText = "Create TR"
End With

' With
CommandBars("RunCreateTribalTR").Controls.Add(Type :=msoControlButton)
' .OnAction = "'" & ThisWorkbook.Name & "'!" & "MergeCells"
' .Caption = "Merge Cells"
' .Style = msoButtonCaption
' .TooltipText = "Merge/Unmerge Cells"
' .BeginGroup = True
' End With
With
CommandBars("RunCreateTribalTR").Controls.Add(Type :=msoControlButton, ID _
:=2950, Befo=3) <----SUBSCRIPT OUT OF RANGE
End With
End With
End Sub


Public Sub CreateTR()
Call CreateTribalSheet
End Sub

I've marked the error line above.
Can anyone please help me here?
Thanks!

Per Jessen
Subscript out of range error?
 
As you have commented out the Merge/UnMerge control there is only one
control in the new CommandBar.

Change the problem line from :
...Befo=3
to
...Befo=2

Regads,
Per

"salgud" skrev i meddelelsen
...
I'm not too familar with this error, and new to working with toolbars in
VBA, so I'm lost on even what to look for on this error. The code so far
is:

Option Explicit
Public Const runCreatetribalTR As String = "RunCreateTribalTR"

Sub Auto_Open()
Call CreateMenubar
With CommandBars("CreateTR")
.Enabled = False
.Visible = False
End With

End Sub

Sub Auto_Close()
Call RemoveMenubar
End Sub

Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(runCreatetribalTR).Delete
On Error GoTo 0
End Sub

Sub CreateMenubar()

Call RemoveMenubar

With Application.CommandBars.Add
.Name = runCreatetribalTR
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarTop
.Left = CommandBars("RunCreateTribalTR").Left +
CommandBars("RunCreateTribalTR").Width
.RowIndex = CommandBars("RunCreateTribalTR").RowIndex

With
CommandBars("RunCreateTribalTR").Controls.Add(Type :=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "CreateTR"
.Caption = "CreateTR"
.Style = msoButtonCaption
.TooltipText = "Create TR"
End With

' With
CommandBars("RunCreateTribalTR").Controls.Add(Type :=msoControlButton)
' .OnAction = "'" & ThisWorkbook.Name & "'!" & "MergeCells"
' .Caption = "Merge Cells"
' .Style = msoButtonCaption
' .TooltipText = "Merge/Unmerge Cells"
' .BeginGroup = True
' End With
With
CommandBars("RunCreateTribalTR").Controls.Add(Type :=msoControlButton, ID _
:=2950, Befo=3) <----SUBSCRIPT OUT OF RANGE
End With
End With
End Sub


Public Sub CreateTR()
Call CreateTribalSheet
End Sub

I've marked the error line above.
Can anyone please help me here?
Thanks!


joel
Subscript out of range error?
 
Do you have 3 items in the control bar?

"salgud" wrote:

I'm not too familar with this error, and new to working with toolbars in
VBA, so I'm lost on even what to look for on this error. The code so far
is:

Option Explicit
Public Const runCreatetribalTR As String = "RunCreateTribalTR"

Sub Auto_Open()
Call CreateMenubar
With CommandBars("CreateTR")
.Enabled = False
.Visible = False
End With

End Sub

Sub Auto_Close()
Call RemoveMenubar
End Sub

Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(runCreatetribalTR).Delete
On Error GoTo 0
End Sub

Sub CreateMenubar()

Call RemoveMenubar

With Application.CommandBars.Add
.Name = runCreatetribalTR
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarTop
.Left = CommandBars("RunCreateTribalTR").Left +
CommandBars("RunCreateTribalTR").Width
.RowIndex = CommandBars("RunCreateTribalTR").RowIndex

With
CommandBars("RunCreateTribalTR").Controls.Add(Type :=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "CreateTR"
.Caption = "CreateTR"
.Style = msoButtonCaption
.TooltipText = "Create TR"
End With

' With
CommandBars("RunCreateTribalTR").Controls.Add(Type :=msoControlButton)
' .OnAction = "'" & ThisWorkbook.Name & "'!" & "MergeCells"
' .Caption = "Merge Cells"
' .Style = msoButtonCaption
' .TooltipText = "Merge/Unmerge Cells"
' .BeginGroup = True
' End With
With
CommandBars("RunCreateTribalTR").Controls.Add(Type :=msoControlButton, ID _
:=2950, Befo=3) <----SUBSCRIPT OUT OF RANGE
End With
End With
End Sub


Public Sub CreateTR()
Call CreateTribalSheet
End Sub

I've marked the error line above.
Can anyone please help me here?
Thanks!

salgud
Subscript out of range error?
 
On Tue, 7 Apr 2009 11:02:02 -0700, joel wrote:

Do you have 3 items in the control bar?

"salgud" wrote:

I'm not too familar with this error, and new to working with toolbars in
VBA, so I'm lost on even what to look for on this error. The code so far
is:

Option Explicit
Public Const runCreatetribalTR As String = "RunCreateTribalTR"

Sub Auto_Open()
Call CreateMenubar
With CommandBars("CreateTR")
.Enabled = False
.Visible = False
End With

End Sub

Sub Auto_Close()
Call RemoveMenubar
End Sub

Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(runCreatetribalTR).Delete
On Error GoTo 0
End Sub

Sub CreateMenubar()

Call RemoveMenubar

With Application.CommandBars.Add
.Name = runCreatetribalTR
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarTop
.Left = CommandBars("RunCreateTribalTR").Left +
CommandBars("RunCreateTribalTR").Width
.RowIndex = CommandBars("RunCreateTribalTR").RowIndex

With
CommandBars("RunCreateTribalTR").Controls.Add(Type :=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "CreateTR"
.Caption = "CreateTR"
.Style = msoButtonCaption
.TooltipText = "Create TR"
End With

' With
CommandBars("RunCreateTribalTR").Controls.Add(Type :=msoControlButton)
' .OnAction = "'" & ThisWorkbook.Name & "'!" & "MergeCells"
' .Caption = "Merge Cells"
' .Style = msoButtonCaption
' .TooltipText = "Merge/Unmerge Cells"
' .BeginGroup = True
' End With
With
CommandBars("RunCreateTribalTR").Controls.Add(Type :=msoControlButton, ID _
:=2950, Befo=3) <----SUBSCRIPT OUT OF RANGE
End With
End With
End Sub


Public Sub CreateTR()
Call CreateTribalSheet
End Sub

I've marked the error line above.
Can anyone please help me here?
Thanks!


Thanks to you both!


All times are GMT +1. The time now is 07:18 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com