Set all values of a form to enabled = false

[Geordie2008]

Hi all,

This might be a double post, but no-one in the VBA forum seems to know...

When opening a form, how do I set the value of all of the ComboBoxes and Text Boxes to be enabled = false (without having to individually name them all?)

I will then enable the ones I want to enable based on criteria.

Any help mucho appreciated! I have tried searches, but got no results on this.

thanks,
Mandy

 

[mhartman]

Hello

You would loop through the controls collection to do this:

Put the following under a command button click event and run:

Dim ctl As Control
For Each ctl In Me.Controls
MsgBox ctl.Name
With ctl
Select Case .ControlType
Case acLabel
If .SpecialEffect = acEffectShadow Then
.SpecialEffect = acEffectNormal
.BorderStyle = conTransparent
Else
.SpecialEffect = acEffectShadow
End If
Case acTextBox
If .SpecialEffect = acEffectNormal Then
.SpecialEffect = acEffectSunken
.BackColor = conWhite
Else
.SpecialEffect = acEffectNormal
.BackColor = Me.Detail.BackColor
End If
End Select
End With
Next ctl

Regards
Mark

 

[odin1701]

If you want to do this every time - this is what you can do, you don't need to toggle like the above code - and you just need to work with the .enabled you don't need to be changing border styles, etc.

Dim ctl As Control

For Each ctl In Me.Controls

With ctl

Select Case .ControlType

Case acTextBox

     .Enabled = False
    
Case acComboBox
    
     .Enabled = False
    
End Select
End With
Next ctl

 

[Geordie2008]

thats great stuff... thanks so much!
Mandy

 

[Geordie2008]

I have this working great with the button,

When I change it to be on the On_Change of a combobox that determines if they are all enabled, or all disabled, it will not allow me to change it to enabled = false.

It says "you cant disable a control while it has focus"

Is there any way to set the focus of the form as nothing? Or could I set the focus to say a unbound text label?

I though it would be something along the lines of:

Me.lbl_previous_update.SetFocus

but this bombed out...
Thanks
Mandy

 

[KenHigg]

You don't have any other controls on the form that you can set the focus to?

 

[gemma-the-husky]

its not allowing you to change the setting for the current combo box , because you are IN IT.

presumably you dont want to lock the combo box that determines the enabled status

so

Dim ctl As Control

For Each ctl In Me.Controls

  With ctl
    {perhaps just ctl<>activecontrol}
    if ctl.name<>activecontrol.name then
       Select Case .ControlType
       Case acTextBox: .Enabled = False
       Case acComboBox: .Enabled = False
       End Select
    end if
  End With
Next ctl

 

[Geordie2008]

I updated the code to the following, hence I already use an if statement, Im not sure how to adapt to include your code (if ctl.name<>activecontrol.name then)

If I could get the code to skip if the ctl = "Employee_Indicator" this would also work as I never need to disable that field...

My idea was to disable all fields and then re-enable the ones I want as it was a shorter list...

Thanks,
Mandy


Private Sub cbo_Employee_Indicator_Change()

Dim ctl As Control

If Me.cbo_Employee_Indicator = "Non Employee" Then
For Each ctl In Me.Controls
With ctl
Select Case .ControlType
Case acTextBox
If .Enabled = False Then
.Enabled = False
Else
.Enabled = False
End If

Case acComboBox
If .Enabled = False Then
.Enabled = False
Else
.Enabled = False
End If
End Select
End With
Next ctl
Me.cbo_Employee_Indicator.Enabled = True
Me.cbo_Reason_for_leaving.Enabled = True
Me.cbo_Replaced.Enabled = True
Me.txt_Replaced_by.Enabled = True
End If

If Me.cbo_Employee_Indicator = "Employee" And Me.Employee_Status = "Inactive" Then
For Each ctl In Me.Controls
With ctl
Select Case .ControlType
Case acTextBox
If .Enabled = False Then
.Enabled = False
Else
.Enabled = False
End If

Case acComboBox
If .Enabled = False Then
.Enabled = False
Else
.Enabled = False
End If
End Select
End With
Next ctl
Me.cbo_Employee_Indicator.Enabled = True
Me.cbo_Employee_Status.Enabled = True
Me.cbo_Inactive_Status.Enabled = True
End If

If Me.cbo_Employee_Indicator = "Employee" And Me.Employee_Status = "Active" Then
For Each ctl In Me.Controls
With ctl
Select Case .ControlType
Case acTextBox
If .Enabled = False Then
.Enabled = True
Else
.Enabled = True
End If

Case acComboBox
If .Enabled = False Then
.Enabled = True
Else
.Enabled = True
End If
End Select
End With
Next ctl
Me.cbo_Inactive_Status.Enabled = False
Me.cbo_Reason_for_leaving.Enabled = False
Me.cbo_Replaced.Enabled = False
Me.txt_Replaced_by.Enabled = False
End If

End If
End Sub

 

[KenHigg]

My idea was to disable all fields and then re-enable the ones I want as it was a shorter list...

If you don't mind me saying, at this point all of this looping is pointless. I would simply create and call proceedure that sets each of them one by one.

 

[gemma-the-husky]

same thing

the ctl.name property is what you want

if ctl.name = "Employee_Indicator" then etc

--------
the expression activecontrol merely references the current control, without you having to explicitly find it and name it.

so

if ctl.name = activecontrol.name then etc, is the same {the exact syntax might be screen.activecontrol}

 

[Geordie2008]

Ken, Im intrigued....

A call procedure? I'll do anything to keep this code as short and maintainable as possible!

 

[Rabbie]

If you don't mind me saying, at this point all of this looping is pointless. I would simply create and call proceedure that sets each of them one by one.

The original request was

When opening a form, how do I set the value of all of the ComboBoxes and Text Boxes to be enabled = false (without having to individually name them all?)

I will then enable the ones I want to enable based on criteria.

which may explain the answers given

 

[KenHigg]

Open the code for the form and create a new sub-procedure and put the code there.

Something like:

Turn this control on
Turn this control off
etc.


Then just call the sub where ever you need it.

 

[gemma-the-husky]

that was there the op started from i think

he wanted to avoid explicitly

mycontrol1.enabled = false
mycontrol2.enabled = false
mycontrol3.enabled = false
mycontrol4.enabled = false

etc

 

[KenHigg]

Yeah, reading back I see. Still all that looping, focus errors, etc, etc... Just code it up.

 

[odin1701]

Or if you named the controls in sequence.

Say you had 50 text boxes named txt1, txt2, ... txt50:

For i = 1 to 50

Me("txt" & i).Enabled = False

Next i

 

[gemma-the-husky]

i thnik you are right Ken - do it once and forget about it

sub setcontrols(setting as boolean)

box1.enabled = setting
box2.enabled = setting
box3.enabled = setting
box4.enabled = setting
box5.enabled = setting

end sub

at least this way, you cover all the boxes in one proc