Create Multiple Textboxes and labels in different forms - Page 2

[ACE--]

Question #3: These 200 Textboxes / labels text or values to be populated into Excel sheet as headers.

[ACE--]

By the below is the code I am using currently. But Every Time I can't open VS and do the programming right. I / we need to find a fix for this.

Ok. Like I said I am using Panel for creating this text boxes at runtime.

Code:

 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Panel1.Controls.Remove(l)
        Panel1.Controls.Remove(t)
    Dim R As Integer
    Dim t As TextBox
    Dim l As Label

        R = TB.Text
        For Row As Integer = 1 To R
            For Col As Integer = 1 To 1
                t = New TextBox
                t.Size = New Drawing.Size(240, 20)
                t.Location = New Point(220 * Col, 30 * Row)
                t.Name = "Textbox" & Row
                t.Text = "Textbox" & Row
                Panel1.Controls.Add(t)

                l = New Label
                l.Size = New Drawing.Size(240, 20)
                l.Location = New Point(20 * Col, 30 * Row)
                l.Font = New Font(l.Font, FontStyle.Bold)
                l.Name = "Label" & Row
                l.Text = "LB" & Row
                Panel1.Controls.Add(l)

            Next
        Next
End Sub

[Ancient Dragon]

:Question #3: These 200 Textboxes / labels text or values to be populated into Excel sheet as headers.

As I mentioned a week ago all you have to do is write the data in the text boxes to a comma deliminated text file, then Excel can easily import the file into a worksheet.

But Every Time I can't open VS and do the programming right.

Sorry, but I don't understand that -- I thought you said you had all that fixed, in this post.

[ACE--]

Can you share the code for that please.

For Example I have created 200 Text Boxes at runtime I want these text boxes content to populated in excel sheet. How I can do that.

As I said time to time # of boxes will be changing. Minimum 1 Max 256.

Can you help me with that.

[Minimalist]

I am using vb express 13 and office 7.
I have a form and one button to open Excel and populate some cells.
The following code I have modified some and shortenend, but it works fine.

Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop


Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim oXL As Application
        Dim oWB As Microsoft.Office.Interop.Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Dim oRng As Excel.Range

        ' Start Excel and get Application object.

        Dim xlApp As New Microsoft.Office.Interop.Excel.Application

        xlApp.Visible = True

        ' Get a new workbook.
        oWB = xlApp.Workbooks.Add
        oSheet = oWB.ActiveSheet

        ' Add table headers going cell by cell.
        oSheet.Cells(1, 1).Value = "First Name"
        oSheet.Cells(1, 2).Value = "Last Name"
        oSheet.Cells(1, 3).Value = "Full Name"
        oSheet.Cells(1, 4).Value = "Salary"

        ' Format A1:D1 as bold, vertical alignment = center.
        With oSheet.Range("A1", "D1")
            .Font.Bold = True
            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
        End With

        ' Create an array to set multiple values at once.
        Dim saNames(5, 2) As String
        saNames(0, 0) = "John"
        saNames(0, 1) = "Smith"
        saNames(1, 0) = "Tom"
        saNames(1, 1) = "Brown"
        saNames(2, 0) = "Sue"
        saNames(2, 1) = "Thomas"
        saNames(3, 0) = "Jane"

        saNames(3, 1) = "Jones"
        saNames(4, 0) = "Adam"
        saNames(4, 1) = "Johnson"

        ' Fill A2:B6 with an array of values (First and Last Names).
        oSheet.Range("A2", "B6").Value = saNames

        ' Fill C2:C6 with a relative formula (=A2 & " " & B2).
        oRng = oSheet.Range("C2", "C6")
        oRng.Formula = "=A2 & "" "" & B2"

        ' Fill D2:D6 with a formula(=RAND()*100000) and apply format.
        oRng = oSheet.Range("D2", "D6")
        oRng.Formula = "=RAND()*100000"
        oRng.NumberFormat = "$0.00"

        ' AutoFit columns A:D.
        oRng = oSheet.Range("A1", "D1")
        oRng.EntireColumn.AutoFit()
        Exit Sub
End Class

The links I have used are:
http://support.microsoft.com/kb/301982/de
http://social.msdn.microsoft.com/Forums/en-US/cab76ead-2166-4394-8a4b-5b1d0cf5c826/open-and-write-to-an-excel-file-from-vbnet-form
http://social.msdn.microsoft.com/Forums/en-US/9a70f14f-2c95-43d2-905f-e09a8550c6e8/excelstart-with-vbnet-with-all-addins

[ACE--]

