data entry to next blank TextBox

I am having trouble with a program I am writing for work. I want to be
able to allow users to input cost codes associated with a specific
task, so I have a userform with a single text box in it, and 2 buttons
that say "ok and add another" and "index." Here are my two problems:

1) I want the "ok and add another" button to keep adding text boxes
underneath the original one. I also want the new textbox VALUE to be
entered into cells in "sheet3" each time the button is clicked, but I
can't figure out how to extract text from the text boxes because there
can be any number of text boxes shown (up to 20), and I don't know how
to leave the text box NAME as a variable that depends on the number of
text boxes present.


2) The "INDEX" button takes the user to "sheet2" which shows an index
of all the cost codes available. I want the user to be able to click on
a cost code, and have that code entered into the next blank text box on
the userform, but I have no idea how to do this.


Sorry, I am extremely new at all this and I have just been teaching
myself along the way.....any help would be GREATLY APPRECIATED!

Thanks,
Cal

Okay I got some of it to work out. So far, the following code will
allow the user to keep adding text boxes and entering numbers, but I
still don't know how to use the index sheet to insert text into the
next blank textbox.

I am also having problems getting the code to enter the TextBox values
into the next BLANK cell down. So, if the user uses 8 textboxes, I need
those values to be entered into cells A1:A8

Here it is:

Option Explicit

Private Sub CodeRequestADD_Click()
Sheets("sheet3").Activate
Dim ctl As Control
Dim txtbox_top As Integer, txtbox_left As Integer, _
txtbox_height As Integer, txtbox_width As Integer, _
txtbox_count As Integer

txtbox_top = 0
txtbox_left = 10 ' something to start with
txtbox_height = 10
txtbox_width = 20
txtbox_count = 0

For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
If ctl.Top > txtbox_top Then
With ctl
txtbox_top = .Top
txtbox_left = .Left
txtbox_height = .Height
txtbox_width = .Width
End With
End If
txtbox_count = txtbox_count + 1
End If
Next ctl

Set ctl = Me.Controls.Add("Forms.TextBox.1", "TextBox" & txtbox_count
+ 1)
With ctl
.Top = txtbox_top + 25
.Left = txtbox_left
.Height = txtbox_height
.Width = txtbox_width
End With
With CodeRequest
.Height = CodeRequest.Height + 25
.CodeRequestADD.Top = .CodeRequestADD.Top + 25
.INDEX.Top = .INDEX.Top + 25
.CodeRequestOK.Top = .CodeRequestOK.Top + 25
.Image1.Top = .Image1.Top + 25
End With
Sheets("sheet3").Range("A1") = CodeRequest.Controls("TextBox" &
txtbox_count).Text

End Sub


You'll see that I simply use Range ("A1") at the bottom.....that's just
because I don't know how to use code to find the first blank cell, and
enter a textbox value into it.

Thanks!
Cal

Hi,

Sheets("sheet3").Range("A" & txtbox_count) = CodeRequest.Controls("TextBox"
& txtbox_count).Text

Will add data to cells A1 to An corresponding to "TexBox1 to TextBoxn"

With regard to your index sheet, you might consider adding a combo box
containing your cost codes rather selecting from your worksheet. Selectiion
from the combobox can be placed in the next textbox.

HTH

"[email protected]" wrote:

