Answered Read or get cell value (Excel)

[freddyboy]

Good evening, I am quite new to VB. I will try to describe as best as I can what I am trying to do.

I have a treeview called "TreeView_Insp_Records". It has several nodes and each nodes text has been renamed by inspections records names. For exemple ( Insp_blabla1, Insp_blabla2, and so on).
Theses inspections can all be found on my excel document shee1. I would like to be able to select the inspection node in the treeview, so that vb search the exact string match on the 1st column and for exemple return the value for column3 (barsalon) in a textbox.
Can I get a hand ?

Column1 (Insp Records)	Column2 (Insp Name)	Column3 ( Company)	Column4 (numbers)
Insp_blabla1	fred	barsalon	345-334-3333
Insp_blabla2 Insp_blabla3 and so on

 

[freddyboy]

Option Explicit On
Imports System.Threading
Imports Microsoft.Office.Interop.Excel
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Dim MonThread As New Thread(AddressOf Rangeloops)

    Public Delegate Sub Callback(ByVal s As Control, ByVal v As Object)

    Private Sub UpdateUI(ByVal this_Control As Control, ByVal vRange As Object)
        this_Control.Text = CType(vRange, String)

    End Sub
    
     Private Sub ExecuteWork()

        Dim sh As Worksheet
        sh = xlWorkBook.Sheets("XlWorkInspfred")
        Dim rg As Range
        rg = sh.Range("A1").CurrentRegion
        'I am the working method, the method we called from UI thread.
        'I am not on the UI thread any more so i can't talk to it.
        Dim s As String = "Testing Worker Method"
        For i = 1 To rg.Rows.Count 'Range pour Inspfred
            Debug.Write(s) 'This is running on non-ui thread
            'How to talk to your ui?

            TextBox_RDIMS.Invoke(New Callback(AddressOf UpdateUI), TextBox_RDIMS, rg.Cells(i, 2).value) 'example rg1.Cells(i, 19).value and don't forget to cast if needed.

            'We call the control we MUST invoke, create a new delegate, send it the method to update from, and provide the control to that method, with the new value.

            'See on updating values from cells : https://stackoverflow.com/questions/23004274/vb-net-excel-worksheet-cells-value

        Next
    End Sub
    
    Public Shared xlApp As Excel.Application
    Public Shared Sheets As Excel.Worksheet

    Public Shared XlWorkInspfred As Excel.Worksheet

    Public Shared xlWorkBook As Excel.Workbook
    Dim appPath As String = My.Application.Info.DirectoryPath


    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open("D:\Users\PC\Desktop\test.xlsx")

    End Sub

    Private Sub TreeView_Insp_Records__AfterSelect(sender As System.Object, e As System.Windows.Forms.TreeViewEventArgs) Handles TreeView_Insp_Records_AfterSelect.AfterSelect
        Dim thread As New Thread(Sub() ExecuteWork())
        thread.Start()
    End Sub

    Private Sub Rangeloops()

        Dim row As Long
        row = 1
        Dim sh As Worksheet
        sh = xlWorkBook.Sheets("XlWorkInspfred")
        Dim rg As Range
        rg = sh.Range("A1").CurrentRegion
        Dim i As Long
        For i = 1 To rg.Rows.Count

            If rg.Cells(i, 1).value = TreeView_Insp_Records_AfterSelect.SelectedNode.Text Then
                TextBox_RDIMS.Text = rg.Cells(i, 2).value

            End If
            row = row + 1
        Next
    End Sub
End Class

Ok, so I got this part working with no errors (I started a small project just to make sure I can get this working fine). Now the range value doesn't change when I select other nodes in the treeview when running it because I have to add this line: ( If rg.Cells(i, 1).value = TreeView_Insp_Records_AfterSelect.SelectedNode.Text Then ) in the UpdateUI sub and then in the ExecuteWork() sub. I've tried to look at some exemples on how to do it and all my attempts have failed. I'd rather not past anything I've tried simply because I feel it's getting quite complex for my level. If I can get the range working, I should be good to pass the rest of the text control in the UpdateUI and then the Executework() by myself. But that range part is blocking me.

One more thing, I was confuse with:

Private Sub UpdateUI(ByVal this_Control As Control, ByVal vRange As Object)
this_Control.Text = CType(vRange, String)
End Sub

I changed this_Control.Text by Textbox_RDIMS.Text and didn't get any errors. So I figured it didn't matter… So I changed it back to this_Control.Text. I suppose it has to stay like this ?

 

[Sheepings]

You're getting there slowly. Lets deal with one issue at a time. For the AfterSelect not doing anything. well it should have been evident to add parameters to your method that's executed by your thread, and to do that, you need to specify your parameters in your thread creation for the methods parameter also... you do that like this :

Change :

        Dim thread As New Thread(Sub() ExecuteWork())
        thread.Start()

Change to :

        Dim thread As New Thread(Sub() ExecuteWork(TreeView_Insp_Records.SelectedNode.Text))
        thread.Start()

Change :

     Private Sub ExecuteWork()

Change to :

    Private Sub ExecuteWork(ByVal value As String)

Then in your ExecuteWork method :

If rg.Cells(i, 1).value = value Then
'Do whatever

value Then is referring to the : ExecuteWork(ByVal value As String) being value in the parameter.

 

[Sheepings]

I changed this_Control.Text by Textbox_RDIMS.Text and didn't get any errors. So I figured it didn't matter…

It does matter. Don't change the code I gave you. That's why I told you to send the whole control from your loop : For i = 1 To rg1.Rows.Count and the method will handle which control gets updated.

 

[Sheepings]

Given you had in your original code :

                TextBox_RDIMS.Text = rg2.Cells(i, 19).value
                TextBox_Type.Text = rg2.Cells(i, 6).value

These would become :

TextBox_RDIMS.Invoke(New Callback(AddressOf UpdateUI), TextBox_RDIMS, rg2.Cells(i, 19).value)
TextBox_Type.Invoke(New Callback(AddressOf UpdateUI), TextBox_Type, rg2.Cells(i, 6).value)

And so on for each textbox in your loop...

 

[freddyboy]

