Double TextBox in Shape using DrawingObject.Formula
When my macro runs, it links values in some cells, then I use those cell values in shapes with DrawingObject.Formula. This way I can only get to the shape to look like the right square, but i would need it to have the second value right below it(see image)
Range("E1").Value = Data
Var1 = Range("E2").Value
Var2 = Range("E3").Value
Var3 = Range("E5").Value
Var4 = Range("F7").Value
Var5 = Range("F8").Value
Var6 = Range("F9").Value
Var7 = Range("E8").Value
wsLP.Shapes(ArrayColBloco(0)).DrawingObject.Formula = "=E1"
wsLP.Shapes(ArrayColBloco(1)).DrawingObject.Formula = "=E2"
wsLP.Shapes(ArrayColBloco(2)).DrawingObject.Formula = "=E3"
wsLP.Shapes(ArrayColBloco(3)).DrawingObject.Formula = "=E5"
wsLP.Shapes(ArrayColBloco(4)).DrawingObject.Formula = "=F5"
wsLP.Shapes(ArrayColBloco(5)).DrawingObject.Formula = "=F6"
wsLP.Shapes(ArrayColBloco(6)).DrawingObject.Formula = "=F7"
wsLP.Shapes(ArrayColBloco(7)).DrawingObject.Formula = "=E8"
Probably would need another way to link the cell values to the shapes textbox
o It is not possible to put a formula as source to a shape that contains more than a single cell reference, see Text And Formula In Shape Text. So something like "=E2" & char(10) & F2" will fail - in Excel, you get an error message "The formula is missing a cell reference...". If you try this with code, you get a runtime error 1004 "Unable to set the Formula property..."
o You could use a helper cell (maybe on a hidden sheet or a hidden column) that has the formula, and reference that cell.
Formula in Z2: =E2 & char(10) & F2
Formula for the shape: =Z2
o When you use cell references as content for a shape, you can't format the characters individually, it's an "all or nothing". So you can't have the first part in bold+red and the second part in black font as you show in your picture.
o I would advice to simply use 2 shapes - put a small shape inside the shape you have, make it transparent (and no border, but do this only after you are sure everything works as it is hard to find an invisible shape on top of another).
- Largest sum of consecutive values in row, but is it still consecutive in the cell of the last column?
- Dynamically Inserting a row and iterating over it in VBA?
- VBA class member function and class definition problem continues
- How can I perform COALESCE in power query?
- VBA class member function and class definition problem
- How can I set OptionButton font using VBA For Loop?
- VBA remove string custom salutation name from list table excel
- How to import many dataframes from R into a one Excel file in a simple way?
- How to add a missing column to the Transform Sample File
- Pull data from multiple workbooks based on a dynamic list
- Dependent dropdown list in Excel ,Data Validation and VBA
- Excel Formula: Filter Group based on Smallest Values
- Find column name of active cell within ListObject in Excel
- Strange characters found in XML file and PowerShell output after exporting from Excel: ​
- MID function inside COUNTIFS
- Finding Max of 3 Inputs VBA
- How to align duplicates on the same rows in Excel
- How to use .HTMLBody to hyperlink a cell value?
- Adding cell values, specific to each page, to each header
- Excel VBA macro for URL redirect detecting returns 200 instead of 301 sometimes
- Count with specific values in columns
- Convert IEEE Float hex string to an Excel VBA Single (float)
- Openpyxl or Pandas, which is better at reading data from a excel file and returning corresponding values
- Excel Function: Using a specific criteria to form an array to corresponding values
- How to pass metadata to Google Drive API upload with VBA
- IsError not capturing error returned by expression
- Excel VBA multi select drop down list
- Imported Excel file with dates, came out as numbers, trying to return to date format
- Creating Conditional Column based on another Table's Column
- Convert Range to a table