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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

My Aswer Is This

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

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

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

1606390322428.png
to
1606390350820.png
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,252
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

glad_ir

New Member
Joined
Nov 22, 2020
Messages
49
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,122,832
Messages
5,598,363
Members
414,233
Latest member
WolverineNurse

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