Option Explicit On
Imports System.Threading
Imports Microsoft.Office.Interop.Excel
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Dim MonThread As New Thread(AddressOf Rangeloops)

    Public Delegate Sub Callback(ByVal s As Control, ByVal v As Object)
    
     Private Sub UpdateUI(ByVal this_Control As Control, ByVal vRange As Object)

        this_Control.Text = CType(vRange, String)

    End Sub
    Private Sub ExecuteWork(ByVal value As String)

        Dim sh As Worksheet
        sh = xlWorkBook.Sheets("XlWorkInspfred")
        Dim rg As Range
        rg = sh.Range("A1").CurrentRegion
        'I am the working method, the method we called from UI thread.
        'I am not on the UI thread any more so i can't talk to it.
        Dim s As String = "Testing Worker Method"
        For i = 1 To rg.Rows.Count 'Range pour Inspfred
            Debug.Write(s) 'This is running on non-ui thread
            'How to talk to your ui?
            If rg.Cells(i, 1).value = value Then
                TextBox_RDIMS.Invoke(New Callback(AddressOf UpdateUI), TextBox_RDIMS, rg.Cells(i, 2).value) 'example rg1.Cells(i, 19).value and don't forget to cast if needed.

                'We call the control we MUST invoke, create a new delegate, send it the method to update from, and provide the control to that method, with the new value.

                'See on updating values from cells : https://stackoverflow.com/questions/23004274/vb-net-excel-worksheet-cells-value
            End If
        Next
    End Sub
    
    Public Shared xlApp As Excel.Application
    Public Shared Sheets As Excel.Worksheet

    Public Shared XlWorkInspfred As Excel.Worksheet

    Public Shared xlWorkBook As Excel.Workbook
    Dim appPath As String = My.Application.Info.DirectoryPath


    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open("D:\Users\PC\Desktop\test.xlsx")

    End Sub

    Private Sub TreeView_Insp_Records_AfterSelect(sender As System.Object, e As System.Windows.Forms.TreeViewEventArgs) Handles TreeView_Insp_Records.AfterSelect
        Dim thread As New Thread(Sub() ExecuteWork(TreeView_Insp_Records.SelectedNode.Text))
        thread.Start()
    End Sub

    Private Sub Rangeloops()

        Dim row As Long
        row = 1
        Dim sh As Worksheet
        sh = xlWorkBook.Sheets("XlWorkInspfred")
        Dim rg As Range
        rg = sh.Range("A1").CurrentRegion
        Dim i As Long
        For i = 1 To rg.Rows.Count

            If rg.Cells(i, 1).value = TreeView_Insp_Records.SelectedNode.Text Then
                TextBox_RDIMS.Text = rg.Cells(i, 2).value

            End If
            row = row + 1
        Next
    End Sub
End Class

Line 55 gives me the error attached. (1.PNG)

 

[Sheepings]

Small adjustment :

    Private Sub TreeView1_AfterSelect(sender As Object, e As TreeViewEventArgs) Handles TreeView_Insp_Records.AfterSelect
        Dim thread As New Thread(Sub() ExecuteWork(e.Node.Text))
        thread.Start()
    End Sub

No more error. Sorry, I'm not currently using VS to write this.

 

[freddyboy]

Imports System.Threading

Public Class RSIform

    Dim MonThread As New Thread(AddressOf Rangeloops)
    Public Delegate Sub Callback(ByVal s As Control, ByVal v As Object)
    
     Private Sub ExecuteWork(ByVal value As String)

        Dim row As Long
        row = 1

        Dim Inspfred As Worksheet
        Dim Inspchris As Worksheet
        Dim Inspjr As Worksheet
        Dim Inspluc As Worksheet
        Dim Inspted As Worksheet
        Inspfred = Form1.xlWorkBook.Sheets("Inspfred")
        Inspchris = Form1.xlWorkBook.Sheets("Inspchris")
        Inspjr = Form1.xlWorkBook.Sheets("Inspjr")
        Inspluc = Form1.xlWorkBook.Sheets("Inspluc")
        Inspted = Form1.xlWorkBook.Sheets("Inspted")
        Dim rg1 As Range 'Range for fred
        Dim rg2 As Range 'Range for chris
        Dim rg3 As Range 'Range for jr
        Dim rg4 As Range 'Range for luc
        Dim rg5 As Range 'Range for ted
        rg1 = Inspfred.Range("C1").CurrentRegion
        rg2 = Inspchris.Range("C1").CurrentRegion
        rg3 = Inspjr.Range("C1").CurrentRegion
        rg4 = Inspluc.Range("C1").CurrentRegion
        rg5 = Inspted.Range("C1").CurrentRegion
        
         Dim s As String = "Testing Worker Method"
        For i = 1 To rg1.Rows.Count 'Range pour Inspfred
            Debug.Write(s)
            
            If rg1.Cells(i, 3).value = value Then
                TextBox_RDIMS.Invoke(New Callback(AddressOf UpdateUI), TextBox_RDIMS, rg1.Cells(i, 19).value)
                TextBox_RSIG.Invoke(New Callback(AddressOf UpdateUI), TextBox_RSIG, rg1.Cells(i, 18).value)
                TextBox_LocationNAME.Invoke(New Callback(AddressOf UpdateUI), TextBox_LocationNAME, rg1.Cells(i, 4).value)
                TextBox_Type.Invoke(New Callback(AddressOf UpdateUI), TextBox_Type, rg1.Cells(i, 6).value)
                TextBox_ABC.Invoke(New Callback(AddressOf UpdateUI), TextBox_ABC, rg1.Cells(i, 7).value)
                TextBox_Railway.Invoke(New Callback(AddressOf UpdateUI), TextBox_Railway, rg1.Cells(i, 5).value)
                TextBox_issue.Invoke(New Callback(AddressOf UpdateUI), TextBox_issue, rg1.Cells(i, 8).value)
                TextBox_InspFROM.Invoke(New Callback(AddressOf UpdateUI), TextBox_InspFROM, rg1.Cells(i, 9).value)
                TextBox_InspTO.Invoke(New Callback(AddressOf UpdateUI), TextBox_InspTO, rg1.Cells(i, 10).value)
                TextBox_Total_Insp.Invoke(New Callback(AddressOf UpdateUI), TextBox_Total_Insp, rg1.Cells(i, 11).value)
                TextBox_Date.Invoke(New Callback(AddressOf UpdateUI), TextBox_Date, rg1.Cells(i, 12).value)
                TextBox_Year.Invoke(New Callback(AddressOf UpdateUI), TextBox_Year, rg1.Cells(i, 2).value)
                TextBox_Lead_RSI.Invoke(New Callback(AddressOf UpdateUI), TextBox_Lead_RSI, rg1.Cells(i, 15).value)
                TextBox_2nd_RSI.Invoke(New Callback(AddressOf UpdateUI), TextBox_2nd_RSI, rg1.Cells(i, 16).value)
                TextBox_Letterofconcern.Invoke(New Callback(AddressOf UpdateUI), TextBox_Letterofconcern, rg1.Cells(i, 21).value)
                TextBox_Prenotice.Invoke(New Callback(AddressOf UpdateUI), TextBox_Prenotice, rg1.Cells(i, 23).value)
                TextBox_notice_order.Invoke(New Callback(AddressOf UpdateUI), TextBox_notice_order, rg1.Cells(i, 26).value)
                TextBox_NAIAT.Invoke(New Callback(AddressOf UpdateUI), TextBox_NAIAT, rg1.Cells(i, 28).value)
                TextBox_AMP.Invoke(New Callback(AddressOf UpdateUI), TextBox_AMP, rg1.Cells(i, 29).value)
                TextBox_letterofwarning.Invoke(New Callback(AddressOf UpdateUI), TextBox_letterofwarning, rg1.Cells(i, 31).value)
                TextBox_Comments.Invoke(New Callback(AddressOf UpdateUI), TextBox_Comments, rg1.Cells(i, 32).value)
                TextBox_insp_type.Invoke(New Callback(AddressOf UpdateUI), TextBox_insp_type, rg1.Cells(i, 17).value)
                row = row + 1
                Exit For
             End If
        Next
        
         For i = 1 To rg2.Rows.Count 'Range pour Inspchris
            If rg2.Cells(i, 3).value = value Then
                TextBox_RDIMS.Invoke(New Callback(AddressOf UpdateUI), TextBox_RDIMS, rg2.Cells(i, 19).value)
                TextBox_RSIG.Invoke(New Callback(AddressOf UpdateUI), TextBox_RSIG, rg2.Cells(i, 18).value)
                TextBox_LocationNAME.Invoke(New Callback(AddressOf UpdateUI), TextBox_LocationNAME, rg2.Cells(i, 4).value)
                TextBox_Type.Invoke(New Callback(AddressOf UpdateUI), TextBox_Type, rg2.Cells(i, 6).value)
                TextBox_ABC.Invoke(New Callback(AddressOf UpdateUI), TextBox_ABC, rg2.Cells(i, 7).value)
                TextBox_Railway.Invoke(New Callback(AddressOf UpdateUI), TextBox_Railway, rg2.Cells(i, 5).value)
                TextBox_issue.Invoke(New Callback(AddressOf UpdateUI), TextBox_issue, rg2.Cells(i, 8).value)
                TextBox_InspFROM.Invoke(New Callback(AddressOf UpdateUI), TextBox_InspFROM, rg2.Cells(i, 9).value)
                TextBox_InspTO.Invoke(New Callback(AddressOf UpdateUI), TextBox_InspTO, rg2.Cells(i, 10).value)
                TextBox_Total_Insp.Invoke(New Callback(AddressOf UpdateUI), TextBox_Total_Insp, rg2.Cells(i, 11).value)
                TextBox_Date.Invoke(New Callback(AddressOf UpdateUI), TextBox_Date, rg2.Cells(i, 12).value)
                TextBox_Year.Invoke(New Callback(AddressOf UpdateUI), TextBox_Year, rg2.Cells(i, 2).value)
                TextBox_Lead_RSI.Invoke(New Callback(AddressOf UpdateUI), TextBox_Lead_RSI, rg2.Cells(i, 15).value)
                TextBox_2nd_RSI.Invoke(New Callback(AddressOf UpdateUI), TextBox_2nd_RSI, rg2.Cells(i, 16).value)
                TextBox_Letterofconcern.Invoke(New Callback(AddressOf UpdateUI), TextBox_Letterofconcern, rg2.Cells(i, 21).value)
                TextBox_Prenotice.Invoke(New Callback(AddressOf UpdateUI), TextBox_Prenotice, rg2.Cells(i, 23).value)
                TextBox_notice_order.Invoke(New Callback(AddressOf UpdateUI), TextBox_notice_order, rg2.Cells(i, 26).value)
                TextBox_NAIAT.Invoke(New Callback(AddressOf UpdateUI), TextBox_NAIAT, rg2.Cells(i, 28).value)
                TextBox_AMP.Invoke(New Callback(AddressOf UpdateUI), TextBox_AMP, rg2.Cells(i, 29).value)
                TextBox_letterofwarning.Invoke(New Callback(AddressOf UpdateUI), TextBox_letterofwarning, rg2.Cells(i, 31).value)
                TextBox_Comments.Invoke(New Callback(AddressOf UpdateUI), TextBox_Comments, rg2.Cells(i, 32).value)
                TextBox_insp_type.Invoke(New Callback(AddressOf UpdateUI), TextBox_insp_type, rg2.Cells(i, 17).value)
                row = row + 1
                Exit For
            End If
            
        Next
         For i = 1 To rg3.Rows.Count 'Range pour Inspjr
            If rg3.Cells(i, 3).value = value Then
                TextBox_RDIMS.Invoke(New Callback(AddressOf UpdateUI), TextBox_RDIMS, rg3.Cells(i, 19).value)
                TextBox_RSIG.Invoke(New Callback(AddressOf UpdateUI), TextBox_RSIG, rg3.Cells(i, 18).value)
                TextBox_LocationNAME.Invoke(New Callback(AddressOf UpdateUI), TextBox_LocationNAME, rg3.Cells(i, 4).value)
                TextBox_Type.Invoke(New Callback(AddressOf UpdateUI), TextBox_Type, rg3.Cells(i, 6).value)
                TextBox_ABC.Invoke(New Callback(AddressOf UpdateUI), TextBox_ABC, rg3.Cells(i, 7).value)
                TextBox_Railway.Invoke(New Callback(AddressOf UpdateUI), TextBox_Railway, rg3.Cells(i, 5).value)
                TextBox_issue.Invoke(New Callback(AddressOf UpdateUI), TextBox_issue, rg3.Cells(i, 8).value)
                TextBox_InspFROM.Invoke(New Callback(AddressOf UpdateUI), TextBox_InspFROM, rg3.Cells(i, 9).value)
                TextBox_InspTO.Invoke(New Callback(AddressOf UpdateUI), TextBox_InspTO, rg3.Cells(i, 10).value)
                TextBox_Total_Insp.Invoke(New Callback(AddressOf UpdateUI), TextBox_Total_Insp, rg3.Cells(i, 11).value)
                TextBox_Date.Invoke(New Callback(AddressOf UpdateUI), TextBox_Date, rg3.Cells(i, 12).value)
                TextBox_Year.Invoke(New Callback(AddressOf UpdateUI), TextBox_Year, rg3.Cells(i, 2).value)
                TextBox_Lead_RSI.Invoke(New Callback(AddressOf UpdateUI), TextBox_Lead_RSI, rg3.Cells(i, 15).value)
                TextBox_2nd_RSI.Invoke(New Callback(AddressOf UpdateUI), TextBox_2nd_RSI, rg3.Cells(i, 16).value)
                TextBox_Letterofconcern.Invoke(New Callback(AddressOf UpdateUI), TextBox_Letterofconcern, rg3.Cells(i, 21).value)
                TextBox_Prenotice.Invoke(New Callback(AddressOf UpdateUI), TextBox_Prenotice, rg3.Cells(i, 23).value)
                TextBox_notice_order.Invoke(New Callback(AddressOf UpdateUI), TextBox_notice_order, rg3.Cells(i, 26).value)
                TextBox_NAIAT.Invoke(New Callback(AddressOf UpdateUI), TextBox_NAIAT, rg3.Cells(i, 28).value)
                TextBox_AMP.Invoke(New Callback(AddressOf UpdateUI), TextBox_AMP, rg3.Cells(i, 29).value)
                TextBox_letterofwarning.Invoke(New Callback(AddressOf UpdateUI), TextBox_letterofwarning, rg3.Cells(i, 31).value)
                TextBox_Comments.Invoke(New Callback(AddressOf UpdateUI), TextBox_Comments, rg3.Cells(i, 32).value)
                TextBox_insp_type.Invoke(New Callback(AddressOf UpdateUI), TextBox_insp_type, rg3.Cells(i, 17).value)
                row = row + 1
                Exit For
            End If
            
        Next
         For i = 1 To rg4.Rows.Count 'Range pour Inspluc
            If rg4.Cells(i, 3).value = value Then
                TextBox_RDIMS.Invoke(New Callback(AddressOf UpdateUI), TextBox_RDIMS, rg4.Cells(i, 19).value)
                TextBox_RSIG.Invoke(New Callback(AddressOf UpdateUI), TextBox_RSIG, rg4.Cells(i, 18).value)
                TextBox_LocationNAME.Invoke(New Callback(AddressOf UpdateUI), TextBox_LocationNAME, rg4.Cells(i, 4).value)
                TextBox_Type.Invoke(New Callback(AddressOf UpdateUI), TextBox_Type, rg4.Cells(i, 6).value)
                TextBox_ABC.Invoke(New Callback(AddressOf UpdateUI), TextBox_ABC, rg4.Cells(i, 7).value)
                TextBox_Railway.Invoke(New Callback(AddressOf UpdateUI), TextBox_Railway, rg4.Cells(i, 5).value)
                TextBox_issue.Invoke(New Callback(AddressOf UpdateUI), TextBox_issue, rg4.Cells(i, 8).value)
                TextBox_InspFROM.Invoke(New Callback(AddressOf UpdateUI), TextBox_InspFROM, rg4.Cells(i, 9).value)
                TextBox_InspTO.Invoke(New Callback(AddressOf UpdateUI), TextBox_InspTO, rg4.Cells(i, 10).value)
                TextBox_Total_Insp.Invoke(New Callback(AddressOf UpdateUI), TextBox_Total_Insp, rg4.Cells(i, 11).value)
                TextBox_Date.Invoke(New Callback(AddressOf UpdateUI), TextBox_Date, rg4.Cells(i, 12).value)
                TextBox_Year.Invoke(New Callback(AddressOf UpdateUI), TextBox_Year, rg4.Cells(i, 2).value)
                TextBox_Lead_RSI.Invoke(New Callback(AddressOf UpdateUI), TextBox_Lead_RSI, rg4.Cells(i, 15).value)
                TextBox_2nd_RSI.Invoke(New Callback(AddressOf UpdateUI), TextBox_2nd_RSI, rg4.Cells(i, 16).value)
                TextBox_Letterofconcern.Invoke(New Callback(AddressOf UpdateUI), TextBox_Letterofconcern, rg4.Cells(i, 21).value)
                TextBox_Prenotice.Invoke(New Callback(AddressOf UpdateUI), TextBox_Prenotice, rg4.Cells(i, 23).value)
                TextBox_notice_order.Invoke(New Callback(AddressOf UpdateUI), TextBox_notice_order, rg4.Cells(i, 26).value)
                TextBox_NAIAT.Invoke(New Callback(AddressOf UpdateUI), TextBox_NAIAT, rg4.Cells(i, 28).value)
                TextBox_AMP.Invoke(New Callback(AddressOf UpdateUI), TextBox_AMP, rg4.Cells(i, 29).value)
                TextBox_letterofwarning.Invoke(New Callback(AddressOf UpdateUI), TextBox_letterofwarning, rg4.Cells(i, 31).value)
                TextBox_Comments.Invoke(New Callback(AddressOf UpdateUI), TextBox_Comments, rg4.Cells(i, 32).value)
                TextBox_insp_type.Invoke(New Callback(AddressOf UpdateUI), TextBox_insp_type, rg4.Cells(i, 17).value)
                row = row + 1
                Exit For
            End If
            
        Next
        For i = 1 To rg5.Rows.Count 'Range pour Inspted
            If rg5.Cells(i, 3).value = value Then
                TextBox_RDIMS.Invoke(New Callback(AddressOf UpdateUI), TextBox_RDIMS, rg5.Cells(i, 19).value)
                TextBox_RSIG.Invoke(New Callback(AddressOf UpdateUI), TextBox_RSIG, rg5.Cells(i, 18).value)
                TextBox_LocationNAME.Invoke(New Callback(AddressOf UpdateUI), TextBox_LocationNAME, rg5.Cells(i, 4).value)
                TextBox_Type.Invoke(New Callback(AddressOf UpdateUI), TextBox_Type, rg5.Cells(i, 6).value)
                TextBox_ABC.Invoke(New Callback(AddressOf UpdateUI), TextBox_ABC, rg5.Cells(i, 7).value)
                TextBox_Railway.Invoke(New Callback(AddressOf UpdateUI), TextBox_Railway, rg5.Cells(i, 5).value)
                TextBox_issue.Invoke(New Callback(AddressOf UpdateUI), TextBox_issue, rg5.Cells(i, 8).value)
                TextBox_InspFROM.Invoke(New Callback(AddressOf UpdateUI), TextBox_InspFROM, rg5.Cells(i, 9).value)
                TextBox_InspTO.Invoke(New Callback(AddressOf UpdateUI), TextBox_InspTO, rg5.Cells(i, 10).value)
                TextBox_Total_Insp.Invoke(New Callback(AddressOf UpdateUI), TextBox_Total_Insp, rg5.Cells(i, 11).value)
                TextBox_Date.Invoke(New Callback(AddressOf UpdateUI), TextBox_Date, rg5.Cells(i, 12).value)
                TextBox_Year.Invoke(New Callback(AddressOf UpdateUI), TextBox_Year, rg5.Cells(i, 2).value)
                TextBox_Lead_RSI.Invoke(New Callback(AddressOf UpdateUI), TextBox_Lead_RSI, rg5.Cells(i, 15).value)
                TextBox_2nd_RSI.Invoke(New Callback(AddressOf UpdateUI), TextBox_2nd_RSI, rg5.Cells(i, 16).value)
                TextBox_Letterofconcern.Invoke(New Callback(AddressOf UpdateUI), TextBox_Letterofconcern, rg5.Cells(i, 21).value)
                TextBox_Prenotice.Invoke(New Callback(AddressOf UpdateUI), TextBox_Prenotice, rg5.Cells(i, 23).value)
                TextBox_notice_order.Invoke(New Callback(AddressOf UpdateUI), TextBox_notice_order, rg5.Cells(i, 26).value)
                TextBox_NAIAT.Invoke(New Callback(AddressOf UpdateUI), TextBox_NAIAT, rg5.Cells(i, 28).value)
                TextBox_AMP.Invoke(New Callback(AddressOf UpdateUI), TextBox_AMP, rg5.Cells(i, 29).value)
                TextBox_letterofwarning.Invoke(New Callback(AddressOf UpdateUI), TextBox_letterofwarning, rg5.Cells(i, 31).value)
                TextBox_Comments.Invoke(New Callback(AddressOf UpdateUI), TextBox_Comments, rg5.Cells(i, 32).value)
                TextBox_insp_type.Invoke(New Callback(AddressOf UpdateUI), TextBox_insp_type, rg5.Cells(i, 17).value)
                row = row + 1
                Exit For
            End If
        Next
    End Sub

Private Sub Rangeloops()

Clearalltextbox()   'Make sure all fields are empty while switching form child to child
TextBox_selectednodes.Text = TreeView_Insp_Records.SelectedNode.Text
Ping_network()
        
'All my loops that you've seen before

end sub

         Private Sub Clearalltextbox()

        txtNodeTextSearch.Text = ""
        TextBox_RDIMS.Text = ""
        TextBox_RSIG.Text = ""
        TextBox_LocationNAME.Text = ""
        TextBox_Railway.Text = ""
        TextBox_Type.Text = ""
        TextBox_ABC.Text = ""
        TextBox_issue.Text = ""
        TextBox_InspFROM.Text = ""
        TextBox_InspTO.Text = ""
        TextBox_Total_Insp.Text = ""
        TextBox_Date.Text = ""
        TextBox_Year.Text = ""
        TextBox_Lead_RSI.Text = ""
        TextBox_2nd_RSI.Text = ""
        TextBox_Letterofconcern.Text = ""
        TextBox_Prenotice.Text = ""
        TextBox_notice_order.Text = ""
        TextBox_AMP.Text = ""
        TextBox_NAIAT.Text = ""
        TextBox_letterofwarning.Text = ""
        TextBox_Comments.Text = ""
        TextBox_selectednodes.Text = ""
        TextBox_insp_type.Text = ""

    End Sub

Alright so here is my code now. EVERYTHING is working fine! I would have never made it that far without your help. Thank you.
When I move in the Treeview from one node to another, the textbox are filled in a more dynamic way. Its much better.
Tho I still need to ask, would it be better to make a Thread or delegate with one loop in each ? Or having 5 would be consider a bit overkill for this?
And finaly, should I make a Thread for clearing all textbox when it comes to clear all their content?
When I move from one childnodes to another in the Treeview ( this is when all textbox = "" ), I can feel like it's still a bit demanding for the program to keep up.
But other then that, I feel I'm almost done.

 

[JohnH]

, I can feel like it's still a bit demanding for the program to keep up.

Look at post 11 if you want to speed things up.

 

[freddyboy]

i have seen post 11. I did not ignored it. I tried but im confused on where and what to change exacly. It doesnt take much as you can see. Could you just show me an exemple with my code in my last post by applying to rg1 ?

 

[JohnH]

I did in post 11. I showed you how to get the values array from rg1, and I showed you how to get a value from the values array. There's nothing more to it. Instead of rg1.Cells(i, 31).value do values(i, 31)

 

[Sheepings]

should I make a Thread for clearing all textbox when it comes to clear all their content?

Yes you should. Because you shouldn't be updating your UI from any method running on your UI thread. That's what makes your app become unresponsive. But I'm afraid threading only takes you so far. It's just the correct way to work with UI controls from another thread or task. Ideally, you should be using a model of data (a class) to track all your objects on your UI and work with that model. It would be your model which would keep your UI updated....anyway, the way you are doing it now is fine for now. After-all, you're still only learning, and I don't want to throw to much at you, as that would just be overwhelming.

Threading is only one small step for added improvements. Real performance will only happen when you start tweaking your code to reduce execution time. Now you know how you should be updating your UI, don't go back to the lazy way of doing it by executing code in your UI thread. This is when you develop a better understanding of code in general and learn the mathematical equations for finding the fastest running syntax...(it comes with experience) But its also important that you understand what the code I gave you does and why it helps. It's discouraging to help you any further because you're not asking how it works or why. When you say :

