+ Reply to Thread
Results 1 to 3 of 3

Loop through controls in UserForm

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-06-2008
    Location
    Manchester
    MS-Off Ver
    MS Office 2003
    Posts
    161

    Loop through controls in UserForm

    I have taken this code from Access and trying to use it in Excel to lock all the TextBox, ComboBox & CheckBox on the form.

    Dim ctlControl As Control
    
    For Each ctlControl In usrfrmReport.Controls
            If ctlControl.ControlType = acTextBox Or _
            ctlControl.ControlType = acComboBox Or _
            ctlControl.ControlType = acCheckBox Then
                ctlControl.Enabled = False
            End If
        Next
    In Access this works fine but trying it Excel it tells me variable not defined and highlights the acTextBox as the problem.

    How can I get around this as otherwise I have to code every single control and this would make my code enormous?
    Last edited by JezLisle; 01-23-2009 at 08:04 AM.

  2. #2
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    Try with this code:
    Dim ctlControl As Control
    
    For Each ctlControl In usrfrmReport.Controls
        If TypeOf Control is TextBox Or _
            TypeOf Control is ComboBox Or _
            TypeOf Control is CheckBox Then
            ctlControl.Enabled = False
        End If
    Next
    Regards,
    Antonio

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    I always dim controls as MSForms controls, I have had problems with just using Control. Try this
    Dim ctlControl As MSForms.Control
        For Each ctlControl In Me.Controls
    
            Select Case TypeName(ctlControl)
                Case "TextBox", "ComboBox", "CheckBox"
                    'disable
                    ctlControl.Enabled = False
                    'alternative - toggle status
                    'tlControl.Enabled = Not ctlControl.Enabled
            End Select
        Next ctlControl
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ 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