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]

it gives me value 1 from window locals

 

[dmt32]

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

Sorry

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

fat finger I meant last one to be List

For j = 1 To 3
  inarr = Sheets("SH" & j).Range("A3:D" & [a65536].End(3).Row).Value

If you only want SH1 SH2 SH3 try changes above & see if helps

Dave

 

[Fluff]

it gives me value 1 from window locals

In that case you don't have a sheet called SH01

 

[abdelfattah]

For j = 1 To 3
inarr = Sheets("SH" & j).Range("A3:D" & [a65536].End(3).Row).Value

it gives me another error
could not set the list property .invalid property array idex
in this line

 .List(Z, 0) = (inarr(i, 1))

 

[Fluff]

It needs to be

With Sheets("SH" & j)
   inarr = .Range("A3:D" & .[a65536].End(3).Row).Value
End With

 

[abdelfattah]

It needs to be

With Sheets("SH" & j)
inarr = .Range("A3:D" & .[a65536].End(3).Row).Value
End With

now it gives me the same error as in post #24

 

[Fluff]

Not on your test file it doesn't, unless you have changed it.

 

[abdelfattah]

this is what I have

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" & j)
   inarr = .Range("A3:D" & .[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 sheets name sh1,sh2,sh3
despite all of this the error still shows

 

[Fluff]

Why have you put the loop back to 4?

 

[abdelfattah]

I have four sheet the firs sheet is different about name starts from the second sheet to four sheet based on names sh1,2,3 it loops from 1 to 4 , is it problem? what I should do form 2 to 4 or what !