+ Reply to Thread
Results 1 to 7 of 7

Color command button font

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,968

    Color command button font

    The code below was created to be used in Excel 2003, and worked fine to create two command buttons, one to cancel the automation, one to resume it. Now I'm on 2010, and when I run it I get an error code saying the font size has to be between 1 and 409 points. However, the highlighted code is the
    .Color = vbBlue (or any other color)
    I'd appreciate some direction in how to revamp just that part of the code. The rest of the functionality still works great. Full code is pasted below. Thanks in advance.
    Sub AddButtons()
        Dim strBname As String
        ActiveSheet.Buttons.Add(200, 40, 81, 50).Select
        strBname = Selection.Name
        Selection.OnAction = "ResumeAuto" 'Macro to run
        Selection.ShapeRange.Fill.ForeColor.SchemeColor = 46
        Selection.Characters.Text = "Click here to Resume the Automation" 'Text of button
        With Selection.Characters(Start:=1, Length:=14).Font '
            .Name = "Verdana"
            .FontStyle = "Regular"
            .Size = 9
            .Color = vbBlue
        End With
        With Selection.Characters(Start:=15, Length:=6).Font 'cut out (start...) if doing whole range
            .Name = "Verdana"
            .FontStyle = "Bold"
            .Size = 9
            .Color = vbRed
        End With
        With Selection.Characters(Start:=22, Length:=14).Font '
            .Name = "Verdana"
            .FontStyle = "Regular"
            .Size = 9
            .Color = vbBlue
        End With
        ActiveSheet.Buttons.Add(325, 40, 81, 50).Select
        strBname = Selection.Name
        Selection.OnAction = "KillButts" 'Macro to run
        Selection.Characters.Text = "Click here to Cancel the Automation" 'Text of button
        With Selection.Characters(Start:=1, Length:=14).Font '
            .Name = "Verdana"
            .FontStyle = "Regular"
            .Size = 9
            .Color = vbBlue
        End With
        With Selection.Characters(Start:=15, Length:=6).Font 'cut out (start...) if doing whole range
            .Name = "Verdana"
            .FontStyle = "Bold"
            .Size = 9
            .Color = vbRed
        End With
        With Selection.Characters(Start:=22, Length:=14).Font '
            .Name = "Verdana"
            .FontStyle = "Regular"
            .Size = 9
            .Color = vbBlue
        End With
        Range("A1").Select
    End Sub
    
    Sub ResumeAuto()
    Run "Killbutts"
    MsgBox "Now we're resuming our process"
    End Sub
    
    Sub Killbutts()
    'Activate sheet to delete autoshapes.
    Dim GetShape As Shape
    
        For Each GetShape In ActiveSheet.Shapes
            GetShape.Delete
        Next
    
    End Sub
    Last edited by jomili; 06-07-2012 at 10:16 AM.

  2. #2
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Color command button font

    I had no problems in E 2010 using:

    Sub AddButtons_snb()
      For j = 1 To 2
        With ActiveSheet.Buttons.Add(Choose(j, 200, 325), 40, 81, 50)
          .OnAction = Choose(j, "ResumeAuto", "KillButts")
          .Characters.Text = "Click here to " & Choose(j, "Resume", "Cancel") & " the Automation"
    
          With .Characters(1, 36).Font
            .Name = "Verdana"
            .Size = 9
            .Color = vbBlue
          End With
    
          With .Characters(15, 6).Font
            .FontStyle = "Bold"
            .Color = vbRed
          End With
    
        End With
      Next
    End Sub



  3. #3
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,968

    Re: Color command button font

    And I had no problem using your code, either. Neat the way you worked it out; where I was working on the first part of the font on one button, then the middle part, then the ending part, your code colors all the font, then recolors the middle ("Resume" or "Cancel"), then creates the next button when J changes to 2, so it's all the same code, instead of different code for each button. Cool! Definitely more elegant, but if "Resume" and "Cancel" (both 6 characters long) were to be replaced with "Stop" and "Start" (4 and 5 characters) you'd have to step out the code more like my original code, wouldn't you?

    I still don't understand why my original code was producing an error. Can you advise on that, just to help with my understanding?

    Thanks,
    John

  4. #4
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Color command button font

    Only a slight modification though:

    With .Characters(15, choose(j,6,5)).Font
            .FontStyle = "Bold"
            .Color = vbRed
          End With

    I still don't understand why my original code was producing an error
    I 'aven't got the faintest....
    But avoiding 'select' and activate is always recommendable.
    And I left out defaultvalues.
    Last edited by snb; 06-07-2012 at 09:16 AM.

  5. #5
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,968

    Re: Color command button font

    I don't think "recommendable" is a word, but it certainly fits. On your altered code below:
    With .Characters(15, choose(j,6,5)).Font
            .FontStyle = "Bold"
            .Color = vbRed
          End With
    I understand what you're doing; if J is 1, then at the 15th character, for the next 6, color it red. If J is 2, then at the 15th character, for the next 5, color it red. I like the way you did that, and didn't see that type of solution myself. I think with that example I can use this routine in a few different applications. Thanks for showing me how.

  6. #6
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Color command button font

    I don't think "recommendable" is a word
    I know it is, see:


    http://www.websters-online-dictionar.../recommendable

  7. #7
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,968

    Re: Color command button font

    Well then, I say your posts are recommendable.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1