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

 

[glad_ir]

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.

Hi, thank you for this and the offer of help. I should have laid out the whole story. TBH I hadn't appreciated moving the text boxes up and down would be a problem until I tried it....I know, newby! Your code to make the text boxes move in line with the selected cells has worked a treat. Thanks again for your help

 

[Macropod]

There is no way for one macro to modify another macro.

Actually, it can be done. It's usually not worth the trouble though, especially since it requires giving trusted access to the VB project - which is a significant security risk.

For some demonstrations of of various ways of manipulating code modules, see:
Use VB script to paste code into a module? (microsoft.com)
Creating Event Handlers Programmatically (msofficeforums.com)
Inserting code line in Word Module (msofficeforums.com)

 

[My Aswer Is This]

Actually, it can be done. It's usually not worth the trouble though, especially since it requires giving trusted access to the VB project - which is a significant security risk.

For some demonstrations of of various ways of manipulating code modules, see:
Use VB script to paste code into a module? (microsoft.com)
Creating Event Handlers Programmatically (msofficeforums.com)
Inserting code line in Word Module (msofficeforums.com)

Your correct. I should have said: "I know of no way". Almost anything can be done with Excel with enough knowledge and code.