Excel VBA Code To Change a Frame’s Properties

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

42036-Frame1

Reply | Quote

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.

Reply | Quote

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.

42042-MeKeyword1

HTH,
Maud

Reply | Quote

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.

42043-JAATR1
42044-JAATR2

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote