vba to sit text boxes alongside each other

[cjcass]

Hi,
This may/may not be achievable - is there some code that will sit three text boxes on top of each other?
So the bottom edge of text box 1 sits adjacent to the top edge of text box 2 and the top edge of text box 3 sits adjacent to the bottom of text box 2? If poss. then could also do left/right edges with other text boxes too?
Rgds,

 

[CodeNinja]

I'm assuming you are doing this in a user form, so I have put it in that context, but the concept is the same if it is in a worksheet...
This userform has 3 textboxes in random places (not aligned) and this is the ******* action for a button:

UserForm1.TextBox2.Top = UserForm1.TextBox1.Top + UserForm1.TextBox1.Height
UserForm1.TextBox3.Top = UserForm1.TextBox1.Top + UserForm1.TextBox1.Height + UserForm1.TextBox2.Height
UserForm1.TextBox2.Left = UserForm1.TextBox1.Left
UserForm1.TextBox3.Left = UserForm1.TextBox1.Left

Good luck,

CN.

 

[cjcass]

Great and thanks... let me have a play with what you've given me

 

[cjcass]

ok, so I'm not using userform they are just text boxes free on the wksheet.

TextBox 149.Left = TextBox 151.Right

I put this into a macro but wouldn't work, what kind of code do I need?

 

[CodeNinja]

This works for me... be sure to adjust sheet and textbox names accordingly...

Sub test()
Sheet1.TextBox2.Left = Sheet1.TextBox1.Left
Sheet1.TextBox3.Left = Sheet1.TextBox1.Left
Sheet1.TextBox2.Top = Sheet1.TextBox1.Top + Sheet1.TextBox1.Height
Sheet1.TextBox3.Top = Sheet1.TextBox2.Top + Sheet1.TextBox2.Height
End Sub

Good luck,
CN.

 

[Rick Rothstein]

ok, so I'm not using userform they are just text boxes free on the wksheet.

What kind of textboxes are they... ActiveX (from Developers tab) or Forms (Insert tab, Text panel, Text Box button)?

 

[CodeNinja]

Good point Rick... I posted the result for ActiveX textboxes... If they were forms, then you would have to replace the syntax Sheet1.TextBox3. with Sheet1.Shapes("Textbox 3"). for all cases and it should work properly... everything else would be the same... Thanks for the clarification.

 

[cjcass]

Hi CN & Rick,
Thanks for your replies.
Ok, so the text boxes have been inserted from the 'Insert tab, Text panel, Text Box button'.
I have run a test in a new spsht and it's still not playing - my complete code (as per CN's last syntax suggestion) is as below, it's being executed from a macro button as opposed to a change or calculation event.

Sub Macro1()
'
' Macro1 Macro

Sheet1.Shapes("TextBox 1").Left = Sheet1.Shapes("TextBox 2").Right

End Sub

It's giving me: 'Run time error 438 - Object doesn't support this property or method'

 

[gallen]

Textboxes don't have a 'Right' property, just a Top, Left, Width and Height property. all others can be calculated form those... so the above should be written as :

Sheet1.Shapes("TextBox 1").Left = Sheet1.Shapes("TextBox 2").Left + Sheet1.Shapes("TextBox 2").Width

Although this puts TextBox 1 on the right hand side of TextBox 2. Is this correct?

You may also want to include:

Sheet1.Shapes("TextBox 1").Top = Sheet1.Shapes("TextBox 2").Top

 

[My Aswer Is This]

You showed:
Sheet1.Shapes("TextBox 1").Left = Sheet1.Shapes("TextBox 2").Right



I do not believe you can use .Right
I believe you can only use .Left and .Top