Macro to modify a macro

glad_ir

New Member
Joined
Nov 22, 2020
Messages
49
Office Version
  1. 2010
Platform
  1. Windows
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


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

New Member
Joined
Nov 22, 2020
Messages
49
Office Version
  1. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,513
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)
 
Solution

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,252
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,828
Messages
5,598,336
Members
414,232
Latest member
MIA10_KO

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