Formattting text box font size

MarkPl

New Member
Joined
Aug 2, 2007
Messages
4
I have a worksheet that has a text box lined to a cell. I want to set the font size based on a value from another cell. eg: if cell D4 == 10 then the fontsize of the text in the text box will be 10. If D4==12 then the font size will be 12.
Any suggestions on how or if this can be done?
Thanks,
Mark
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Welcome to the board!! :)

Put this in the worksheet module:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$4" Then
    TextBox1.Font.Size = Target.Value
End If
End Sub
 

MarkPl

New Member
Joined
Aug 2, 2007
Messages
4
Wow,
Thanks very much for the quick reply. I have tried inserting the code that you provided into a module for the worksheet but unfortunately it is still not working.What I have is as follows
A B C D E
1 Line1 Line2 Line3 Line4 Use Font Size
2 This is a test 16

Then a series of text boxes that show the content of cells A through D. The font size in E is calculated based on the length of the longest entry in A-D.
When I got your code I went into the Excel VB editor and inserted a module into the worksheet and then pasted your code into that. Saved the file and then exited and reopened the spreadsheet. I also a couple of lines to the code to make it apply to all of the textboxes. Any suggestions as to what I did wrong?
Thanks for any assistance,
Mark
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Can you post the amended code as you now have it?
 

MarkPl

New Member
Joined
Aug 2, 2007
Messages
4
All I have done is to add in the additional text boxes that will use the same reference for the font size as follows. (Or at least that is what I hope I have done. )

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$E$4" Then
TextBox1.Font.Size = Target.Value
TextBox2.Font.Size = Target.Value
TextBox3.Font.Size = Target.Value
TextBox4.Font.Size = Target.Value
End If

End Sub
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
When you say "inserted a module into the worksheet" did you go Insert>Module? If so, then that was wrong.

If you right click the worksheet tab and select view code, this will open the worksheet module that you need, paste the code here.

BTW what you have changed looks fine to me.
 

MarkPl

New Member
Joined
Aug 2, 2007
Messages
4
Well I have done as you suggested and I am still not getting it to apply the formatting. I have also renamed the text boxes to Text_Box_1 ... Text_Box_4 and then changed the name in the code so it is as follows;

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$E$4" Then
Text_Box_1.Font.Size = Target.Value
Text_Box_2.Font.Size = Target.Value
Text_Box_3.Font.Size = Target.Value
Text_Box_4.Font.Size = Target.Value
End If

End Sub

I am not sure if vb uses object names or just the identifiers so I have tried it all ways Text Box 1 , TextBox1, or Text_Box_1 . I appreciate the assistance on this because I am at a loss.
Thanks again,
Mark
 

Forum statistics

Threads
1,181,363
Messages
5,929,538
Members
436,676
Latest member
Mavri

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