Macro to modify a macro

[glad_ir]

Hello,

I wonder if somebody can help me please. Is it possible to write a macro that modifies another macro?

What I'm trying to achieve: I have the macro below which moves Textbox2 on row 5 and Textbox1 on row 15 to be directly above the selected cell (code kindly provided by "And the answer Is....", I'm not that clever!). I'd like a second macro that modifies this macro by changing "TextBox1" to "TextBox2" and vice versa in the code to effectively switch the Text Boxes. I've tried the record macro function to get started but no joy! Is there a way of achieving this?

Any help would be very much appreciated.

thank you,
Iain

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

If Range("A1").Value = False Then
Exit Sub
End If

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

 

[My Aswer Is This]

I wrote this code for you. There is no way for one macro to modify another macro.

Can you not just modify the code yourself ?
Or are you saying from time to time you want to switch around which TextBox Top is on what row?

 

[My Aswer Is This]

How about we have you enter 5 or what ever in Range("B1")
Then the script will look in Range("B1") for the Row Top for Textbox1
So if you enter 12 in B1 Textbox1 top will be what ever row number you entered in B1
And the same for C1 and Textbox2
Would something like that work.

 

[glad_ir]

How about we have you enter 5 or what ever in Range("B1")
Then the script will look in Range("B1") for the Row Top for Textbox1
So if you enter 12 in B1 Textbox1 top will be what ever row number you entered in B1
And the same for C1 and Textbox2
Would something like that work.

Hi. Sorry for quoting your name wrong in the post - that code is excellent by the way!

What I am trying to achieve in the sheet as a follow on is the ability to move ranges of information up and down in the sheet with their associated titles. May be easier to describe with a picture...

The below represent 2 data series in my sheet. The blue and yellow ranges are larger than shown and will contain multiple user entries and objects. Users will want to reorder the series in the sheet as new information becomes available, so they will want to move from

to

when they click a button.

I can move the yellow and blue blocks no problem but they then have the incorrect TextBox associated as the code that allows they to move horizontally/align with the selected cell anchors them to the specified rows. I can see a couple of options but not sure if either is viable

1) Get the button macro to change the TextBox name in your macro so the text box moves with the data block.....doesn't sound like this is viable?
2) Leave the textboxes where they are and swap the text in them?

or am I completely barking up the wrong tree here?

 

[My Aswer Is This]

Thinking about this:
What is a Block?
You said:
I can move the yellow and blue blocks

 

[My Aswer Is This]

By Block do you mean a named Range?

 

[glad_ir]

By Block do you mean a named Range?

Hi, the blocks are ranges. I haven't named them but can do. These ranges contain cells with questions, cells for the user to add data. There are also merged cells in the ranges where the user pastes objects (I have a macro that selects all user added objects and adjust their properties to move and size with cells so that when the range is cut and paste they go with it). Is this clear or shall I send a snip of the sheet?

 

[My Aswer Is This]

You said:
I can move the yellow and blue blocks
And you said I can move the ranges.
It's impossible to move a range. You can move the data in a range but not the range unless you change the name of a Range.

I guess I still cannot figure out what were trying to do here.

Maybe someone else here on form will be able to help you.

 

[glad_ir]

Hi - thanks for coming back. Sorry for not being clear - I'm inexperienced with excel and not clear on the correct phrases. I meant move the data in a range rather than the range itself.

I've uploaded a copy of a spreadsheet showing what I'm trying to achieve. Since uploading this somebody else has shown me how to swap text between the Text Boxes (option 2 above) so I no longer need to change the text boxes (option 1 above).

Thank you for trying to help me and sorry again for being so confusing.

Best regards, Iain

macrobook1.xlsm

 

[My Aswer Is This]

I always like it best when a User especially a new user tells me his ultimate Goal.
Your original post if I remember correctly wanted a Textbox to move around on the screen when you scrolled around your worksheet. And I said I did not know how to do that. So I suggested using active cell. But that turned into a much more complicated case.
I believe the best thing to do would be to use a UserForm.

A Userform is a little Form that can be displayed any time you want and the form can be placed on the screen any place you want and it sets just where you want it. You can move it if you want. And you can still work on your sheet just like normal.
The userform sets in the same place on your screen no matter where you scroll to.
Now on the Userform you can put all sorts of controls like Command buttons Textboxes and any thing you want. So you could have 5 textboxes which could display any data you want.

So you could enter Alpha into Textbox1 and Bravo into Textbox 2
Or you can have a script written that when the userform is opened all this data is automatically loaded into the textboxes. Or you can enter it yourself at any time.

If you think this would work let me know and I will give you help if needed.