> Okay I got some of it to work out. So far, the following code will
> allow the user to keep adding text boxes and entering numbers, but I
> still don't know how to use the index sheet to insert text into the
> next blank textbox.
>
> I am also having problems getting the code to enter the TextBox values
> into the next BLANK cell down. So, if the user uses 8 textboxes, I need
> those values to be entered into cells A1:A8
>
> Here it is:
>
> Option Explicit
>
> Private Sub CodeRequestADD_Click()
> Sheets("sheet3").Activate
> Dim ctl As Control
> Dim txtbox_top As Integer, txtbox_left As Integer, _
> txtbox_height As Integer, txtbox_width As Integer, _
> txtbox_count As Integer
>
> txtbox_top = 0
> txtbox_left = 10 ' something to start with
> txtbox_height = 10
> txtbox_width = 20
> txtbox_count = 0
>
> For Each ctl In Me.Controls
> If TypeName(ctl) = "TextBox" Then
> If ctl.Top > txtbox_top Then
> With ctl
> txtbox_top = .Top
> txtbox_left = .Left
> txtbox_height = .Height
> txtbox_width = .Width
> End With
> End If
> txtbox_count = txtbox_count + 1
> End If
> Next ctl
>
> Set ctl = Me.Controls.Add("Forms.TextBox.1", "TextBox" & txtbox_count
> + 1)
> With ctl
> .Top = txtbox_top + 25
> .Left = txtbox_left
> .Height = txtbox_height
> .Width = txtbox_width
> End With
> With CodeRequest
> .Height = CodeRequest.Height + 25
> .CodeRequestADD.Top = .CodeRequestADD.Top + 25
> .INDEX.Top = .INDEX.Top + 25
> .CodeRequestOK.Top = .CodeRequestOK.Top + 25
> .Image1.Top = .Image1.Top + 25
> End With
> Sheets("sheet3").Range("A1") = CodeRequest.Controls("TextBox" &
> txtbox_count).Text
>
> End Sub
>
>
> You'll see that I simply use Range ("A1") at the bottom.....that's just
> because I don't know how to use code to find the first blank cell, and
> enter a textbox value into it.
>
> Thanks!
> Cal
>
>

After much frustration I figured it out. Here's how it begins.

Private Sub TextBox1_Change()
Worksheets("Curve").Shapes("Curve Line No. 1").Select
Selection.Characters.Text = UserForm2.TextBox1.Text
End Sub


"Toppers" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Sheets("sheet3").Range("A" & txtbox_count) =
> CodeRequest.Controls("TextBox"
> & txtbox_count).Text
>
> Will add data to cells A1 to An corresponding to "TexBox1 to TextBoxn"
>
> With regard to your index sheet, you might consider adding a combo box
> containing your cost codes rather selecting from your worksheet.
> Selectiion
> from the combobox can be placed in the next textbox.
>
> HTH
>
> "[email protected]" wrote:
>
>> Okay I got some of it to work out. So far, the following code will
>> allow the user to keep adding text boxes and entering numbers, but I
>> still don't know how to use the index sheet to insert text into the
>> next blank textbox.
>>
>> I am also having problems getting the code to enter the TextBox values
>> into the next BLANK cell down. So, if the user uses 8 textboxes, I need
>> those values to be entered into cells A1:A8
>>
>> Here it is:
>>
>> Option Explicit
>>
>> Private Sub CodeRequestADD_Click()
>> Sheets("sheet3").Activate
>> Dim ctl As Control
>> Dim txtbox_top As Integer, txtbox_left As Integer, _
>> txtbox_height As Integer, txtbox_width As Integer, _
>> txtbox_count As Integer
>>
>> txtbox_top = 0
>> txtbox_left = 10 ' something to start with
>> txtbox_height = 10
>> txtbox_width = 20
>> txtbox_count = 0
>>
>> For Each ctl In Me.Controls
>> If TypeName(ctl) = "TextBox" Then
>> If ctl.Top > txtbox_top Then
>> With ctl
>> txtbox_top = .Top
>> txtbox_left = .Left
>> txtbox_height = .Height
>> txtbox_width = .Width
>> End With
>> End If
>> txtbox_count = txtbox_count + 1
>> End If
>> Next ctl
>>
>> Set ctl = Me.Controls.Add("Forms.TextBox.1", "TextBox" & txtbox_count
>> + 1)
>> With ctl
>> .Top = txtbox_top + 25
>> .Left = txtbox_left
>> .Height = txtbox_height
>> .Width = txtbox_width
>> End With
>> With CodeRequest
>> .Height = CodeRequest.Height + 25
>> .CodeRequestADD.Top = .CodeRequestADD.Top + 25
>> .INDEX.Top = .INDEX.Top + 25
>> .CodeRequestOK.Top = .CodeRequestOK.Top + 25
>> .Image1.Top = .Image1.Top + 25
>> End With
>> Sheets("sheet3").Range("A1") = CodeRequest.Controls("TextBox" &
>> txtbox_count).Text
>>
>> End Sub
>>
>>
>> You'll see that I simply use Range ("A1") at the bottom.....that's just
>> because I don't know how to use code to find the first blank cell, and
>> enter a textbox value into it.
>>
>> Thanks!
>> Cal
>>
>>

