VBA help

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
881
Office Version
  1. 365
Platform
  1. Windows
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:
VBA 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:

VBA 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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Mel Smith

Well-known Member
Joined
Dec 13, 2005
Messages
881
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,143,677
Messages
5,720,252
Members
422,272
Latest member
ginkgoVil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top