Event for all controls on form (1 Viewer)

[steve1111]

Hello,

I have a form that has a lot of textboxes on it. I have two Public Subs (cOn and cOff) that when the control in On Enter it highlights the control and bolds the label and On Exit turns that control back to normal.

My question is rather than going through each control on the form and setting the On Enter to Call cOn and the On Exit to cOff, is there a way that can be written in VBA for all the controls on the form.

On other piece i am trying to figure out is not all my contols have a label so when i access the Public sub throws in error.

me.ActiveControl.Controls.Item(0).FontBold = True

Thanks for the suggestions!

 

[MarkK]

Have you tried doing this with conditional formatting instead? I expect that would be easier.

Yes, you can programmatically enumerate all the controls on the form with code like . . .

dim ctrl as Access.Control
for each ctrl in me.controls
   select case ctrl.controltype
      case acTextBox, acComboBox
         ctrl.OnEnter = "=cOn()"
         ctrl.OnExit = "=cOff()"
   end select
next

. . . and set properties of those controls in the loop.

In this case . . .

me.ActiveControl.Controls.Item(0).FontBold = True

. . . check if the controls collection contains a control first, like . . .

with me.activecontrol.controls
   if .count > 0 then .item(0).fontbold = true
end with

See what's going on there?

Cheers,

 

[steve1111]

Thanks for both suggestions, i didn't even consider conditional formatting.

 

[arnelgp]

you can create a class module.
sample name the class module as clsTextBox

Option Compare Database
Option Explicit

Private WithEvents mTextBox As Access.TextBox
Private mLabel As Access.Label
Private mlngTextBackColor As Long
Private mbolLabelBold As Boolean

Const EventHook As String = "[Event Procedure]"


Public Sub Init(t As Access.TextBox, Optional l As Access.Label)
    Set mTextBox = t
    mlngTextBackColor = t.BackColor
    If Not (l Is Nothing) Then
        Set mLabel = l
        mbolLabelBold = l.FontBold
    End If
    mTextBox.OnEnter = EventHook
    mTextBox.OnExit = EventHook
End Sub


Private Sub Class_Terminate()
    Set mTextBox = Nothing
    Set mLabel = Nothing
End Sub

Private Sub mTextBox_Enter()
    mTextBox.BackColor = vbYellow
    If Not (mLabel Is Nothing) Then
        mLabel.FontBold = True
    End If
End Sub

Private Sub mTextBox_Exit(Cancel As Integer)
    mTextBox.BackColor = mlngTextBackColor
    If Not (mLabel Is Nothing) Then
        mLabel.FontBold = mbolLabelBold
    End If
End Sub

the textbox will get yellow background on enter event.
associated label control bold.
on exit, colors and bold attributes reinstated
on your form's event:

Option Compare Database
Option Explicit
'this is an example of form with 3 textbox
'two label control on Text0 and Text2
'Text4 has no associate label
Private mText1 As clsTextBox
Private mText2 As clsTextBox
Private mText3 As clsTextBox

Private Sub Form_Close()
    Set mText1 = Nothing
    Set mText2 = Nothing
    Set mText3 = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)

    Set mText1 = New clsTextBox
    Set mText2 = New clsTextBox
    Set mText3 = New clsTextBox
    
    Call mText1.Init(Me.Text0, Me.Label1)
    Call mText2.Init(Me.Text2, Me.Label3)
    Call mtext3.Init(Me.Text4)
    
End Sub