VB.net/Excel- "Backwards" tab index For Each iteration with textboxes.

Question

Hi there,

I have a form with 3 textboxes and 1 button.

textbox1 has tab index 0, and it's text = 1

textbox2 has tab index 1, and it's text = 2

textbox3 has tab index 2, and it's text = 3

I want to iterate thru the textboxes and place their values into cells so that...

range("A1").value = txtbox1.text (ie: A1 = "1") range("A2").value = txtbox2.text (ie: A2 = "2") range("A3").value = txtbox3.text (ie: A3 = "3")

but what I am getting is...

range("A1").value = txtbox1.text (ie: A1 = "3") range("A2").value = txtbox2.text (ie: A2 = "2") range("A3").value = txtbox3.text (ie: A3 = "1")

I have tried reversing the tab index for the text boxes, but it doesn't change the "backwards iteration".

Is there something I can do to change this so that the loop runs from lowest tab index to highest?

Thanks!

Public Class Form1

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

  Dim objExcel As New Microsoft.Office.Interop.Excel.Application 'Declaring the object.
  objExcel.Visible = True 'Setting Excel to visible.

  Dim cntrl As Control

  With objExcel
     .Workbooks.Add() 'Adding a workbook.
     .Range("A1").Select() 'Selecting cell A1.
  End With


  'Form contains 3 text boxes, with one number in each (1,2,3), and one button to fire the code in this sub.

  For Each cntrl In Me.Controls 'For every control on the form...
     If TypeOf (cntrl) Is TextBox Then 'If the control is a textbox, then...
        With objExcel
           .ActiveCell.Value = cntrl.Text 'place the control's text in the active cell and...
           .ActiveCell.Offset(1, 0).Activate() 'offset down one row.
        End With
     End If 'If the control is not a textbox (if it's the button), do nothing.
  Next 'Go to the next control.

  objExcel = Nothing 'Release the object.
  GC.Collect() 'Clean up.

End Sub End Class

Answer 1

Sounds like it may be the way Excel is iterating over the controls. Have you tried this to see what the output is?

  Dim objExcel As New Microsoft.Office.Interop.Excel.Application 'Declaring the object.
  objExcel.Visible = True 'Setting Excel to visible.

  Dim cntrl As Control

  With objExcel
     .Workbooks.Add() 'Adding a workbook.
     .Range("A3").Select() 'Selecting cell A3.
  End With


  'Form contains 3 text boxes, with one number in each (1,2,3), and one button to fire the code in this sub.

  For Each cntrl In Me.Controls 'For every control on the form...
     If TypeOf (cntrl) Is TextBox Then 'If the control is a textbox, then...
        With objExcel
           .ActiveCell.Value = cntrl.Text 'place the control's text in the active cell and...
           .ActiveCell.Offset(-1, 0).Activate() 'offset up one row.
        End With
     End If 'If the control is not a textbox (if it's the button), do nothing.
  Next 'Go to the next control.

  objExcel = Nothing 'Release the object.
  GC.Collect() 'Clean up.

Answer 2

Using a For Each loop implies that you don't care what order you are doing them in.

What I would do is use the "tag" property of the control to hold the row number you'd like the answer in:

For Each cntrl In Me.Controls 
  If TypeOf (cntrl) Is TextBox Then 
    objExcel.ActiveSheet.Cells(cntrl.Tag, 1).Value = cntrl.Text 
  End If 
Next 

You could also add a hidden panel on the form that contains only your key textboxes, then use a traditional FOR loop instead of a 'For Each':

Dim i as integer
Dim myBox as textBox
For i = 1 to 3
    set myBox = Me.Panel1.Controls(i)
    objExcel.ActiveSheet.Cells(myBox.Tag, 1).Value = myBox.Text 
Next i

It should go through in Tab order. You can even skip the typecheck, because you already know what it is.