Universal code for textbox properties

[sfrvn]

I often have forms with (too) many textboxes. To assist users, I
prefer to change the back color of the textbox when it has the focus.
It is easy for the User to locate where they are on the form. However,
coding each textbox's GotFocus and LostFocus event is very tedious if
you use the Me![tbxName].BackColor = 12713983 syntax for these
events. The problem stems from having to use the specific textbox name
for each event for each control. Cut-and-Paste can only do so much.
Even after searching the newsgroups, I failed to find an easy method to
accomplish this goal.

Using info I found in the newsgroups, I constructed the following code
together. The value of the code rests in its 'universal' nature. Once
you create the GotFocus and LostFocus events for the textboxes, you
simply copy/paste the appropriate code into each 'sub'. You do not
need to specify the name of the control. This makes the process of
customizing 50 textboxes x 2 events much easier.

Sample code follows:

Private Sub tbxName_GotFocus()

'Copy the following 3 lines into all GotFocus events
Dim CurrCtl As String
CurrCtl = Screen.ActiveControl.Name
Me(CurrCtl).BackColor = 12713983

End Sub

Private Sub tbxName_LostFocus()

'Copy the following 3 lines into all LostFocus events
Dim CurrCtl As String
CurrCtl = Screen.ActiveControl.Name
Me(CurrCtl).BackColor = 16777215

End Sub

Obviously, you can adapt the code to change textbox properties as you
see fit.

Hope you find this useful.

Gary B

 

[Klatuu]

Here is one that takes even less work. All you need to do is enter
=SetColor() in your GotFocus and LostFocus property boxes for your text
boxes.
Here is the code:

Public Function SetColor(Optional varNewColor As Variant) As Boolean
Dim frm As Form
Dim ctl As Control

Set frm = Screen.ActiveForm
Set ctl = frm.ActiveControl
If IsMissing(varNewColor) Then
ctl.BackColor = IIf(ctl.BackColor = 16777215, 12713983, 16777215)
Else
ctl.BackColor = varNewColor
End If
Set frm = Nothing
Set ctl = Nothing

End Function

One additional advantage is that even if you have changes the color to
something other than the standard white or the light yellow focus, it will
change it back to white. Also, you can use the optional variable to set to a
specific color for that control or if in any of the events for your control
you can set the color. For example, in the Before Update event when the
value entered isn't correct:

If IsNull(Me.txtBlah) Then
SetColor(vbRed)
MsgBox "A Value is Required for This Field"
Cancel = True
End If

 

[sfrvn]

Here is one that takes even less work. All you need to do is enter
=SetColor() in your GotFocus and LostFocus property boxes for your text
boxes.
Here is the code:

[code snipped!]

Thanks for a newer, faster, cheaper, ... method. I'm always looking
for these attributes. Unfortunately, when I tried your function, I got
an error:

Compile error:

Expected: line number or label or statement or end of statement

I added a new module and pasted your code into the module. When I type
the phrase " =SetColor() " <no quotes> into the 'GotFocus' event
for a textbox, the error pops up.

<scratching head!>

I understand what the function does, but I don't know enough to have
been able to sit down and write the function from scratch. That means
I need help to solve this issue.

Any suggestions, Klatuu??

Thanks!

 

[sfrvn]

Klatuu...

I figured it out.

I reasoned that I was not trying to set the 'value' ( meaning = ) of
the GotFocus event... my goal was to 'run' the function. So I dug
deeply and remembered that you can 'call' a function. So I tried...

Call SetColor() ...works like a champ. Thanks, Klatuu... a
tip-o-the-hat to you, sir, for you excellent suggestion!

gary b

 

[Klatuu]

Happy to be of assistance.