Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    Quebec City, Quebec, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Modify format of part of cell (2002)

    Anyone know how to create a macro or VBA to modify part of a cell format ?
    I very often need to use Greek symbols and subscripts within a cell for writing mathematical equations.
    (Example: tmin = MAX (a, t, tn) where the min is a subscript, and the a should be the Greek alpha)
    What I do is F2 the cell to edit it, select the text to change using the keyboard arrows (the mouse is too slow), then use the mouse to select the font drop-down list, type "s" to get near to the Symbols font to get the Greek equivalent of the letter selected. Then I use the keyboard to move to the desired subscript text, select it, move my hand back to the mouse to click on the "Font point-size decrease" button twice, and while I'm there, click on the Italics button. And so on, and so on...
    I've tried writing a macro, but it only applies to the full cell content. Once in Edit mode, it doesn't activate.
    Any ideas ???

  2. #2
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify format of part of cell (2002)

    Kevin,

    If the equation is just for display, you could place a textbox on the worksheet and type out your equation, drag the box to where you want it, then right click on the textbox border and select no fill and no line.

    Maybe not the best solution....but it works.

    Brent

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify format of part of cell (2002)

    There is another workaround. Have a spare workbook with the target formats in it. Use Ctrl + C and Ctrl + V to Copy and Paste into the <big>Formula Bar</big>. The Font Property in VBA applies to a Range - minimum size an entire cell (as you have found out). HTH
    Gre

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Modify format of part of cell (2002)

    On the example you provided the following should convert the min part to subscript for each cell in the selection. Actually what it does is work on characters 2 to 4 inclusive regardless of what they are. You may be able to adapt it for your needs. <pre>Dim oCell As Range
    For Each oCell In Selection
    With oCell.Characters(Start:=2, Length:=3).Font
    .Subscript = True
    End With
    Next oCell</pre>

    Andrew C

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify format of part of cell (2002)

    Good point, Andrew. On that basis, should he not be able to set up a toolbar with a fair number of his desired options linked into macros?
    Gre

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Modify format of part of cell (2002)

    He could, and using Instr and InstrRev might be able to introduce variables as to the actual characters to be operated on. Plenty of scope for experimentation at least.

    Andrew

  7. #7
    Lounger
    Join Date
    Mar 2002
    Location
    Quebec City, Quebec, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify format of part of cell (2002)

    It's not quite as easy as that unfortunately. Because the 'min' part is just an example, and the position of the letters to be subscripted or converted to Greek is never the same from one line to the next.
    I think the solution for me would be to have a short-cut key for each of the 3 types of conversion.
    One exists already. I can use Ctrl-I for converting the subscript to italics.
    I need another for reducing the size of the subscript to 8,
    and a third for converting font to Symbol.
    With those 3 shortcuts, I could convert all I need without touching the mouse.
    That's the only way to do it when the text to convert is never in the same place.

    But how can I associate a shortcut key with an existing button? The button to reduce the font size by one point is already on my tool bar, but doesn't have an associated short-cut (I think).

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify format of part of cell (2002)

    Hi Unkamunka,

    <<The Font Property in VBA applies to a Range - minimum size an entire cell (as you have found out).>>

    No it doesn't.

    Activecell.characters(5,10).Font.Size=24
    Sets the size of characters 5 to 14 to 24

    Unfortunately one cannot run any code while editing a cell.

    But one could use special characters to delimit the text that needs special formatting, e.g. _this_ causes the word This to be subscript, ^this^ to make it superscript, etcetera. Then use a macro that searches for these special characters, removes them and formats the area they enclosed appropriately.
    Beats all the clicking, selecting and dragging the original poster experiences.

    I believe there is an insert symbol utility out there somewhere. maybe at:

    www.cpearson.com
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify format of part of cell (2002)

    If you read Andrew's second post more carefully - the same point was made this morning by Jan Karel - you will see that you can set up a Macro using the Instr and InstrRev functions. (Look in the VBA Help files - the functions will search for "_" - or whatever delimiter you choose.) You then attach that macro to toolbar icons (Customise|Attach Macro) - linking the macro to a keyboard shortcut. Someone else may come up with some code before I get the chance. HTH
    Gre

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify format of part of cell (2002)

    Here it is.

    Option Explicit
    Dim iStart() As Integer
    Dim iEnd() As Integer
    Dim iCount As Integer
    Dim sChar() As String
    Const sChars As String = "_^%&#"

    Sub ChangeLocalFormats()
    Dim sCellTxt As String
    Dim sTemp As String
    Dim iLoop As Integer
    iCount = 1
    ReDim sChar(1)
    ReDim iStart(1)
    ReDim iEnd(1)
    sCellTxt = ActiveCell.Value
    If Left(ActiveCell.Formula, 1) = "=" Then Exit Sub

    For iLoop = 1 To Len(sCellTxt)
    sTemp = Mid(sCellTxt, iLoop, 1)
    If iLoop > Len(sCellTxt) Then Exit For
    If InStr(sChars, sTemp) > 0 And Not sTemp = "" Then
    ReDim Preserve sChar(iCount)
    ReDim Preserve iStart(iCount)
    ReDim Preserve iEnd(iCount)
    sChar(iCount) = sTemp
    GetStartEnd sCellTxt, sTemp
    ActiveCell.Replace sTemp, "", xlPart, , True
    sCellTxt = ActiveCell.Value
    iCount = iCount + 1
    iLoop = 0
    End If
    Next
    For iLoop = 1 To iCount - 1
    If iStart(iLoop) > 0 Then
    With ActiveCell.Characters(iStart(iLoop), iEnd(iLoop) - iStart(iLoop))
    Select Case InStr(sChars, sChar(iLoop))
    Case 1
    .Font.Subscript = True
    Case 2
    .Font.Superscript = True
    Case 3
    .Font.Italic = True
    Case 4
    .Font.Bold = True
    Case 5
    .Font.Name = "Symbol"
    End Select
    End With
    End If
    Next
    End Sub
    Sub GetStartEnd(sCellTxt As String, sChar As String)
    iStart(iCount) = InStr(1, sCellTxt, sChar)
    iEnd(iCount) = InStr(iStart(iCount) + 1, sCellTxt, sChar) - 1
    End Sub


    So in this:

    _ sets subscript
    ^ sets superscript
    % sets italic
    & sets Bold
    # sets Symbol font

    Test it on this text:

    ^Super^_sub_%italic%&bold&#greek#

    You can even nest formatting characters:

    ^super&boldsuper&^
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Lounger
    Join Date
    Mar 2002
    Location
    Quebec City, Quebec, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify format of part of cell (2002)

    Wow !!!!!!!!
    What an effort !!
    I pasted it into a VB module of my Personal.xls, and applied it to a custom button.
    It works perfectly! Genial stuff. I am so amazed that I can't work. I'll have to take a few minutes off.
    Thanks - great effort.

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Modify format of part of cell (2002)

    Brilliant!
    -John ... I float in liquid gardens
    UTC -7DS

  13. #13
    Lounger
    Join Date
    Mar 2002
    Location
    Quebec City, Quebec, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify format of part of cell (2002)

    Jan, sometimes I have more than one place in the equation where I need a subscript or a Greek symbol.
    Only the first of each seems to get processed.
    Example: f_r_ + #a# + S_n_ + #b#

    If I edit a second time to get the second ocurrence of each type right, the first occurrence gets undone.

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify format of part of cell (2002)

    <blush>
    <g>

    It took me about one hour to put it together.
    Great you like it.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modify format of part of cell (2002)

    Oh yeah, I see why, the clue is in the line with the replace method, which replaces all occurrances of the chartacter in question. Bad design <g>.

    No time now to develop a workaround though.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •