Subscript out of range?

[CC268]

Hey guys,

Borrowed this awesome Sheet Selector code from a fellow here:

Sub SheetSelector()Const ColItems  As Long = 20
Const LetterWidth As Long = 20
Const HeightRowz As Long = 18
Const SheetID As String = "__SheetSelection"
 
Dim i%, TopPos%, iSet%, optCols%, intLetters%, optMaxChars%, optLeft%
Dim wsDlg As DialogSheet, objOpt As OptionButton, optCaption$, objSheet As Object
optCaption = "": i = 0
 
Application.ScreenUpdating = False
 
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(SheetID).Delete
Application.DisplayAlerts = True
Err.Clear
 
Set wsDlg = ActiveWorkbook.DialogSheets.Add
With wsDlg
.Name = SheetID
.Visible = xlSheetHidden
iSet = 0: optCols = 0: optMaxChars = 0: optLeft = 78: TopPos = 40
 
For Each objSheet In ActiveWorkbook.Sheets
If objSheet.Visible = xlSheetVisible Then
i = i + 1
 
If i Mod ColItems = 1 Then
optCols = optCols + 1
TopPos = 40
optLeft = optLeft + (optMaxChars * LetterWidth)
optMaxChars = 0
End If
 
intLetters = Len(objSheet.Name)
If intLetters > optMaxChars Then optMaxChars = intLetters
iSet = iSet + 1
.OptionButtons.Add optLeft, TopPos, intLetters * LetterWidth, 16.5
.OptionButtons(iSet).Text = objSheet.Name
TopPos = TopPos + 13
 
End If
Next objSheet
 
If i > 0 Then
 
.Buttons.Left = optLeft + (optMaxChars * LetterWidth) + 24
 
With .DialogFrame
.Height = Application.Max(68, WorksheetFunction.Min(iSet, ColItems) * HeightRowz + 10)
.Width = optLeft + (optMaxChars * LetterWidth) + 24
.Caption = "Select Sheet:"
End With
 
.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront
Application.ScreenUpdating = True
 
If .Show = True Then
For Each objOpt In wsDlg.OptionButtons
If objOpt.Value = xlOn Then
optCaption = objOpt.Caption
Exit For
End If
Next objOpt
End If
 
If optCaption = "" Then
MsgBox "You did not select a worksheet.", 48
Exit Sub
Else
 
'MsgBox "You selected the sheet named ''" & optCaption & "''." & vbCrLf & "Click OK to go there.", 64, "FYI:"
Sheets(optCaption).Activate
 
End If
 
End If
 
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
 
End With
End Sub

I call this code out in another code of mine, but I get a "Subscript out of range" on the bolded line. I thought that .Worksheets(optCaption) would be the correct name considering optCaption is what is used in the Sheet Selector code. What am I missing here?

Sub FormatAsBuilt()

Dim CopyFromWbk As Workbook
Dim CopyToWbk As Workbook
Dim ShToCopy As Worksheet
Dim FileName As Variant
    
   With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        If .Show Then
            Set wb = Workbooks.Open(.SelectedItems(1))
        Else
            Exit Sub
        End If
    End With
    
Call SheetSelector


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
    