would it be better to make a Thread or delegate with one loop in each ?

It's important to know what each one does and why one of those is dependent on the other (thread, and delegate). Your misunderstanding becomes clear when you add the word "or" in there.

I see a lot of room for improvement in your above code. To bring post 11 forward for convenience, so we don't have to keep bouncing between pages and to recoup on what was said. What exactly do you not understand here :

Getting values from a sheet range once as array is as fast as getting a single cell value, so if you get 20 values from a range speed can be improved 20 times... or so

Dim values As Object(,) = rg1.Value

Then you can get each one value from array:

Dim value = values(row, column)

Have you tried to adapt what was being described? How many times does the below loop cycle compared to picking a one time variable object form an array?
For i = 1 To rg1.Rows.Count

 

[freddyboy]

I'm confuse as do I need to make those changes in the " Private Sub Rangeloops() " where all my orginal loops are, or the " Private Sub ExecuteWork(byval value as string) " which is the thread, or both of them ?

I've adapted like this to try but I don't see any significant changes in term of speed, it does seem to behave differently when it fills the textbox but other than filling my code of Dim and making it look heavier, I don't think it's improving it?

 Private Sub Rangeloops()

        Clearalltextbox()   'Make sure all fields are empty while switching form child to child
        TextBox_selectednodes.Text = TreeView_Insp_Records.SelectedNode.Text
        Ping_network()

        Dim row As Long
        row = 1
        Dim Inspfred As Worksheet
        Dim Inspchris As Worksheet
        Dim Inspjr As Worksheet
        Dim Inspluc As Worksheet
        Dim Inspted As Worksheet
        Inspfred = Form1.xlWorkBook.Sheets("Inspfred")
        Inspchris = Form1.xlWorkBook.Sheets("Inspchris")
        Inspjr = Form1.xlWorkBook.Sheets("Inspjr")
        Inspluc = Form1.xlWorkBook.Sheets("Inspluc")
        Inspted = Form1.xlWorkBook.Sheets("Inspted")
        Dim rg1 As Range 'Range pour fred
        Dim rg2 As Range 'Range pour chris
        Dim rg3 As Range 'Range pour jr
        Dim rg4 As Range 'Range pour luc
        Dim rg5 As Range 'Range pour ted
        rg1 = Inspfred.Range("C1").CurrentRegion
        rg2 = Inspchris.Range("C1").CurrentRegion
        rg3 = Inspjr.Range("C1").CurrentRegion
        rg4 = Inspluc.Range("C1").CurrentRegion
        rg5 = Inspted.Range("C1").CurrentRegion
       
         Dim i As Long
     
        For i = 1 To rg1.Rows.Count 'Range for Inspfred

            Dim RDIMS_fred As Object(,) = rg1.Value
            Dim RSIG_fred As Object(,) = rg1.Value
            Dim LocationNAME_fred As Object(,) = rg1.Value
            Dim Type_fred As Object(,) = rg1.Value
            Dim ABC_fred As Object(,) = rg1.Value
            Dim Railway_fred As Object(,) = rg1.Value
            Dim issue_fred As Object(,) = rg1.Value
            Dim InspFROM_fred As Object(,) = rg1.Value
            Dim InspTO_fred As Object(,) = rg1.Value
            Dim Total_Insp_fred As Object(,) = rg1.Value
            Dim Date_fred As Object(,) = rg1.Value
            Dim Year_fred As Object(,) = rg1.Value
            Dim Lead_RSI_fred As Object(,) = rg1.Value
            Dim RSI2_fred As Object(,) = rg1.Value
            Dim Letterofconcern_fred As Object(,) = rg1.Value
            Dim Prenotice_fred As Object(,) = rg1.Value
            Dim notice_order_fred As Object(,) = rg1.Value
            Dim NAIAT_fred As Object(,) = rg1.Value
            Dim AMP_fred As Object(,) = rg1.Value
            Dim letterofwarning_fred As Object(,) = rg1.Value
            Dim Comments_fred As Object(,) = rg1.Value
            Dim insp_type_fred As Object(,) = rg1.Value
       
            Dim RDIMS = RDIMS_fred(i, 19)
            Dim RSIG = RSIG_fred(i, 18)
            Dim LocationNAME = LocationNAME_fred(i, 4)
            Dim Type = Type_fred(i, 6)
            Dim ABC = ABC_fred(i, 7)
            Dim Railway = Railway_fred(i, 5)
            Dim issue = issue_fred(i, 8)
            Dim InspFROM = InspFROM_fred(i, 9)
            Dim InspTO = InspTO_fred(i, 10)
            Dim Total_Insp = Total_Insp_fred(i, 11)
            Dim freddate = Date_fred(i, 12)
            Dim Year = Year_fred(i, 2)
            Dim Lead_RSI = Lead_RSI_fred(i, 15)
            Dim RSI2 = RSI2_fred(i, 16)
            Dim Letterofconcern = Letterofconcern_fred(i, 21)
            Dim Prenotice = Prenotice_fred(i, 23)
            Dim notice_order = notice_order_fred(i, 26)
            Dim NAIAT = NAIAT_fred(i, 28)
            Dim AMP = AMP_fred(i, 29)
            Dim letterofwarning = letterofwarning_fred(i, 31)
            Dim Comments = Comments_fred(i, 32)
            Dim insp_type = insp_type_fred(i, 17)

If rg1.Cells(i, 3).value = TreeView_Insp_Records.SelectedNode.Text Then

                TextBox_RDIMS.Text = RDIMS
                TextBox_RSIG.Text = RSIG
                TextBox_LocationNAME.Text = LocationNAME
                TextBox_Type.Text = Type
                TextBox_ABC.Text = ABC
                TextBox_Railway.Text = Railway
                TextBox_issue.Text = issue
                TextBox_InspFROM.Text = InspFROM
                TextBox_InspTO.Text = InspTO
                TextBox_Total_Insp.Text = Total_Insp
                TextBox_Date.Text = freddate
                TextBox_Year.Text = Year
                TextBox_Lead_RSI.Text = Lead_RSI
                TextBox_2nd_RSI.Text = RSI2
                TextBox_Letterofconcern.Text = Letterofconcern
                TextBox_Prenotice.Text = Prenotice
                TextBox_notice_order.Text = notice_order
                TextBox_NAIAT.Text = NAIAT
                TextBox_AMP.Text = AMP
                TextBox_letterofwarning.Text = letterofwarning
                TextBox_Comments.Text = Comments
                TextBox_insp_type.Text = insp_type
                row = row + 1
                Exit For
            End If
        Next
       
      
 For i = 1 To rg2.Rows.Count 'Range for Inspchris

