+ Reply to Thread
Results 1 to 8 of 8

Changing Shapes line color using VBA

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    09-07-2009
    Location
    Hemet, CA
    MS-Off Ver
    Office 2007 Professional
    Posts
    111

    Changing Shapes line color using VBA

    I just can’t seem to figure this one out. I’m using Excel 2007.
    And I have 3 different Rounded Rectangle Shapes on a single worksheet named Cover Page. I’m trying to figure out how to change the line color using VBA with option buttons. Here’s what I have so far. Option button 6 refers to a macro that opens a color chart for custom selection. Any suggestion would be greatly appreciated.


        Dim wsh As Worksheet
        Dim lngColor As Long
    
        If OptionButton6 Then
            lngColor = ShowColor
            If lngColor = -1 Then
                MsgBox "You didn't select a color!", vbExclamation
                Exit Sub
            End If
        End If
    
        Comment3.Hide
    
        Application.ScreenUpdating = False
    
        With Worksheets("Cover Page").Shapes
            .Line.OutlineColor
            Select Case True
            
            Case OptionButton1.Value
                .Color = RGB(146, 208, 80)
    
            Case OptionButton2.Value
                .Color = RGB(120, 220, 255)
    
            Case OptionButton3.Value
                .Color = RGB(216, 216, 216)
    
            Case OptionButton4.Value
                .Color = RGB(255, 220, 110)
    
            Case OptionButton5.Value
                .Color = RGB(250, 100, 95)
    
            Case OptionButton6.Value
                .Color = lngColor
    
            End Select
        End With
    
        For Each wsh In Worksheets
            wsh.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                        Password:="", UserInterFaceOnly:=True
    
            ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True
    
        Next wsh
        
        Application.ScreenUpdating = True
        
        'ActiveWorkbook.Save
    
    End Sub
    Last edited by ABabeNChrist; 11-23-2010 at 10:59 AM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: Changing Shapes line color using VBA

    More like

    ActiveSheet.Shapes(1).Line.ForeColor.RGB = 255 ' red border
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Contributor
    Join Date
    09-07-2009
    Location
    Hemet, CA
    MS-Off Ver
    Office 2007 Professional
    Posts
    111

    Re: Changing Shapes line color using VBA

    Thank you Andy
    I tried using a similar piece of code earlier that I found searching the internet, but it was without (1) and of course I had no success. I made changes to my code like so and it now works great.

    May I ask a couple quick questions?
    1. If I were to add a Rectangle to this same sheet, what would I need to add to include this also?
    2. This one might be the tricky one. Is it possible to add a Shape.Glow 11Point

    Here's a view of the changes I made.

        Application.ScreenUpdating = False
        With Worksheets("Cover Page").Shapes(1).Line
    
            Select Case True
    
            Case OptionButton1.Value
                .ForeColor.RGB = RGB(146, 208, 80)
    
            Case OptionButton2.Value
                .ForeColor.RGB = RGB(120, 220, 255)
    
            Case OptionButton3.Value
                .ForeColor.RGB = RGB(216, 216, 216)
    
            Case OptionButton4.Value
                .ForeColor.RGB = RGB(255, 220, 110)
    
            Case OptionButton5.Value
    
                .ForeColor.RGB = RGB(250, 100, 95)
    
            Case OptionButton6.Value
                .ForeColor.RGB = lngColor
    
            End Select
        End With

  4. #4
    Forum Contributor
    Join Date
    09-07-2009
    Location
    Hemet, CA
    MS-Off Ver
    Office 2007 Professional
    Posts
    111

    Re: Changing Shapes line color using VBA

    I figured out how to select more than 1 type of shape by using array approach.
    I changed this part

    With Worksheets("Cover Page").Shapes(1).Line

    With this

    With Worksheets("Cover Page").Shapes.Range(Array("Rounded Rectangle 1", "Rectangle 2")).Line
    And now I know how to include additional shapes if needed. I still haven’t figured out how to use Glow?

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,481

    Re: Changing Shapes line color using VBA

    The object model is not complete for the Glow formatting.

    But the glow will be solid as you can not set the transparency.

    Sub x()
        Dim shpTemp As Shape
        
        Set shpTemp = ActiveSheet.Shapes(1)
        With shpTemp.Glow
            .Radius = 11
           .Color.ObjectThemeColor = msoThemeColorAccent1
        End With
        
    End Sub

  6. #6
    Forum Contributor
    Join Date
    09-07-2009
    Location
    Hemet, CA
    MS-Off Ver
    Office 2007 Professional
    Posts
    111

    Re: Changing Shapes line color using VBA

    Hi Andy
    I was able to get the code to work when using this approach
    Option Explicit
    
    Private Sub CommandButton1_Click()
        Dim wsh As Worksheet
        Dim lngColor As Long
        Dim shpTemp As Shape
    
        If OptionButton6 Then
            lngColor = ShowColor
            If lngColor = -1 Then
                MsgBox "You didn't select a color!", vbExclamation
                Exit Sub
            End If
        End If
    
        Glow.Hide
    
        Application.ScreenUpdating = False
    
        Set shpTemp = Sheets("Cover Page").Shapes(1)
    
        With shpTemp.Glow
            .Radius = 11
    
            Select Case True
    
            Case OptionButton1.Value
                .Color.RGB = RGB(146, 208, 80)
    
            Case OptionButton2.Value
                .Color.RGB = RGB(120, 220, 255)
    
            Case OptionButton3.Value
                .Color.RGB = RGB(216, 216, 216)
    
            Case OptionButton4.Value
                .Color.RGB = RGB(255, 220, 110)
    
            Case OptionButton5.Value
                .Color.RGB = RGB(250, 100, 95)
    
            Case OptionButton6.Value
                .Color.RGB = lngColor
    
            End Select
        End With
    
        Application.ScreenUpdating = True
    
        Unload Glow
    
        ActiveWorkbook.Save
    
        MsgBox "Changes have been saved"
    
    End Sub
    But when I try to select more than one shape at a time using an array I get an error Run-time error ‘13’, type mismatch

     Set shpTemp = Sheets("Cover Page").Shapes.Range(Array("Rounded Rectangle 1", "Rectangle 2"))
    Any idea what it might be?

+ 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