Set CopyFromWbk = wb
[COLOR=#ff0000][B]Set ShToCopy = CopyFromWbk.Worksheets(optCaption)[/B][/COLOR]
Set CopyToWbk = ThisWorkbook
ShToCopy.Copy After:=CopyToWbk.Sheets(CopyToWbk.Sheets.Count)
[COLOR=#ff0000][B]Sheets(optCaption).Name = "Sheet1"[/B][/COLOR]
CopyFromWbk.Close savechanges:=False


Rows("1:9").Delete
Columns("B:D").Insert
Cells(1, 2) = "NHA Part Number"
Cells(1, 3) = "NHA Serial Number"
Cells(1, 4) = "NHA Rev"
Columns("L:R").EntireColumn.Delete
Columns.AutoFit
ActiveSheet.Cells.UnMerge
Columns("G:G").Select
Selection.Copy
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("I:I").Select
Selection.Copy
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Columns("J:J").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft


Dim partno(6) As Variant 'defining our variables - variant is a data type that can hold any type of value you want
Dim serialno(6) As Variant 'same as above
Dim revno(6) As Variant 'same as above
inrow = 2 'defining the variable "inrow" to equal 2
inlevel = 0 'defining the variable "inlevel" to equal 0


Range("b2:d5000").ClearContents 'this is simply taking the range of b2:d5000 and clearing the contents of the cells




While Cells(inrow, 1) <> "" 'while cell in row 2, column 1...
currentlevel = Cells(inrow, 1) 'the variable currentlevel is equal to the value of the cell in row 2, column 1
currentpart = Cells(inrow, 5) 'the variable currentpart is equal to the value of the cell in row 2, column 5
currentserial = Cells(inrow, 6) 'the variable currentserial is equal to the value of the cell in row 2, column 6
currentrev = Cells(inrow, 7) ' the variable currentrev is equal to the value of the cell in row 2, column 7
partno(currentlevel) = currentpart 'the variable partno in the currentlevel is equal to the variable currentpart (whatever value is in row 2, column 5)
serialno(currentlevel) = currentserial 'the variable serialno in the currentlevel is equal to the variable currentserial (whatever value is in row 2, column 6)
revno(currentlevel) = currentrev 'the variable revno in the currentlevel is equal to the variable currentrev (whatever value is in row 2, column 7)
    
If currentlevel > 1 Then 'if the value in row 2, column 1 is greater than 1 then proceed to the following...
Cells(inrow, 2) = partno(currentlevel - 1) 'the value in row 2, column 2 = value of partno in the current level - 1
Cells(inrow, 3) = serialno(currentlevel - 1) 'the value in row 2, column 3 = value of partno in the current level - 1
Cells(inrow, 4) = revno(currentlevel - 1) 'the value in row 2, column 4 = value of partno in the current level - 1
End If 'end if statement
        
inrow = inrow + 1 'move onto the next row (row 3)
Wend 'end while loop


Columns("A").EntireColumn.Delete


Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove


Dim Lst As Long
Lst = Range("B" & Rows.Count).End(xlUp).Row


With Range("A1")
    .Value = "1"
    .AutoFill Destination:=Range("A1").Resize(Lst), Type:=xlFillSeries
End With


Cells.Select
     With Selection
        .WrapText = False
     End With
     
Columns.HorizontalAlignment = xlCenter
Columns.VerticalAlignment = xlCenter
Columns.AutoFit
Rows.AutoFit


Cells.Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
Cells.Select
    With Selection.Borders
    .LineStyle = xlNone
    End With


Range("A1").Select
    
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
    
End Sub

 

[Joe4]

Note that in the first procedure, optCaption is a user defined variable that is being set:

Dim wsDlg As DialogSheet, objOpt As OptionButton, [COLOR=#ff0000]optCaption[/COLOR]$, objSheet As Object
...
optCaption = objOpt.Caption

In the second procedure, you never set it to anything prior to trying to reference it.

 

[CC268]

Note that in the first procedure, optCaption is a user defined variable that is being set:

Dim wsDlg As DialogSheet, objOpt As OptionButton, [COLOR=#ff0000]optCaption[/COLOR]$, objSheet As Object
...
optCaption = objOpt.Caption

In the second procedure, you never set it to anything prior to trying to reference it.

How would I go about fixing that?

 

[Joe4]

You need to set it equal to something before referencing it.
What you need to set it to, I couldn't tell you. I really haven't analyzed what the code is supposed to be doing. I figured you would have a pretty good idea, since you updated it.

Note that if you are trying to use the value that is being set in the first procedure. By default, values are not maintained and accessible between multiple procedures.
You need to do one of two things:
1. Set optCaption up to be a Global Variable, so it is available to all procedures
- or -
2. Pass the value of optCaption from the one procedure to the next

 

[CC268]

You need to set it equal to something before referencing it.
What you need to set it to, I couldn't tell you. I really haven't analyzed what the code is supposed to be doing. I figured you would have a pretty good idea, since you updated it.

Note that if you are trying to use the value that is being set in the first procedure. By default, values are not maintained and accessible between multiple procedures.
You need to do one of two things:
1. Set optCaption up to be a Global Variable, so it is available to all procedures
- or -
2. Pass the value of optCaption from the one procedure to the next

Hmm so I could do something like this at the beginning of the code?

Public optCaption As Variant

 

[Joe4]

Give it a shot, and see if it works.
You will just want to also them remove the variable declaration from the first procedure (so it is not declared twice).

 

[CC268]

Give it a shot, and see if it works.
You will just want to also them remove the variable declaration from the first procedure (so it is not declared twice).

Hmm I am stuck...can't seem to get it to work. I am a little confused...is optCaption the variable for whatever sheet name you select?

 

[Joe4]

Hmm I am stuck...can't seem to get it to work. I am a little confused...is optCaption the variable for whatever sheet name you select?

You may want to ask the person who wrote the code. I am not sure what it is supposed to be doing, and it isn't manner in which I typically write code.