Dynamic Text Boxes

a couple of years ago I used Excel also to transfer a lot of information by means of textboxes in cells which contain a lot of text. In order to make the text good readable I was able to configure the textbox in such a way that one could scroll the text in the texbox up and down. However, I have not used this feature for many years now, and I wonder If you could refresh my memory in order for me to use this again.
Many thanks in advance,

Raymond Ong A Kwien

Hi Mynda,

This tip is working beautifully for an excel dashboard I created, except that when I reopen and refresh the source data for the report, the text box formulas don't update. They only reflect the updated figures when I double-click on each text box, which then loses the text surrounding the updated figure as well as the formatting/font. Any idea how to fix this so I don't need to click on dozens of text boxes, reformat, and re-type the text surrounding the updated number?

Thank you,
Genni

Thanks Peter. I was trying to avoid using select/selection. I found a work around using a direct property of the shapes object:


Worksheet("Sheet1").Shapes("NameOfShape").DrawingObject.Formula = "=TypeFormulaHere"


My actual macro, used for updating a dashboard based on certain data from each weekday:


Sub UpdateDashMonday()

'This only updates one weekday, need to use other days' macros respectively.
Dim db As Worksheet, vRow As Long, i As Long

Set db = ThisWorkbook.Worksheets("Dashboard")
vRow = 4


For i = 1 To 13


db.Shapes("FV" & i & "Label").DrawingObject.Formula = "=Monday!B" & vRow
db.Shapes("FV" & i & "Extract").DrawingObject.Formula = "=Monday!D" & vRow
db.Shapes("FV" & i & "pH").DrawingObject.Formula = "=Monday!E" & vRow
db.Shapes("FV" & i & "Status").DrawingObject.Formula = "=Monday!F" & vRow
db.Shapes("FV" & i & "Temp").DrawingObject.Formula = "=Monday!I" & vRow

vRow = vRow + 1

Next i



End Sub••••ˇˇˇˇ

Here is a better Delete macro that only deletes existing textboxes. The previous procedure deleted Buttons (formControls)

Sub deleteTextBoxes()
'Deletes TextBoxes
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.Type = 17 Then
shp.Delete
End If
Next shp
End Sub

Sam
Here is another macro that allows you to delete all previous text boxes (but not charts). So two macros really!

Sub AddTextBox2()
'
Dim response
response = MsgBox("Do you want to delete all shapes from sheet", vbOKCancel, _
"Charts will not be deleted")
If response = vbOK Then
deleteDrawingObjects
End If

ActiveSheet.Shapes.AddTextBox(msoTextOrientationHorizontal, 30, _
50, 100, 25).Select
' numbers= position from Left, Top of sheet _
shape Width, Height
With Selection.ShapeRange
.Fill.ForeColor.RGB = RGB(220, 220, 250)
.Line.Weight = 2.25
.Line.ForeColor.RGB = RGB(255, 192, 0)
End With
With Selection
.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle
.Formula = "=$E$1"
.Font.Bold = True
End With
[a1].Select
End Sub

Sub deleteDrawingObjects()
'Deletes all shapes but charts
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If Not shp.Type = msoChart Then
shp.Delete
End If
Next shp
End Sub

Sam
Try this link
https://www.thespreadsheetguru.com/the-code-vault/2014/3/23/reference-a-worksheet-textbox

Ot this - works with existing textbox and sets a link to cell and formats Fill then text.

Sub RefTxtBx()
ActiveSheet.Shapes.Range(Array("TextBox 1")).Select
Selection.Formula = "=E1"
With Selection.ShapeRange.Fill
.ForeColor.RGB = RGB(192, 0, 0)
End With
With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
Selection.ShapeRange.TextFrame2.TextRange.Font.Bold = msoTrue
End With
[A1].Select
End Sub

Dp you know how to reference the text boxes from a macro coded through VBA?

I imagine Shari has solved this by now, but for anyone who needs it, you can remove the "$" absolute referencing in the result of =CELL("address:) in the standard way by wrapping it in SUBSTITUTE():

=SUBSTITUTE(CELL("address"),"$","")


Separately, using Excel 2019, I permanently lose the ability to format the text box AFTER linking it.

Thank you Mynda. This was helpful. It helped automate one of tasks and gave me back my 15 minutes every week :)
Keep it up.

Is there supposed to be a limit to the amount of text from a cell that a text box will display? I have an entire paragraph I am trying to display, and it gets cut off part way through the paragraph. Any help you could offer would be awesome.