Disreguard, I posted to the wrong message.

"Joel Mills" <[email protected]> wrote in message
news:[email protected]...
> After much frustration I figured it out. Here's how it begins.
>
> Private Sub TextBox1_Change()
> Worksheets("Curve").Shapes("Curve Line No. 1").Select
> Selection.Characters.Text = UserForm2.TextBox1.Text
> End Sub
>
>
> "Toppers" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Sheets("sheet3").Range("A" & txtbox_count) =
>> CodeRequest.Controls("TextBox"
>> & txtbox_count).Text
>>
>> Will add data to cells A1 to An corresponding to "TexBox1 to TextBoxn"
>>
>> With regard to your index sheet, you might consider adding a combo box
>> containing your cost codes rather selecting from your worksheet.
>> Selectiion
>> from the combobox can be placed in the next textbox.
>>
>> HTH
>>
>> "[email protected]" wrote:
>>
>>> Okay I got some of it to work out. So far, the following code will
>>> allow the user to keep adding text boxes and entering numbers, but I
>>> still don't know how to use the index sheet to insert text into the
>>> next blank textbox.
>>>
>>> I am also having problems getting the code to enter the TextBox values
>>> into the next BLANK cell down. So, if the user uses 8 textboxes, I need
>>> those values to be entered into cells A1:A8
>>>
>>> Here it is:
>>>
>>> Option Explicit
>>>
>>> Private Sub CodeRequestADD_Click()
>>> Sheets("sheet3").Activate
>>> Dim ctl As Control
>>> Dim txtbox_top As Integer, txtbox_left As Integer, _
>>> txtbox_height As Integer, txtbox_width As Integer, _
>>> txtbox_count As Integer
>>>
>>> txtbox_top = 0
>>> txtbox_left = 10 ' something to start with
>>> txtbox_height = 10
>>> txtbox_width = 20
>>> txtbox_count = 0
>>>
>>> For Each ctl In Me.Controls
>>> If TypeName(ctl) = "TextBox" Then
>>> If ctl.Top > txtbox_top Then
>>> With ctl
>>> txtbox_top = .Top
>>> txtbox_left = .Left
>>> txtbox_height = .Height
>>> txtbox_width = .Width
>>> End With
>>> End If
>>> txtbox_count = txtbox_count + 1
>>> End If
>>> Next ctl
>>>
>>> Set ctl = Me.Controls.Add("Forms.TextBox.1", "TextBox" & txtbox_count
>>> + 1)
>>> With ctl
>>> .Top = txtbox_top + 25
>>> .Left = txtbox_left
>>> .Height = txtbox_height
>>> .Width = txtbox_width
>>> End With
>>> With CodeRequest
>>> .Height = CodeRequest.Height + 25
>>> .CodeRequestADD.Top = .CodeRequestADD.Top + 25
>>> .INDEX.Top = .INDEX.Top + 25
>>> .CodeRequestOK.Top = .CodeRequestOK.Top + 25
>>> .Image1.Top = .Image1.Top + 25
>>> End With
>>> Sheets("sheet3").Range("A1") = CodeRequest.Controls("TextBox" &
>>> txtbox_count).Text
>>>
>>> End Sub
>>>
>>>
>>> You'll see that I simply use Range ("A1") at the bottom.....that's just
>>> because I don't know how to use code to find the first blank cell, and
>>> enter a textbox value into it.
>>>
>>> Thanks!
>>> Cal
>>>
>>>
>
>