Swapping text between 2 text boxes

glad_ir

Board Regular
Joined
Nov 22, 2020
Messages
76
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
847
Office Version
  1. 365
Platform
  1. Windows
If you just need to swap the contents:

VBA Code:
Dim Temp As String
Temp = TextBox1
TextBox1 = TextBox2
TextBox2 = Temp
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,982
Office Version
  1. 2016
Platform
  1. Windows
Just for curiosity-sake, I would note you can also do this without using a Temp variable (less efficiently though I would guess)...
VBA Code:
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...
VBA Code:
N1 = N1 + N2
N2 = N1 - N2
N1 = N1 - N2
 
Last edited:

glad_ir

Board Regular
Joined
Nov 22, 2020
Messages
76
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jan 27, 2010
Messages
847
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Nov 22, 2020
Messages
76
Office Version
  1. 2010
Platform
  1. Windows
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

VBA Code:
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
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,982
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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?
 
Last edited:

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
847
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 27, 2010
Messages
847
Office Version
  1. 365
Platform
  1. Windows
We need to clarify whether these text boxes are ActiveX or Forms controls. I assumed ActiveX, and your code looks like ActiveX,
 

glad_ir

Board Regular
Joined
Nov 22, 2020
Messages
76
Office Version
  1. 2010
Platform
  1. Windows
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.

1606404761125.png


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

Watch MrExcel Video

Forum statistics

Threads
1,130,381
Messages
5,641,805
Members
417,239
Latest member
AymericA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top