VBA help

[Mel Smith]

Hello folks,

I have inherited a VBA-heavy workbook with three command buttons on one of the sheets. The first two command buttons work perfectly whereas the third doesn't and i cannot understand why. The first lot of code below works but the second set doesn't. Can anyone tell me why, please?

Working code:

Private Sub CommandButton1_Click()

Dim RetStat
RetStat = Application.Dialogs(xlDialogPrinterSetup).Show
If RetStat Then
CommandButton1.Visible = False
CommandButton2.Visible = False
Me.PrintForm
CommandButton1.Visible = True
CommandButton2.Visible = True
End If
End Sub

Private Sub CommandButton2_Click()

Unload Me

End Sub

Private Sub cbplayer_Change()
Dim playerName As String
playerName = cbPlayer.Value

Dim data_row As Integer
Dim i As Integer
Dim current_textBox As Integer

Dim column_nums As Variant
Dim sheet_num As Variant


sheet_num = Array(1, 2, 3)
' these are the column numbers shown below in the array
'Del = Array(3, 4, 16, 6, 7, 8, 9, 10, 11, 12, 13, 14, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25)
column_nums = Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25) ', 27, 28, 29
current_textBox = 1

For b = 1 To 3
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(sheet_num(b - 1))
   
    With ws
        data_row = WorksheetFunction.Match(playerName, .Columns("B:B"), 0)
            If IsNumeric(data_row) Then
                For i = 0 To 33
                    Me.Controls("TextBox" & current_textBox).Value = .Cells(data_row, column_nums(i))
                    current_textBox = current_textBox + 1
                Next
            Else
                MsgBox playerName & vbNewLine & "Not Found on sheet " & .Name
            End If
    End With
   
Next
                      
End Sub

Private Sub Userform_Initialize()
cbPlayer.List = ThisWorkbook.Worksheets(1).Range("B4:B63").Value2
End Sub

Not working code:

Private Sub CommandButton1_Click()

Dim RetStat
RetStat = Application.Dialogs(xlDialogPrinterSetup).Show
If RetStat Then
CommandButton1.Visible = False
CommandButton2.Visible = False
Me.ScrollHeight = 0
Me.ScrollBars = fmScrollBarsNone
Me.Height = 750
Me.PrintForm
Me.ScrollHeight = 750
Me.ScrollBars = fmScrollBarsVertical
Me.Height = 600
CommandButton1.Visible = True
CommandButton2.Visible = True
End If
End Sub

Private Sub CommandButton2_Click()

Unload Me

End Sub

Private Sub cbplayer_Change()
Dim playerName As String
playerName = cbPlayer.Value

Dim data_row As Integer
Dim i As Integer
Dim current_textBox As Integer

Dim column_nums As Variant
Dim sheet_num As Variant


sheet_num = Array(7, 8, 9, 10, 11)
' these are the column numbers shown below in the array
'Del = Array(3, 4, 16, 6, 7, 8, 9, 10, 11, 12, 13, 14, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25)
column_nums = Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25) ', 27, 28, 29
current_textBox = 1

For b = 1 To 5
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(sheet_num(b - 1))
   
    With ws
        data_row = WorksheetFunction.Match(playerName, .Columns("B:B"), 0)
            If IsNumeric(data_row) Then
                For i = 0 To 33
                    Me.Controls("TextBox" & current_textBox).Value = .Cells(data_row, column_nums(i))
                    current_textBox = current_textBox + 1
                Next
            Else
                MsgBox playerName & vbNewLine & "Not Found on sheet " & .Name
            End If
    End With
   
Next
                      
End Sub

Private Sub TextBox171_Change()

End Sub

Private Sub Userform_Initialize()
cbPlayer.List = ThisWorkbook.Worksheets(1).Range("B4:B63").Value2
End Sub

Many thanks, Mel

 

[Mel Smith]

I forgot to add that the (first?) line that is highlighted when i press debug is:

Me.Controls("TextBox" & current_textBox).Value = .Cells(data_row, column_nums(i))
current_textBox = current_textBox + 1

Mel