@ Minimalist. I can understand you code but my requirement is completely different from what you have suggested.

@ Ancient Dragon: As you stated I using this writing this data different formats. But first I need to get this data into Excel.

Now Coming to my questions.

Question #1: If I enter 200 value in Form3 Texbox.1 And button1 click and then next I want to create 200 Textboxes in the form3. I already the code but it is not working. It is creating the text boxes but box to box distance is enlarging. How can I maintain same distance to each textbox.

Question #2: Whatever the input I am giving in Textbox.1 same number of lables should be created in form1. How can I do it.

Fixed by my self: I have created this textboxes and labels inside a panel. Now there no problem for now.

Question #3: These 200 Textboxes / labels text or values to be populated into Excel sheet as headers.

Still waiting for the resolution. If anyone need more detailed info pleae let me know.

Thanks to Reverend Jim. His code helped me a lot.

[Reverend Jim]

Try creating a form with one button in the top left. Set the form AutoScroll to True and use this code

Public Class Form1

    Const ROWS As Integer = 100     'number of rows         
    Const COLS As Integer = 3       'number of columns      

    Const BOXW As Integer = 100     'textbox width          
    Const BOXH As Integer = 20      'textbox height         
    Const BOXM As Integer = 3       'margin between boxes   

    Private TextBoxes(ROWS, COLS) As TextBox

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        For row As Integer = 1 To ROWS
            For col As Integer = 1 To COLS

                Dim xpos As Integer = 10 + (col - 1) * (BOXW + BOXM)
                Dim ypos As Integer = 50 + (row - 1) * (BOXH + BOXM)

                Dim newbox As New TextBox
                newbox.Size = New Drawing.Size(BOXW, BOXH)
                newbox.Location = New Point(xpos, ypos)
                newbox.Tag = {row, col}
                newbox.Text = "Textbox(" & row & ", " & col & ")"
                AddHandler newbox.TextChanged, AddressOf TextBox_TextChanged
                TextBoxes(row, col) = newbox
                Me.Controls.Add(newbox)

            Next
        Next

    End Sub

    Private Sub TextBox_TextChanged(sender As System.Object, e As System.EventArgs)

        Dim tbx As TextBox = sender
        Me.Text = "Box(" & tbx.Tag(0) & ", " & tbx.Tag(1) & ") is " & tbx.Text

    End Sub

End Class

By storing the textbox references in an array you get easier access later on. By storing the array {row, col} in the tag you can easily back-reference the coordinates. Also, by creating the Consts for the size and number of boxes you can change the layout more easily. You might even create Consts for the start position of the upper left box as well.

[ACE--]

Hi Reverend Jim,

Thanks for the code anyway. Can you please suggest code for updated this textbox values in Excel as .xls format.

Below is my code for creating textboxes. I will try your code by the way.

Now I want to populate this Label Text as Excel Header (1st Row)

Dim R is form1.textbox.text

Dim C As Integer
    Dim R As Integer 
    Dim t As TextBox
    Dim l As Label


Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Panel1.Controls.Remove(l)
        Panel1.Controls.Remove(t)

        R = TB.Text
        For Row As Integer = 1 To R
            For Col As Integer = 1 To 1
                t = New TextBox
                t.Size = New Drawing.Size(240, 20)
                t.Location = New Point(220 * Col, 30 * Row)
                t.Name = "Textbox" & Row
                t.Text = "Textbox" & Row
                Panel1.Controls.Add(t)

                l = New Label
                l.Size = New Drawing.Size(240, 20)
                l.Location = New Point(20 * Col, 30 * Row)
                l.Font = New Font(l.Font, FontStyle.Bold)
                l.Name = "Label" & Row
                l.Text = "LB" & Row
                Panel1.Controls.Add(l)

            Next
        Next

        Try
        Catch ex As InvalidCastException
            Exit Sub
        End Try

    End Sub

[Reverend Jim]

I don't understand what you are asking.

[ACE--]

Label1.text = sheet1 A1
Label2.text = sheet1 B1
Label3.text = sheet1 C1
and so on..

At runtime I might create what ever the amount of Labeles I am creating those labeles text to be populated into above formats.

Is there code for that. I have tried but. Not able to find the correct loop for that.

Can you please help me out.

[Ancient Dragon]

textbox values in Excel as .xls format

I mentioned this before -- if you want to write directly into xls format you have to use Office Autimation. I know VB.NET 2012 and 2013 easily does that, and in that case there is no need for all those text boxes. I even posted a picture of that in this thread.

[Reverend Jim]

You can find an example of using Excel from vb.net in the code snippets section here