Swapping text between 2 text boxes

[glad_ir]

Hello,

Wondering if somebody could advise how to exchange text between 2 text boxes with VBA please. I have no idea where to start.

I have 2 boxes in my sheet - Text Box 1 contains the text "Series 1" and Text box 2 contains the text "Series 2". I need a macro that will convert this to Text Box 1 contains the text "Series1" and Text Box 2 contains the text "Series 2". I have another macro in my sheet that references the Text Box numbers so I think it is the text in the text boxes that needs to be switched rather than the text boxes names.

Any help with this would be much appreciated!

Thanks,
Iain

 

[6StringJazzer]

If you just need to swap the contents:

Dim Temp As String
Temp = TextBox1
TextBox1 = TextBox2
TextBox2 = Temp

 

[Rick Rothstein]

Just for curiosity-sake, I would note you can also do this without using a Temp variable (less efficiently though I would guess)...

TextBox1 = TextBox2 & TextBox1
TextBox2 = Mid(TextBox1, Len(TextBox2) + 1)
TextBox1 = Left(TextBox1, Len(TextBox1) - Len(TextBox2))

And for those who might be interested, you can also swap numbers without using a Temp variable as well. Assuming your two numeric variables are N1 and N2...

N1 = N1 + N2
N2 = N1 - N2
N1 = N1 - N2

 

[glad_ir]

Hi,

Thank you both for your replies. I must be doing something wrong though as I can't get either of the codes to work. I'm not in design mode and other macros are working but when I try these codes nothing happens. I've check the TextBox names and they are TextBox 1 and TextBox 2. To confirm the text boxes are ActiveX control boxes. Do you have any ideas what I could be doing wrong?

My problem statement above was incorrect but I believe you interpreted my intent! Corrected one below....

Thanks,
Iain

I have 2 boxes in my sheet - Text Box 1 contains the text "Series 1" and Text box 2 contains the text "Series 2". I need a macro that will convert this to Text Box 1 contains the text "Series 2" and Text Box 2 contains the text "Series 1". I have another macro in my sheet that references the Text Box numbers so I think it is the text in the text boxes that needs to be switched rather than the text boxes names.

 

[6StringJazzer]

Where are you putting the code we suggested? When are you expecting it to be executed?

Please show all the code you currently have in that worksheet module. (Using code tags will make it more readable, select code then click the VBA button in the edit controls.)

 

[glad_ir]

Where are you putting the code we suggested? When are you expecting it to be executed?

Please show all the code you currently have in that worksheet module. (Using code tags will make it more readable, select code then click the VBA button in the edit controls.)

Hi,

I tried to execute it from a module as want to assign it to a macro button that will also move some other ranges in the sheet. The only other macro I have in the sheet is the one below (provided by "My answer is this") which moves the text boxes in their rows to be above the selected cells. Will this prevent your code from running?

thanks,
Iain

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Modified  11/25/2020  8:29:29 AM  EST

With TextBox2
    .Left = ActiveCell.Left
    .Top = Cells(5, 2).Top
End With
With TextBox1
    .Left = ActiveCell.Left
    .Top = Cells(15, 2).Top
End With

End Sub

 

[Rick Rothstein]

You said in a previous post (Message #4) that your textboxes were named "TextBox 1" and "TextBox 2" where you showed a space in front of the number. Are those (with the space) your actual textbox names?

 

[6StringJazzer]

The code you showed won't interfere with the code I provided. If you created another sub in another module, and tie it to a button, one possibility is that the sub is not running at all. Add a MsgBox call as the first line of code in your other sub. Do you get the msgbox when you click the button? If not, you may not have the button correctly linked to the code. If you do get the msgbox, then please show that code.

 

[6StringJazzer]

We need to clarify whether these text boxes are ActiveX or Forms controls. I assumed ActiveX, and your code looks like ActiveX,

 

[glad_ir]

Hi, in answer to Rick's question - I've just checked and the texts boxes are "TextBox1" and "TextBox2" without spaces - sorry I inadvertently added these in.
The Text Boxes are ActiveX selected from the insert toolbar in developer. I've added the Message into the code and it seems to be running but the text isn't switching.

Am I allowed to attach the excel file in question to posts? Sorry I'm new