Very useful - just what I needed to get me heading in the right direction

Mark, I know this is late, but perhaps for the benefit of others ...

The problem is you're typing your formula (your equal signs) in the text box itself. This is incorrect. You're meant to click the text box to make it active, *then* type the formula (equal sign) in the *formula bar*, the area above the actual spreadsheet cells, and below the ribbons.

Thank you this was very clear and helpful!

Thank You for the tip. I tried to combine/concatenate the text box formula as shown in figure 4, however I kept getting error messages. I am using Excel 2010.

Great tip, so Simple, Who Knew? Thanks

I have a cell that contains a statement and upper management would like to reference a cell within the statement. For example: "If Cell I27 does not match..." and if a row is added or deleted, then cell I27 would change to I26 or I28. I have tried Cell("address") and I can reference the cell in the statement, but it shows as $I$27 and they do not want to see the absolute dollar signs, any suggestions?

Hi Simon,

Text boxes are finicky like that. You'll just have to reapply the formatting after linking it to the cell.

Mynda

Trying to update an existing text box. Followed the steps below. Cell content is being copied but losing the original text box formatting. Any idea why?

Hi Mark,

What version of Excel are you using?

Mynda

I followed the steps above:
- Add a Text Box
- Ensure the Text Box was active
- Type =
- Click on a cell... At that point, the cell Text Box becomes inactive, the cell becomes active and Text Box does not display the contents of the cell.

I am using the CONCATENATE function in Excel to take text in three separate cells, combine them and deposit them in a forth cell. The result = "The Jones Family".

I then have to upload the spreadsheet in a specific format to a website. The format allows only one column for the customer's name - "The Jones Family". If, however, I delete the initial columns in my original spreadsheet, "The Jones Family" loses its reference.

Any suggestions?

I have a range of cells with data that changes based upon user input. I can't seem to get the text box to display the range of data. It only displays the first cell referenced. any suggestions?

I have a load of text in a cell that I need to display in a text box on a different sheet.
My formula of ='project Scope - Notes'!A1 only shows the first 7 lines, the last of which is cut off after 19 characters.

If I put the exact same formula into a cell, or a set of merged cells it displays all the text. All 31 lines.

Any idea why it is truncating the text or how I can make it show it all?

I have currently got round it by merging a load of cells but I want the user to be able to resize the text box to fit as I have second text box under it?

Much Obliged

Dear All,
I need technical help in preparing the attendance sheet in excel.
I’ve already prepared a sheet in which when I change the month from the drop down list, the dates & days are automatically updated.
The problem is that when I change the month, the dates & days are automatically updated but the columns (or table) associated with these days are not updated. E.g. in the month of January, 2015; 4th day was Sunday, means it’s a weekend so I put “H” (Holiday) in all the cells under 4th January, 2015 but when I change the month to February, 2015; the 4th day was Wednesday but the cells under 4th Feb, 2015 is showing “H” (Holiday), which is the value of month of January.
Is it possible that when I change the month, the columns under the days/dates are also updated and most importantly I can change the value as per requirement?

Thanks,

Huge, huge help! Thanks for the info! Cheers!

@David
I think you might be trying to enter the "=K2" formula directly into the text box itself which just accepts this as text input rather than a formula. What you need to do is select the text box and enter the formula (=K2)into the formula bar not the text box.

For some reason I cannot get this to work. I use Excel 2010. When I enter = within the active text box and then click on the cell I want to show in the box, focus goes to the other cell, and all that remains in teh text box is the "=" I entered. If I type in the cell reference as =k2, that text appears in the text box, and not the value from cell K2.

Wow! Great tip, and even better way of presenting and explaining it!

- Terry

Drop-down lists are created by selecting "Data Validation", on the "Data" ribbon.

In the settings tab, Allow: List, and in Source: type the items you want in the list, separated by commas. Make sure you tick the "in-cell dropdown" option, and the "Ignore blank" option will depend on your use.

Excellent tip, I didn't know that, amazing!

Mynda, I didn't know you were writing for tips.net! I'm excited to see more!

Awesome :)

Mynda is showing us just how a tutorial should be written. No unneceassary fantasy third party required, easy to read & absorb language, some interactive graphics and bingo, informative and well structured. We could have more of this.
Bob.

Tony,

Check here:

http://excelribbon.tips.net/T006191

-Allen

This is good but I've forgotten how to create the drop-down list in the first place. Help!!

Juan, Bryan, Zaigham and Bob, thank you! I'm glad you like my tip :-)