View Single Post
Old 08-15-2018, 06:45 AM   #6
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,567
Thanks: 25
Thanked 471 Times in 447 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Set a group of controls as visible/hidden, enabled/disabled or locked/unlocked

@Ridder
This is a good concept of using the TAG property to update multiple controls, but I think you have may lose the average user in some areas. Less may be more. I recommend get rid of this
Code:
Public Sub EnableControls(State As Boolean, Tg1 As String, Optional Tg2 As String, Optional Tg3 As String, _
        Optional Tg4 As String, Optional Tg5 As String, Optional Tg6 As String)

On Error GoTo Err_Handler

    'set controls to locked or not according to the control tag value
     For Each ctrl In Screen.ActiveForm.Controls
        Select Case ctrl.ControlType
        
        Case acLabel, acImage, acLine, acRectangle, acPageBreak
            'no code here - these can't be disabled
        Case Else
            If ctrl.Tag = Tg1 Or ctrl.Tag = Tg2 Or ctrl.Tag = Tg3 Or ctrl.Tag = Tg4 _
                    Or ctrl.Tag = Tg5 Or ctrl.Tag = Tg6 Then ctrl.Enabled = State
        End Select
        
    Next ctrl
  
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " " & Err.description
    Resume Exit_Handler
    
End Sub
You make your code less generic (unless you get rid of those tag parameters and add a ParamArray). Recommend replace simply with the below code because the multiple tags does not add to what you are trying to demonstrate and only adds a level of complexity. I understand you are trying to be efficient, but does not really help show what you are doing.

Code:
Public Sub EnableControls(State As Boolean, TheTag as string)
On Error GoTo Err_Handler
    'set controls to Enabled or not according to the control tag value
     For Each ctrl In Screen.ActiveForm.Controls
        If ctrl.Tag = TheTag Then ctrl.Enabled = State
    Next ctrl
 
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " " & Err.description
    Resume Exit_Handler
    
End Sub
Then modify the call from
Code:
EnableControls True, "A", "B", "C", "D"
    ShowControls True, "A", "B", "C", "D"
    LockControls False, "A", "B", "C", "D"
to the longer but simpler for the average user to follow
Code:
    EnableControls True, "A" 
    EnableControls True, "B"
    EnableControls True, "C"
    EnableControls True, "D" 
    ShowControls True, "A"
    ....
    ShowControls True, "D"
    LockControls False, "A"
    ...
    LoctControls False, "D"
Also may want to label the controlType subform as for information only. I was lost on what its purpose was. I assumed you actually used it to determine if a control had the property, but you do not it is for visibility only. If you do then I recommend adding the field controltypevalue.
ControlTypeValue 'Long
Code:
ControlType	ControlTypeValue
acLabel         100
acRectangle     101
acCommandButton 104
acOptionButton  105
acCheckBox      106
acOptionGroup   107
acTextBox       109
acListBox       110
acComboBox      111
acTabCtl        123
....
Then if you made the field names the actual Property name and made the values boolean (Show/Hide to Visible, Enable/Disable to Enable, Lock/Unlock to Lock) you could actually check this table to see if a property is settable
Code:
Public Function HasProperty(PropName As String, ControlType As Long) As Boolean
  'This may be more work than it is worth. You would have to keep the table updated. You are probably better off just trapping the error if the property does not exist
  HasProperty = Nz(DLookup(PropName, "tblControlTypes", "ControlTypeValue = " & ControlType), False)
End Function
However, it may just be quicker to try to set a property that does not exist and throw the error.
MajP is offline   Reply With Quote