Adding $ text to certain parts of formula via a VBA user from

mdd16

Board Regular
Joined
Jan 11, 2011
Messages
84
Office Version
  1. 365
Platform
  1. Windows
Hello to all the helpful souls in this group,

I wish to create a procedure to create a partially absolute formula. And I don't want to do Shift+4 to add $ signs at those places. Here is what I intend to create :

1. I will create a user form wherein the formula of the current selected cell will show up in a text box.

2. User form will have 3 buttons captioned "$A1", "A$1" and "$A$1". And there will be an "Accept" button to replace the formula in the current selection cell.

3. In the formula text, I will select portions where I want to add $ sign and click the relevant "$" Button. $ sign would be added either before row number or column number or both as per the command assigned to the button. I will repeat the selection of text for adding $ signs at multiple places in formula.

4. Button "Accept" closes the form and places new formula in current selection cell.

Can someone help me with code for step 3. Rest I will manage to do myself.

Thanks in advance for your kind help.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
is it an existing formula in a certain cell, you copy want to change the absolute or relative addresses or is it a formula you create in VBA and want to copy to a cell ?
How do you know the portions ?
Can you give an example of such a formula ?
 
Upvote 0
Hi. The formula will be pre existing the the cell. I want to copy and paste in a text box in a user form. I will select the segments of cell address texts one by one. And by clocking the appropriate user form command button will want to add $ sign either before the alphabet representing column or number text representing row or both.

There will be only one cell address in selected text at one time.

The command will apply to only the selected text and not on the entire string of formula text.

Basically I am trying to avoid pressing shift and 4 key multiple times.

Crazy idea ?
 
Upvote 0
Why not just pressing F4?
Am on mac with parallels F4 is a two click thing. Anyway I do understand some will find my idea a little crazy. Lazy to click twice but eager to build a user form and the coding work. ?? funny mind !
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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
Back
Top