Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Aug 2015
    Posts
    11
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Excel VBA Code To Change a Frame's Properties

    Good evening Excel Experts,

    I have a UserForm with a Frame1; within the frame there are 4 radial (OptionButtons). The Frame and OptionButtons are disabled when the UserForm loads. I also have to 2 CheckBoxes on the UserForm. On selection of CheckBox2; Frame1 and the 4 OptionButtons are Enabled. I'm looking for code that would change the color and/or width (style) of the frame's border.

    Thanks

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    How about something like this:

    Code:
    Private Sub UserForm_Initialize()
    Me.Frame1.BorderColor = vbGreen
    Me.Frame1.ForeColor = vbRed
    End Sub
    There does not seem to be an adjustment to the thickness of the line but setting the Special effect to Flat and font to bold seems to make the frame bolder.

    HTH,
    Maud

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    An alternative would be to place a label on the form with a backcolor and border the desired color. In the image below, the label has a height and width both 6 greater than the frames, sent to the back, and centered behind the frame. The frames border is turned off. This gives the appearance of a thicker border.

    HTH

    Frame1.png

  4. #4
    New Lounger
    Join Date
    Aug 2015
    Posts
    11
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Maudibe,

    Thank you for the code; it's exactly what I was looking for. I have a followup question. I'm writing my first VBA code and noticed I could use your code without the preceding me. (e.g., Frame1.ForeColor = vbRed) and get the same results. What is the advantage of using the me. or disadvantage for not using it?

    Again, many thanks.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    I only got in the habit of using when two forms may be interacting. For example, if the textbox1 on form A influences or is influenced by a textbox on form B

    Me.TextBox1.Value = UserForm2.textbox7.Value

    The Me keyword helps me keep it straight which user form each textbox belongs to. If you are dealing with a control (ex. textbox) on the userform in the module of that userform then you do not need to specify Me or the userform name. However, if you are using a control on another userform (as above), you must specify the other userform's name.

    The other big advantage of the Me keyword is that you get the benefit of Intellisense. Typing Me followed by a period offers a dropdown with all the properties, methods, and controls to select from.

    MeKeyword1.png

    HTH,
    Maud

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JAATR,

    If your objective is to draw attention to the frame & option buttons an alternative is to hide the frame (the option buttons hide with it) and then unhide it when the checkbox is clicked.

    JAATR1.JPG
    JAATR2.JPG
    Code:
    Option Explicit
    
    Private Sub UserForm_Activate()
    
       
       With Me.Frame1
           .Visible = False
    '       .SpecialEffect = fmSpecialEffectSunken
    '       .BackColor = vbRed
       End With 'Frame1
    
    
    End Sub
    
    Private Sub cboxActivateFrame_Click()
    
       If cboxActivateFrame Then
       
         With Me.Frame1
             .Visible = True
    '         .SpecialEffect = fmSpecialEffectRaised
    '         .BackColor = vbGreen
         End With 'Frame1
    
       Else
       
         With Me.Frame1
             .Visible = False
    '         .SpecialEffect = fmSpecialEffectSunken
    '         .BackColor = vbRed
         End With 'Frame1
       
       End If
       
    End Sub
    Another option is to change the back color of the frame (see commented out lines above).

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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