fixing error subscript out of range in userform

[abdelfattah]

hello
i have a problem about this error subscript out of range when run userform and highlights in this line in module

userform2.show

I'm pretty sure about name of userform and this is the whole codes

Private Sub Commandbutton1_Click()
Dim cNum As Integer
Dim X As Integer
Dim nextrow As Range
Dim sht As String
sht = ComboBox1.Value
If Me.ComboBox1.Value = "" Then
MsgBox "Select a sheet from the combobox and add the date"
Exit Sub
End If
cNum = 4
Set nextrow = Sheets(sht).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next
For X = 1 To cNum
Me.Controls("Reg" & X).Value = ""
Next

MsgBox "The values have been sent to the " & sht & " sheet"

End Sub

Private Sub UserForm_Initialize()
Dim s As Long
    For s = 1 To Sheets.Count
        ComboBox1.AddItem Sheets(s).Name
    Next
 
  Dim ws As Worksheet, a, i As Long, j As Long
 
 ListBox1.ColumnWidths = "100;85;85;80;50"
ListBox1.ColumnCount = 4
Z = 1
For j = 1 To 4
  inarr = Sheets("SH" & Format(j, "00")).Range("A3:l" & [a65536].End(3).Row).Value
  With ListBox1
    For i = 1 To UBound(inarr)
      .AddItem
      .List(Z, 0) = (inarr(i, 1))
      .List(Z, 1) = (inarr(i, 2))
      .List(Z, 2) = (inarr(i, 3))
      .List(Z, 3) = (inarr(i, 4))
      Z = Z + 1
    Next i
  End With
Next j
End Sub

I hope anybody help
thanks

 

[abdelfattah]

hi, Fluff still the problem continues
this is the final code

Private Sub UserForm_Initialize()
Dim s As Long
    For s = 1 To Sheets.Count
        ComboBox1.AddItem Sheets(s).Name
    Next
 
  Dim ws As Worksheet, a, i As Long, j As Long
 
 ListBox1.ColumnWidths = "100;85;85;80;50"
ListBox1.ColumnCount = 4
Z = 1
For j = 1 To 4
  With Sheets("SH" & Format(j, "00"))
   inarr = .Range("A3:l" & .[a65536].End(3).Row).Value
End With
  With ListBox1
    For i = 1 To UBound(inarr)
      .AddItem
      .List(Z, 0) = (inarr(i, 1))
      .List(Z, 1) = (inarr(i, 2))
      .List(Z, 2) = (inarr(i, 3))
      .List(Z, 3) = (inarr(i, 4))
      Z = Z + 1
    Next i
  End With
Next j
End Sub

and the name sheets are sh01,sh02,sh03

 

[Fluff]

Which line gives the error?

 

[abdelfattah]

sorry Fluff for delaying the error when run the userform in module

UserForm2.Show

 

[Fluff]

In the VB Editor click on tools > Options > General> Check "Break in Class module" > Ok.
Then try to run the code again, what line gets highlighted.

 

[dmt32]

Hi,

you are looping for 4 sheets named SH1 SH2 SH3 SH4 but you only have 3 in your sample file

For j = 1 To 4

Try changing the 4 to a 3 & see if it resolves your issue

Also, your sample file looks to be the similar to one posted on this site:Populate ListBox from Multiple sheets - OzGrid Free Excel/VBA Help Forum

If this is so & it does what you are trying to achieve what is wrong with the posted solution that you are trying to change?

Dave

 

[abdelfattah]

In the VB Editor click on tools > Options > General> Check "Break in Class module" > Ok.
Then try to run the code again, what line gets highlighted.

highlight in this line

With Sheets("SH" & Format(j, "00"))

 

[abdelfattah]

you are looping for 4 sheets named SH1 SH2 SH3 SH4 but you only have 3 in your sample file

actually about sheets I have four sheets but the first name is list if you see change from 2 to 4 also doesn't success

 

[Fluff]

What is the value of j when you get the error?

 

[abdelfattah]

if you mean variable j it is relating sheets name and if you mean column in sheet is nothing

 

[Fluff]

I mean what is the value of j when the error occurs.