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

 

[Rick Rothstein]

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.

 

[6StringJazzer]

It's hard to do this in bits and pieces. Please show all your code.

 

[glad_ir]

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]

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]

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?

 

[Rick Rothstein]

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...

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...

Sub swaptext()
  Dim Temp As String
  With Sheets("Sheet1")
    Temp = .TextBox1
    .TextBox1 = .TextBox2
    .TextBox2 = Temp
  End With
End Sub

 

[glad_ir]

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