Dim RDIMS_chris As Object(,) = rg2.Value
Dim RSIG_chris As Object(,) = rg2.Value
Dim LocationNAME_chris As Object(,) = rg2.Value
Dim Type_chris As Object(,) = rg2.Value
Dim ABC_chris As Object(,) = rg2.Value
Dim Railway_chris As Object(,) = rg2.Value
Dim issue_chris As Object(,) = rg2.Value
Dim InspFROM_chris As Object(,) = rg2.Value
Dim InspTO_chris As Object(,) = rg2.Value
Dim Total_Insp_chris As Object(,) = rg2.Value
Dim Date_chris As Object(,) = rg2.Value
Dim Year_chris As Object(,) = rg2.Value
Dim Lead_RSI_chris As Object(,) = rg2.Value
Dim RSI2_chris As Object(,) = rg2.Value
Dim Letterofconcern_chris As Object(,) = rg2.Value
Dim Prenotice_chris As Object(,) = rg2.Value
Dim notice_order_chris As Object(,) = rg2.Value
Dim NAIAT_chris As Object(,) = rg2.Value
Dim AMP_chris As Object(,) = rg2.Value
Dim letterofwarning_chris As Object(,) = rg2.Value
Dim Comments_chris As Object(,) = rg2.Value
Dim insp_type_chris As Object(,) = rg2.Value
Dim RDIMS = RDIMS_chris(i, 19)
Dim RSIG = RSIG_chris(i, 18)
Dim LocationNAME = LocationNAME_chris(i, 4)

Dim Type = Type_chris(i, 6)
Dim ABC = ABC_chris(i, 7)
Dim Railway = Railway_chris(i, 5)
Dim issue = issue_chris(i, 8)
Dim InspFROM = InspFROM_chris(i, 9)
Dim InspTO = InspTO_chris(i, 10)
Dim Total_Insp = Total_Insp_chris(i, 11)
Dim freddate = Date_chris(i, 12)
Dim Year = Year_chris(i, 2)
Dim Lead_RSI = Lead_RSI_chris(i, 15)
Dim RSI2 = RSI2_chris(i, 16)
Dim Letterofconcern = Letterofconcern_chris(i, 21)
Dim Prenotice = Prenotice_chris(i, 23)
Dim notice_order = notice_order_chris(i, 26)
Dim NAIAT = NAIAT_chris(i, 28)
Dim AMP = AMP_chris(i, 29)
Dim letterofwarning = letterofwarning_chris(i, 31)
Dim Comments = Comments_chris(i, 32)
Dim insp_type = insp_type_chris(i, 17)

            If rg2.Cells(i, 3).value = TreeView_Insp_Records.SelectedNode.Text Then 

TextBox_RDIMS.Text = RDIMS
TextBox_RSIG.Text = RSIG
TextBox_LocationNAME.Text = LocationNAME
TextBox_Type.Text = Type
TextBox_ABC.Text = ABC
TextBox_Railway.Text = Railway
TextBox_issue.Text = issue
TextBox_InspFROM.Text = InspFROM
TextBox_InspTO.Text = InspTO
TextBox_Total_Insp.Text = Total_Insp
TextBox_Date.Text = freddate
TextBox_Year.Text = Year
TextBox_Lead_RSI.Text = Lead_RSI
TextBox_2nd_RSI.Text = RSI2
TextBox_Letterofconcern.Text = Letterofconcern
TextBox_Prenotice.Text = Prenotice
TextBox_notice_order.Text = notice_order
TextBox_NAIAT.Text = NAIAT
TextBox_AMP.Text = AMP
TextBox_letterofwarning.Text = letterofwarning
TextBox_Comments.Text = Comments
TextBox_insp_type.Text = insp_type

Exit For
End If
Next

 

[JohnH]

No no no.

Dim values As Object(,) = rg1.Value

That's ALL values in the range. You get this once from spreadsheet for each of your CurrentRegions.

After that you can get each value from the array and put it into your controls.

 

[freddyboy]

