• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formatting Cells in Excel Text Boxes

P

PaulFogel123

Member
I'm using a text box with an = formula to capture a cell label (please see attached). I want the first letter of each word in the label to be larger to simulate MS Word's Small Caps font. While I can do this manually, the label will change depending on user selection, so I need a formula that will do the formatting. The label could contain one, two, or three words.

Any ideas?

Paul
 

Attachments

  • Workbook1.xlsx
    25.1 KB · Views: 6
In short. You can't.

Excel applies format to character by position in a string, and this can't be done via formula.

Workarounds:
1. Use VBA to format string and update upon user action.
2. Use or create custom font that meets your need (note that you need to embed or ship the font along with the file)

Edit: To clarify, format is applied to each cell and can't be carried over using formula.
 
Last edited:
Hi ,

Chihiro has already posted that it cannot be done using formulae.

One workaround is possible if the number of text strings that can be entered in cell B2 is known to you.

If so , all that is required is to use a lookup table to retrieve the formatted text , and then point the textbox to this cell which contains the retrieved formatted text.

Would that be possible in your case ?

All this is assuming that VBA is out of the question ; if VBA can be used , then the solution is simple and straightforward.

Narayan
 
There would only be two possible text strings in B2. I've tried various things, but the obstacle is that no formula will format the text string the way I want it (small caps). VBA could possibly work, but I'm not skilled enough to use it.
 
PaulFogel123 said:
There would only be two possible text strings in B2. I've tried various things, but the obstacle is that no formula will format the text string the way I want it (small caps). VBA could possibly work, but I'm not skilled enough to use it.
Click to expand...
Hi ,

If you can upload your workbook , the code is fairly simple , and can be written.

Narayan
 
Hi ,

See the attached file.

A constant SHAPENAME has been defined , and is at present containing the text Rectangle 1.

Any text that is entered in cell B2 , will be displayed in this shape with the starting letters in a bigger font size.

If you change the constant SHAPENAME to point to the text box named Text Box 1 , any text that is entered in cell B2 , will be displayed in this text box with the starting letters in a bigger font size.

Narayan
 

Attachments

  • Workbook1 (1).xlsm
    17.2 KB · Views: 4
Brilliant. I'm impressed. Exactly what I needed--except for one modification, if you would. B2 will be a formula that refers to a label elsewhere in the workbook. If I type a cell reference in B2, Rectangle 1 doesn't do anything with it. Only a manual entry in B2 will work. Can this be changed?
Thank you.
 
Hi ,

There is a problem with what you want done - the Worksheet_Change event is triggered only by user-driven changes , not formula-driven ones.

Thus , if B2 contains a formula , and the text box / shape refers to B2 , when the contents of B2 change because of the formula , the Worksheet_Change event will not be triggered.

Can we use the precedent cell to which B2 is referring ?

If we can use that cell which is being manually changed , then we can make use of the same code without too much of a change.

Narayan
 
Hi Narayan,
The precedent cell is not manually changed either. The situation is that the user chooses an option from a dropdown list, which changes certain cells that will act as labels for text boxes that I'm using for graphs. These labels all use small caps format. So I need a way to trigger an event without manual input.

What about if the user merely navigates to the tab in question? Would that trigger an event? Maybe Private Sub Worksheet_Activate() would do the job.
 
Hi ,

Selection through a dropdown list is a user driven change ; the Worksheet_Change event will be triggered.

Narayan
 
The selection from the user list, in combination with other selections from other dropdown lists, changes various labels in the workbook. These dropdown lists are on different tabs than the one in which my text box resides. It is these labels that I want my text box to refer to. I can't change the string of references to make it trigger directly to the text box in question.
 
Hi ,

If you can upload your workbook , we may be able to find a solution.

The text modifying routine can be called from where ever the labels are being changed using dropdowns.

Narayan
 
The dropdowns drive graph labels for many different sheets, and they aren't direct. For example, a dropdown list choice of Equivalent Discharges drives labels of Equivalent Visits and CMI-Wtd Discharges and others, things that naturally flow downstream from those choices. In the sample workbook, choosing Hospital from the dropdown list triggers the labels of Medicare Discharges, Commercial Discharges, and a host of others. The full workbook is 32 MB and has confidential patient data, so if you can, please work with the sample.
 
Hi ,

The data is not required ; you can clear all of the data and upload a file which contains only the dropdowns , and mention the cells which need to be formatted.

Narayan
 
Since the dropdowns don't link to the labels directly, but produce other text, themselves based on formulas, the basic problem remains--how to trigger an event without manual input in a cell. I thought that maybe selecting another tab and then coming back to the original tab would do it, but I guess not. Your code up to this point went beyond what anyone else came up with. But thanks for trying; I do appreciate it.
Paul
 
Sorry to be such a bother, but I experimented with Worksheet_Activate but I don't know where it goes or the other commands needed. It would seem to replace the first line below.

Private Sub Worksheet_Change(ByVal Target As Range)
Const SHAPENAME = "Rectangle 1"
If Application.Intersect(Target, Range("$B$2")) Is Nothing Then Exit Sub

If Target.Count > 1 Then Exit Sub

With Me.Shapes(SHAPENAME).TextEffect
.Text = Target
.FontBold = msoTrue
.FontSize = 16
End With

i = 1
With Me.Shapes(SHAPENAME).TextFrame.Characters(i, 1).Font
.Size = 18
End With

startofword = False

For i = 2 To Len(Target)
If ((startofword) And (Mid(Target, i, 1) <> " ")) Then
startofword = False

With Me.Shapes(SHAPENAME).TextFrame.Characters(i, 1).Font
.Size = 18
End With
End If

If Mid(Target, i, 1) = " " Then startofword = True
Next
End Sub
 
Hi ,

The change from a Worksheet_Change event procedure to a Worksheet_Activate event procedure is substantial ; can you mention the worksheet cell which has the dropdown , and the name of the shape which is displaying the text ?

Narayan
 
Attached is something analogous. Two dropdown lists are on a separate tab, and the label formula is on the first tab. The actual formulas in my workbook are more complex, but this is the basic idea. The text box needs to reflect whatever's in B2 without manual entry.
Paul
 

Attachments

  • Workbook1 (1).xlsm
    22.5 KB · Views: 2
Narayan,
This works perfectly. I'll bet not one in a thousand experienced Excel users could pull this off. Thank you so much. I'm sending a well-deserved donation.
Paul
 
You must log in or register to reply here.
Back
Top