Error on userform load data

[asyamonique]

Private Sub CommandButton16_Click()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlManual
Worksheets("0600").Visible = True

If FileExists("C:\me\0600.xls") Then
Workbooks.Open Filename:="C:\me\0600.xls"
Range("A:A,M:M,N:N,Q:Q").Select
Range("Q1").Activate
Selection.Copy
Windows("Result.xls").Activate
Sheets("0600").Select
Columns("A:A").Select
ActiveSheet.Paste
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Windows("0600.xls").Activate
ActiveWindow.Close False
End If
 
Dim a, i As Long, ii As Long, b(), n As Long
With ComboBox1
If .Text = "" Then Exit Sub
If WorksheetFunction.CountIf(Worksheets("0600").Range("a:a"), .Text) = 0 Then
Exit Sub
End If
a = Worksheets("0600").Range("a1").Resize(Worksheets("0600").Range("a" & Rows.Count).End(xlUp).Row, 26).Value
For i = 1 To UBound(a, 1)
If a(i, 1) = .Text Then
n = n + 1: ReDim Preserve b(1 To 26, 1 To n)
For ii = 1 To UBound(a, 2)
b(ii, n) = a(i, ii)
Next
End If
Next
End With
With ListBox1
.ColumnCount = 26
.ColumnWidths = "0;50;80;50;50;50;50,50;50;50;50;50;50,50;50;50;50;50;50,50;50;50;50;50;50,50"
.Column = b
End With
Sheet4.Select

On Error Resume Next
With Me.ListBox1
Me.TextBox1.Value = Me.ListBox1.Column(1, 0)
Me.TextBox2.Value = Me.ListBox1.Column(1, 1)
Me.TextBox3.Value = Me.ListBox1.Column(1, 2)
Me.TextBox4.Value = Me.ListBox1.Column(1, 3)
Me.TextBox5.Value = Me.ListBox1.Column(1, 4)
Me.TextBox6.Value = Me.ListBox1.Column(1, 5)
Me.TextBox7.Value = Me.ListBox1.Column(1, 6)
Me.TextBox8.Value = Me.ListBox1.Column(1, 7)
Me.TextBox9.Value = Me.ListBox1.Column(1, 8)
Me.TextBox10.Value = Me.ListBox1.Column(1, 9)
Me.TextBox11.Value = Me.ListBox1.Column(1, 10)
Me.TextBox12.Value = Me.ListBox1.Column(1, 11)
Me.TextBox13.Value = Me.ListBox1.Column(1, 12)
Me.TextBox14.Value = Me.ListBox1.Column(1, 13)
Me.TextBox15.Value = Me.ListBox1.Column(1, 14)
Me.TextBox16.Value = Me.ListBox1.Column(1, 15)
Me.TextBox17.Value = Me.ListBox1.Column(1, 16)
Me.TextBox18.Value = Me.ListBox1.Column(1, 17)
Me.TextBox19.Value = Me.ListBox1.Column(1, 18)
Me.TextBox20.Value = Me.ListBox1.Column(1, 19)
Me.TextBox21.Value = Me.ListBox1.Column(3, 0)
Me.TextBox22.Value = Me.ListBox1.Column(3, 1)
Me.TextBox23.Value = Me.ListBox1.Column(3, 2)
Me.TextBox24.Value = Me.ListBox1.Column(3, 3)
Me.TextBox25.Value = Me.ListBox1.Column(3, 4)
Me.TextBox26.Value = Me.ListBox1.Column(3, 5)
Me.TextBox27.Value = Me.ListBox1.Column(3, 6)
Me.TextBox28.Value = Me.ListBox1.Column(3, 7)
Me.TextBox29.Value = Me.ListBox1.Column(3, 8)
Me.TextBox30.Value = Me.ListBox1.Column(3, 9)
Me.TextBox31.Value = Me.ListBox1.Column(3, 10)
Me.TextBox32.Value = Me.ListBox1.Column(3, 11)
Me.TextBox33.Value = Me.ListBox1.Column(3, 12)
Me.TextBox34.Value = Me.ListBox1.Column(3, 13)
Me.TextBox35.Value = Me.ListBox1.Column(3, 14)
Me.TextBox36.Value = Me.ListBox1.Column(3, 15)
Me.TextBox37.Value = Me.ListBox1.Column(3, 16)
Me.TextBox38.Value = Me.ListBox1.Column(3, 17)
Me.TextBox39.Value = Me.ListBox1.Column(3, 18)
Me.TextBox40.Value = Me.ListBox1.Column(3, 19)
End With

Dim varTotal As Currency
Dim varRow As Integer
For varRow = 0 To (ListBox1.ListCount - 1)
varTotal = varTotal + ListBox1.Column(2, varRow)
Next
Me.TextBox724.Value = varTotal
Dim arTotal As Currency
Dim arRow As Integer
For arRow = 0 To (ListBox1.ListCount - 1)
arTotal = arTotal + ListBox1.Column(3, arRow)
Next
Me.TextBox725.Value = arTotal
Me.TextBox418.Value = TextBox724.Value
Me.TextBox381.Value = TextBox725.Value

For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
ctl.Value = Format(ctl.Value, "$#,##0.000")
End If
Next ctl
Worksheets("0600").Visible = False
Application.Calculation = xlAutomatic
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Good Day,
In regards given code above im having error.
The listbox populates the combobox index.Its filtering the column A values into the listbox.And the textboxes fills with related columns.
This code works only when its run on 'wiev code' windows(Alt+F11)other wise its hang!!
Is it possible that someone will tell me the reason why?

 

[Norie]

Where does the code 'hang' and when?

By 'wiev code' window do you mean the VBE code window?

How are you running the code exactly?

Finally what is the code meant to do and why do you have 40 textboxes when you have all the data in a listbox already?

Actualy that brings to mind another question, a 26 column listbox?

Sorry for all the questions. it's quite a lot of code to check and it would be helpful to know what it's meant to do.

 

[asyamonique]

Where does the code 'hang' and when?

By 'wiev code' window do you mean the VBE code window?

How are you running the code exactly?

Finally what is the code meant to do and why do you have 40 textboxes when you have all the data in a listbox already?

Actualy that brings to mind another question, a 26 column listbox?

Sorry for all the questions. it's quite a lot of code to check and it would be helpful to know what it's meant to do.

Hi Norie,
Yes i meaned VBE code window.
It hangs at the begining.
Actually the userform shows me the hourly interim results.And i need that textboxes to fill with column values then the user can easly wiev the report details.
As i told its very strange that it works only when its run on VBE code wiev!
Cheers

 

[Norie]

Isn't the listbox enough to view the results?

The only advantage I can see with textboxes is they could be used to change the data, though I suppose formatting could come into it.

What do you mean by 'hang'?

Does it not do anything, get stuck in a loop, error out?

Is it at the beginning of the code you posted or when you initially open/view the form?

 

[asyamonique]

I think u r right the listbox would be enough..I will remove the textboxes from userform.

Couple of questions about listbox.
1.How can i freeze to first column while scroling right/left ?
2.Is there any way to put lines between the columns? like between every two column a line...
3.Is it poossible to change the font color of list box columns?

Many Thanks again.

 

[Norie]

1 No.

2 No.

3 You can only change the font colours of all the columns not individual columns.

Sorry, that's why I mentioned that formatting might come into it.

Have you considered not even using a listbox?

What sort of data is it you are dealing with and what are you trying to do with it?

 

[asyamonique]

Yes i had.
Thanks