ExcelBanter - a macro that Formats a textbox based on value in a cell

On Jun 6, 4:00 pm, txm49 wrote:
Have created textbox. The text in the textbox is driven from a formula.
Want the font in the textbox to depend on the value of a particular cell.
This particular cell can equal "RED", "GREEN", or "BLACK". Am attempting to
write a macro to solve this.

You can use the following event procedure to track the Change event
for the worksheet. This assumes that the cell you want to affect the
textbox is in A1 and that the textbox name is "Text Box 1". To be sure
of the name of the textbox click the textbox and look at the name box
left of the formula bar.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "A1" Then
Select Case UCase(Target.Value)
Case "RED"
ActiveSheet.Shapes("Text Box 1").Select
Selection.Characters.Font.Name = "Times New Roman"
Case "GREEN"
ActiveSheet.Shapes("Text Box 1").Select
Selection.Characters.Font.Name = "Arial"
Case "BLUE"
ActiveSheet.Shapes("Text Box 1").Select
Selection.Characters.Font.Name = "Tunga"
End Select
End If
Target.Select
End Sub

To install:
- Right click on the sheet tab. Choose View Code...
- It takes you to the VBA IDE. Paste the above code in the active
window.

HTH
Kostis Vezerides