?
Solved

How do I apply a property to all controls in a userform?

Posted on 2011-05-11
5
Medium Priority
?
304 Views
Last Modified: 2012-05-11
Hi

I have several controls (especially labels) which I would like to make transparent when the userform is initialized.

How do I loop through all controls and change their properties?

Thanks
0
Comment
Question by:Massimo Scola
  • 3
  • 2
5 Comments
 
LVL 77

Expert Comment

by:GrahamSkan
ID: 35738292
You will need something like this
Sub ModControls()
    Dim ctl As MSForms.Control
    For Each ctl In UserForm1.Controls
        If TypeName(ctl) = "Label" Then
            ctl.BackStyle = fmBackStyleTransparent
        End If
    Next ctl
End Sub

Open in new window

0
 

Author Comment

by:Massimo Scola
ID: 35738545
Graham: I get a message that a Object required is required.
Am I missing something?
0
 
LVL 77

Expert Comment

by:GrahamSkan
ID: 35738910
Hmm. It works OK for me. Which line gives the error?
0
 
LVL 77

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 35738950
I've now put it in the Initialise sub, and use Me instead of the form name
Private Sub UserForm_Initialize()
    Dim ctl As MSForms.Control
    For Each ctl In Me.Controls
        If TypeName(ctl) = "Label" Then
            ctl.BackStyle = fmBackStyleTransparent
        End If
    Next ctl
End Sub

Open in new window

0
 

Author Closing Comment

by:Massimo Scola
ID: 35753578
fantastic!
Thanks a lot!
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question