One Textbox Value into 2 different textboxes or cells

[senators40]

Hi,

I am trying to get a value from one textbox into 2 or more textboxes and cells in excel.

For instance

I have blank in textbox1, textbox2 and cell A1 and textbox3 with 50

I would like a command that says put textbox3's value of 50 into textbox1, textbox2 and cell A1 without having to repeat the formula 3 times (or more depending on the situation.

Textbox1.Text = Textbox3.Text
Textbox2.Text = Textbox3.Text
Range("A1").Value = Textbox3.Text

Any help would be appreciated

Jeff

 

[SkipVought]

Jeff,

It's what programming is all about. WYSIWYG! ;-)

You could tweek it a tad by...

with Textbox3
  Textbox1.Text = .Text
  Textbox2.Text = .Text
  Range("A1").Value = .Text
end with

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[senators40]

Thanks,

Would there be a way to state that if the textbox name contains a specific word (say Buildings) with the textboxes being Buildings2005, Buildings2006 etc to put the same value on both. I am thinking that the the "left" feature may work but I can't get the code to work

In other words if we had Buildings2005, Buidlings2006 to put the value of Textbox1 into both textboxes with the common item being Buildings.

I also would like to assign the .Top, .Left, .BorderColor etc

Thanks,

Jeff

 

[rmikesmith]

Jeff,

The function you are looking for is Instr, which returns the position of a substring in a string. Here is some code that will cycle through all controls on the Userform. If the control is a TextBox and if the name contains the substring Buildings then a particular value will be entered into both:

Dim Ctl As Control

   With frmInput
     For Each Ctl In .Controls
       If TypeName(Ctl) = "TextBox" And InStr(1, Ctl.Name, "Buildings", vbTextCompare) > 0 Then
         Ctl.Text = 20 [COLOR=green]'or assign a variable here[/color]
       End If
     Next Ctl
     .Show
   End With

In my test setup, the Userform (frmInput) contains three TextBoxes, a ListBox, a CheckBox and a CommandButton. The TextBoxes are named txtBuildings2005, txtBuildings2006 and txtOther. When run, only the two TextBoxes containing Buildings in the name were populated.

HTH,
Mike