Swapping text between 2 text boxes

glad_ir

Board Regular
Joined
Nov 22, 2020
Messages
69
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,881
Office Version
  1. 2010
Platform
  1. Windows
You can put your file on a free file sharing site (the only one I trust is DropBox though), mark it for sharing and then post the link they give you... that will allow us to download your file is look at what is going on.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
822
Office Version
  1. 365
Platform
  1. Windows
It's hard to do this in bits and pieces. Please show all your code.
 

glad_ir

Board Regular
Joined
Nov 22, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
Hi, I think I've uploaded it.....can you access this?

macrobook1.xlsm

I've also included the macro that swaps the ranges coloured yellow and blue which I'm running off the double headed arrow. This works fine but the title in the text boxes doesn't change.
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
822
Office Version
  1. 365
Platform
  1. Windows
Your button (the two-headed arrow) is calling a Sub in Module2 called Switch. The code I provided is in Module1 in a sub called swaptext that is never called anywhere. I think you probably want to take the code that is in swaptext and copy it into Switch...?
 

glad_ir

Board Regular
Joined
Nov 22, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Hi, great - you can access it....never used dropbox before!

I haven't associated it with the double headed arrow yet but the plan is to run both macros off it or combine swaptext and Switch into a single macro......however swaptext doesn't work me . Does it work for you?
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,881
Office Version
  1. 2010
Platform
  1. Windows
You actually have two problems with your code. The first was identified by 6StringJazzer, namely, you never call the swaptext macro. Add this line of code to the bottom of your Switch subroutine...
VBA Code:
Call swaptext

Now, your second problem is in the coding of swaptext... VBA thinks your TextBox1 and TextBox2 are local variables, not textbox objects. You have to reference the sheet the textboxes are on to get VBA to see them as objects. Change your swaptext code to this and add what I mentioned above and all should work as you wanted...
VBA Code:
Sub swaptext()
  Dim Temp As String
  With Sheets("Sheet1")
    Temp = .TextBox1
    .TextBox1 = .TextBox2
    .TextBox2 = Temp
  End With
End Sub
 
Solution

glad_ir

Board Regular
Joined
Nov 22, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
Bingo!! Thank you both so much for your help with this and also for the explanation - I really appreciate your time and help. Thank you :):)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,643
Messages
5,626,055
Members
416,158
Latest member
CaliburBlade138

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