VBA...Help!

[Eric.Z]

I am trying to create an Excel addin file.
I have this code twice "If Workbooks("My Bids.xlsm") Is Nothing Then" in my program.
There was always a run-time error saying "subscript out of range".
However if I delete the first "If Workbooks("My Bids.xlsm") Is Nothing Then" and leave the second one there as it is, then the program works fine.
Any idea how to fix this?

Below is the code of my file.

'********************************
 
'********************************
 
'********************************
 
'********************************
 
' This is where the error occurs!!!
 
If Workbooks("My Bids.xlsm") Is Nothing Then 'this is where the error occurs.
 
Else
 
 
 
Set newCombo = mybar.Controls.Add(Type:=msoControlButton)
 
With newCombo
 
.Caption = "Yu NextPlan"
 
.faceId = 1142
 
.OnAction = "yuNext"
 
.Enabled = True
 
End With
 
 
 
 
 
Set newCombo = mybar.Controls.Add(Type:=msoControlButton)
 
With newCombo
 
.Caption = "Yu PrevPlan"
 
.faceId = 1143
 
.OnAction = "yuPrev"
 
.Enabled = True
 
End With
 
 
 
End If
 
 
 
 
 
X = Application.CommandBars.Count
 
For i = 1 To X
 
If Application.CommandBars(i).Name = "ID DF" Then Exit For
 
Next i
 
 
 
For X = 1 To i - 1
 
On Error Resume Next
 
Workbooks(BN).Sheets(" ").Cells(i, 1) = Application.CommandBars(X).Visible
 
Application.CommandBars(X).Visible = False
 
Next X
 
 
 
Set mybar = CommandBars.Add(Name:="ID", Position:=msoBarTop, Temporary:=True, MenuBar:=False)
 
mybar.Visible = True
 
Set newCombo = mybar.Controls.Add(Type:=msoControlDropdown)
 
With newCombo
 
i = 2
 
.AddItem Left(Sheets(MA).Cells(i, 1), 5)
 
i = 3
 
Do While Sheets(MA).Cells(i, 1) <> ""
 
If Left(Sheets(MA).Cells(i, 1), 5) <> Left(Sheets(MA).Cells(i - 1, 1), 5) Then .AddItem Left(Sheets(MA).Cells(i, 1), 5)
 
i = i + 1
 
Loop
 
.Caption = "- or Select ID"
 
.Style = msoComboLabel
 
.OnAction = "Select"
 
.BeginGroup = True
 
End With

The first sentence below works fine even though the code is exactly the same!!

If Workbooks("My Bids.xlsm") Is Nothing Then 'If i delete the previous sentence, this sentence works fine without any problems.
 
Else
Set newCombo = mybar.Controls.Add(Type:=msoControlDropdown)
 
With newCombo
 
i = 2
 
.AddItem Workbooks("My Bids.xlsm").Worksheets("Sheet1").Cells(i, 1)
 
i = 3
 
Do While Workbooks("My Bids.xlsm").Worksheets("Sheet1").Cells(i, 1) <> ""
 
.AddItem Workbooks("My Bids.xlsm").Worksheets("Sheet1").Cells(i, 1)
 
i = i + 1
 
Loop
 
.Caption = "Select Plan ID"
 
.Style = msoComboLabel
 
.OnAction = "YuSwitch"
 
.BeginGroup = True
 
End With
 
 
 
End If

 

[Andy Nguyen]

Try this http://social.msdn.microsoft.com/Forums/is/isvvba/thread/e53256f5-15bc-411e-8c83-22867aa4c4cc