Use VBA to enable a text box when a check box is checked

Question

I am new to VBA and I'm trying to create a form in Access where a text box is enabled or disabled based on whether a check box is checked or unchecked.

So if the 'Survey Requested?' box is checked the 'Date Survey Requested' box is enabled for the user to enter a date.

I have the following code:

  Private Sub CheckSurveyRequested_AfterUpdate()

If CheckSurveyRequested = True Then
    DateSurveyReq.Enabled = True
Else
    DateSurveyReq.Enabled = False
End If

End Sub

But this comes up with a '424 Object Required' error when I run line 5.

Anyone have a suggestion as to what I'm doing wrong here?

Remove the Set keyword. Just DateSurveyReq.Enabled = False. — jbud
Tip: DateSurveyReq.Enabled = CheckSurveyRequested.Value is a nice one-liner to achieve this. — Erik A
Sorry the Set is a typo, I've taken it out but still getting the same error — Alex
Could you please clarify how I can use DateSurveyReq.Enabled = CheckSurveyRequested.Value instead? that doesn't seem to be working for me either — Alex

Zack Zack · Accepted Answer · 2019-03-22T16:54:52

You should definitely use the AfterUpdate event---tying the behavior of your textbox to the click event means that a user using their keyboard to navigate the form won't get the same behavior.

Also, you should replicate this behavior when the form loads: If the default value for the checkbox is False, then your textbox should be disabled when the form loads.

Also, as @ErikA notes, you can do this with one readable line.

So I would recommend something like this:

Option Explicit

Private Sub chkSurveyRequested_AfterUpdate()
    Me.txtDateSurveyReq.Enabled = Me.chkSurveyRequested.value
End Sub

Private Sub Form_Load()
    Me.txtDateSurveyReq.Enabled = Me.chkSurveyRequested.value
End Sub

In order to not repeat yourself, you could move this code into a separate sub:

Option Explicit

Private Sub Form_Load()
    ' assign the function below to the AfterUpdate event of the checkbox.
    Me.chkSurveyRequested.AfterUpdate = "=UpdateControls()"

    ' now execute the function directly
    UpdateControls
End Sub

Private Function UpdateControls()
    Me.txtDateSurveyReq.Enabled = Me.chkSurveyRequested.value
End Function

Use VBA to enable a text box when a check box is checked

2 Answers