1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Ask an Excel Question' started by PaulFogel123, Aug 1, 2017.

  1. PaulFogel123

    PaulFogel123 Member

    Messages:
    129
    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

    Attached Files:

  2. SirJB7

    SirJB7 Excel R┼Źnin

    Messages:
    8,894
    Hi, PaulFogel123!
    I'm afraid I don't understand to which "= formula" are you referring to. Could you please elaborate? Thank you.
    Regards!
  3. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,669
    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: Aug 1, 2017
  4. PaulFogel123

    PaulFogel123 Member

    Messages:
    129
    The text box in the attached file has an = sign that refers to cell B2.
  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,586
    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
  6. PaulFogel123

    PaulFogel123 Member

    Messages:
    129
    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.
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,586
    Hi ,

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

    Narayan
  8. PaulFogel123

    PaulFogel123 Member

    Messages:
    129
    Thanks! It's attached.

    Attached Files:

  9. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,586
    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

    Attached Files:

    Thomas Kuriakose likes this.
  10. PaulFogel123

    PaulFogel123 Member

    Messages:
    129
    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.
  11. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,586
    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
  12. PaulFogel123

    PaulFogel123 Member

    Messages:
    129
    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.
  13. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,586
    Hi ,

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

    Narayan
  14. PaulFogel123

    PaulFogel123 Member

    Messages:
    129
    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.
  15. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,586
    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
  16. PaulFogel123

    PaulFogel123 Member

    Messages:
    129
    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.
  17. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,586
    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
  18. PaulFogel123

    PaulFogel123 Member

    Messages:
    129
    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
  19. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,586
    Hi ,

    We can make use of the Worksheet_Activate or the Worksheet_Calculate events.

    Narayan
  20. PaulFogel123

    PaulFogel123 Member

    Messages:
    129
    Okay, I will try it. It's very late here (Pacific time), so I will try it in the morning.
  21. PaulFogel123

    PaulFogel123 Member

    Messages:
    129
    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
  22. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,586
    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
  23. PaulFogel123

    PaulFogel123 Member

    Messages:
    129
    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

    Attached Files:

  24. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,586
    Hi ,

    See the file now.

    Narayan

    Attached Files:

  25. PaulFogel123

    PaulFogel123 Member

    Messages:
    129
    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
    NARAYANK991 likes this.

Share This Page