Linking TextBox Values in MultiPage Control with Excel Cells

Stan,

Might this help?

To try it, you need a userform containing a standard two-tab multipage. On the first tab, place three textboxes and a commandbutton. Add the following code for 'CommandButton1_Click'.

Private Sub CommandButton1_Click()

Sheets("Sheet1").Activate
Range("A1").Select
For n = 1 To 3
ctrl_name = "TextBox" + CStr(n) ' assigns textbox name to a variable
If n > 1 Then
ActiveCell.Offset(1, 0).Select ' move down one cell if beyond the first item
End If
ActiveCell.Value = Controls(ctrl_name).Value ' paste the text-box value into the active cell
Next

End Sub

If it works, alter the activecell.offset navigation to suit your spreadsheet and you should be in business.

MikeB

Looks Good Mike, but I'm working with an array of text boxes

Mike

What if I had three columns and three row of text boxes?

Stan ,

Re: Looks Good Mike, but I'm working with an array of text boxes

Stan,

Essentially the same code, if I'm understanding you correctly. Three rows and columns of textboxes makes 9, which are named TextBox1 to TextBox9 regardless of their position on your form. Place the extra six textboxes on the form in my previous example.

The CommandButton1_Click code becomes:

Private Sub CommandButton1_Click()

Sheets("Sheet1").Activate
Range("A1").Select
For n = 1 To 9
ctrl_name = "TextBox" + CStr(n) ' assigns textbox name to a variable
If n > 1 Then
If n = 4 Or n = 7 Then
ActiveCell.Offset(-2, 1).Select ' move across one cell and up two cells
Else
ActiveCell.Offset(1, 0).Select ' move down one cell
End If
End If
ActiveCell.Value = Controls(ctrl_name).Value ' paste the text-box value into the active cell
Next

End Sub

How's that?

MikeB

Re: Looks Good Mike, but I'm working with an array of text boxes

Mike

Tried it but got the error message "Could not find the specified object" at the following line:

ActiveCell.Value = Controls(ctrl_name).Value

I have tried with the command button either on the Userform or, as you suggested, on the first page of the Multipage control. Any ideas?

Stan

One other thing...

Mike

I'm not sure I understand the line "ctrl_name = "TextBox" + CStr(n)". Does it retreive the actual name of the textbox (e.g. myText.Text)?

Stan

Re: One other thing...

No, it assumes that all textboxes are named

Textbox1
Textbox2
.
.
.
Textbox9

Juan Pablo

Re: One other thing...

Is there a way I can get around this by referencing the name I've assigned?

Stan

What names do you have ? (NT)

Re: What names do you have ? (NT)

Juan

Here is an example:

txtClO2Royal_SW_U.Text

I was wondering if I can use the ".Name" property. I tried the following but got an "object missing error":

ctrl_name = TextBox.Name

Stan

Re: What names do you have ?

I think you can, but it's a little more difficult, i think the best is to adjust the names in order to have a "logic" sequence.

Juan Pablo

Re: What names do you have ?

Stan,

The following code works in my hands.

In my earlier example, I have renamed my nine textboxes from "TextBox1", "TextBox2"..."TextBox9" to "A", "B"..."I". You assign these names to the variable ctrl_name in the first If...EndIf loop below.

Private Sub CommandButton1_Click()

Sheets("Sheet1").Activate
Range("A1").Select
For n = 1 To 9
If n = 1 Then ' assigns textbox name to a variable
ctrl_name = "A"
ElseIf n = 2 Then
ctrl_name = "B"
ElseIf n = 3 Then
ctrl_name = "C"
ElseIf n = 4 Then
ctrl_name = "D"
ElseIf n = 5 Then
ctrl_name = "E"
ElseIf n = 6 Then
ctrl_name = "F"
ElseIf n = 7 Then
ctrl_name = "G"
ElseIf n = 8 Then
ctrl_name = "H"
Else
ctrl_name = "I"
End If
If n > 1 Then
If n = 4 Or n = 7 Then
ActiveCell.Offset(-2, 1).Select ' move across one cell and up two cells
Else
ActiveCell.Offset(1, 0).Select ' move down one cell
End If
End If
ActiveCell.Value = Controls(ctrl_name).Value ' paste the text-box value into the active cell
Next

End Sub

I would strongly recommend generising the names of your textboxes to include the numbers 1...n somewhere in the name. Your would then be able to make more elegant use of the For...Next loop.

For instance, if your first text box name is "abc1xyz" and your second is "abc2xyz" then you can populate the ctrl_name variable using the line:

ctrl_name = "abc" + CStr(n) + "xyz"

where the CStr command changes the numeric 'n' to text. You can use this one line then to replace all of the rather pedantic assignment of variables in the first If...EndIf loop in my longer suggested code above. If you can't do this, then the replacement of the "A", "B" etc. with your individual textbox names ought to work just the same.

MikeB

Thanks Mike!

I very much appreciate your help Mike.

Stan

If n = 1 Then ' assigns textbox name to a variable ctrl_name = "A" ElseIf n = 2 Then ctrl_name = "B" ElseIf n = 3 Then ctrl_name = "C" ElseIf n = 4 Then ctrl_name = "D" ElseIf n = 5 Then ctrl_name = "E" ElseIf n = 6 Then ctrl_name = "F" ElseIf n = 7 Then ctrl_name = "G" ElseIf n = 8 Then ctrl_name = "H" Else ctrl_name = "I" End If ActiveCell.Value = Controls(ctrl_name).Value ' paste the text-box value into the active cell