Good coding comes with experience. Experience, well that comes with bad coding
Hope this is right:

 Dim i As Long

        For i = 1 To rg1.Rows.Count 'Range for Inspfred

            Dim values As Object(,) = rg1.Value

            If rg1.Cells(i, 3).value = TreeView_Insp_Records.SelectedNode.Text Then ' Si les valeurs colonne C = node sélectionner dans treeview
                TextBox_RDIMS.Text = values(i, 19)
                TextBox_RSIG.Text = values(i, 18)
                TextBox_LocationNAME.Text = values(i, 4)
                TextBox_Type.Text = values(i, 6)
                TextBox_ABC.Text = values(i, 7)
                TextBox_Railway.Text = values(i, 5)
                TextBox_issue.Text = values(i, 8)
                TextBox_InspFROM.Text = values(i, 9)
                TextBox_InspTO.Text = values(i, 10)
                TextBox_Total_Insp.Text = values(i, 11)
                TextBox_Date.Text = values(i, 12)
                TextBox_Year.Text = values(i, 2)
                TextBox_Lead_RSI.Text = values(i, 15)
                TextBox_2nd_RSI.Text = values(i, 16)
                TextBox_Letterofconcern.Text = values(i, 21)
                TextBox_Prenotice.Text = values(i, 23)
                TextBox_notice_order.Text = values(i, 26)
                TextBox_NAIAT.Text = values(i, 28)
                TextBox_AMP.Text = values(i, 29)
                TextBox_letterofwarning.Text = values(i, 31)
                TextBox_Comments.Text = values(i, 32)
                TextBox_insp_type.Text = values(i, 17)
                row = row + 1
                Exit For
            End If
        Next

        For i = 1 To rg2.Rows.Count 'Range for Inspchris

            Dim values As Object(,) = rg2.Value

            If rg2.Cells(i, 3).value = TreeView_Insp_Records.SelectedNode.Text Then ' Si les valeurs colonne C = node sélectionner dans treeview
                TextBox_RDIMS.Text = values(i, 19)
                TextBox_RSIG.Text = values(i, 18)
                TextBox_LocationNAME.Text = values(i, 4)
                TextBox_Type.Text = values(i, 6)
                TextBox_ABC.Text = values(i, 7)
                TextBox_Railway.Text = values(i, 5)
                TextBox_issue.Text = values(i, 8)
                TextBox_InspFROM.Text = values(i, 9)
                TextBox_InspTO.Text = values(i, 10)
                TextBox_Total_Insp.Text = values(i, 11)
                TextBox_Date.Text = values(i, 12)
                TextBox_Year.Text = values(i, 2)
                TextBox_Lead_RSI.Text = values(i, 15)
                TextBox_2nd_RSI.Text = values(i, 16)
                TextBox_Letterofconcern.Text = values(i, 21)
                TextBox_Prenotice.Text = values(i, 23)
                TextBox_notice_order.Text = values(i, 26)
                TextBox_NAIAT.Text = values(i, 28)
                TextBox_AMP.Text = values(i, 29)
                TextBox_letterofwarning.Text = values(i, 31)
                TextBox_Comments.Text = values(i, 32)
                TextBox_insp_type.Text = values(i, 17)
                Exit For
            End If
        Next

        For i = 1 To rg3.Rows.Count 'Range for Inspjr

            Dim values As Object(,) = rg3.Value

            If rg3.Cells(i, 3).value = TreeView_Insp_Records.SelectedNode.Text Then ' Si les valeurs colonne C = node sélectionner dans treeview
                TextBox_RDIMS.Text = values(i, 19)
                TextBox_RSIG.Text = values(i, 18)
                TextBox_LocationNAME.Text = values(i, 4)
                TextBox_Type.Text = values(i, 6)
                TextBox_ABC.Text = values(i, 7)
                TextBox_Railway.Text = values(i, 5)
                TextBox_issue.Text = values(i, 8)
                TextBox_InspFROM.Text = values(i, 9)
                TextBox_InspTO.Text = values(i, 10)
                TextBox_Total_Insp.Text = values(i, 11)
                TextBox_Date.Text = values(i, 12)
                TextBox_Year.Text = values(i, 2)
                TextBox_Lead_RSI.Text = values(i, 15)
                TextBox_2nd_RSI.Text = values(i, 16)
                TextBox_Letterofconcern.Text = values(i, 21)
                TextBox_Prenotice.Text = values(i, 23)
                TextBox_notice_order.Text = values(i, 26)
                TextBox_NAIAT.Text = values(i, 28)
                TextBox_AMP.Text = values(i, 29)
                TextBox_letterofwarning.Text = values(i, 31)
                TextBox_Comments.Text = values(i, 32)
                TextBox_insp_type.Text = values(i, 17)
                Exit For
            End If
        Next

        For i = 1 To rg4.Rows.Count 'Range for Inspluc

            Dim values As Object(,) = rg4.Value

            If rg4.Cells(i, 3).value = TreeView_Insp_Records.SelectedNode.Text Then ' Si les valeurs colonne C = node sélectionner dans treeview
                TextBox_RDIMS.Text = values(i, 19)
                TextBox_RSIG.Text = values(i, 18)
                TextBox_LocationNAME.Text = values(i, 4)
                TextBox_Type.Text = values(i, 6)
                TextBox_ABC.Text = values(i, 7)
                TextBox_Railway.Text = values(i, 5)
                TextBox_issue.Text = values(i, 8)
                TextBox_InspFROM.Text = values(i, 9)
                TextBox_InspTO.Text = values(i, 10)
                TextBox_Total_Insp.Text = values(i, 11)
                TextBox_Date.Text = values(i, 12)
                TextBox_Year.Text = values(i, 2)
                TextBox_Lead_RSI.Text = values(i, 15)
                TextBox_2nd_RSI.Text = values(i, 16)
                TextBox_Letterofconcern.Text = values(i, 21)
                TextBox_Prenotice.Text = values(i, 23)
                TextBox_notice_order.Text = values(i, 26)
                TextBox_NAIAT.Text = values(i, 28)
                TextBox_AMP.Text = values(i, 29)
                TextBox_letterofwarning.Text = values(i, 31)
                TextBox_Comments.Text = values(i, 32)
                TextBox_insp_type.Text = values(i, 17)
                Exit For
            End If
        Next

        For i = 1 To rg5.Rows.Count 'Range for Inspted

            Dim values As Object(,) = rg5.Value

            If rg5.Cells(i, 3).value = TreeView_Insp_Records.SelectedNode.Text Then ' Si les valeurs colonne C = node sélectionner dans treeview
                TextBox_RDIMS.Text = values(i, 19)
                TextBox_RSIG.Text = values(i, 18)
                TextBox_LocationNAME.Text = values(i, 4)
                TextBox_Type.Text = values(i, 6)
                TextBox_ABC.Text = values(i, 7)
                TextBox_Railway.Text = values(i, 5)
                TextBox_issue.Text = values(i, 8)
                TextBox_InspFROM.Text = values(i, 9)
                TextBox_InspTO.Text = values(i, 10)
                TextBox_Total_Insp.Text = values(i, 11)
                TextBox_Date.Text = values(i, 12)
                TextBox_Year.Text = values(i, 2)
                TextBox_Lead_RSI.Text = values(i, 15)
                TextBox_2nd_RSI.Text = values(i, 16)
                TextBox_Letterofconcern.Text = values(i, 21)
                TextBox_Prenotice.Text = values(i, 23)
                TextBox_notice_order.Text = values(i, 26)
                TextBox_NAIAT.Text = values(i, 28)
                TextBox_AMP.Text = values(i, 29)
                TextBox_letterofwarning.Text = values(i, 31)
                TextBox_Comments.Text = values(i, 32)
                TextBox_insp_type.Text = values(i, 17)
                Exit For
            End If

 

[JohnH]

No, you still get values from sheet many times inside the loop. It can also be greatly improved by using the array in lookup:

Dim values As Object(,) = rg1.Value
For i = 1 To values.GetUpperBound(0) 'rows
    If values(i, 3) = TreeView_Insp_Records.SelectedNode.Text Then
        TextBox_RDIMS.Text = values(i, 19)

Notice rg1.value is the last time to talk to sheet, inside loop just use the array.