Highlighting controls - vba code 1

[kjv1611]

I 2 simple lines of code I am currently using to show which control has the focus at any given moment. My problem is that with this code, I have to put it in for each control (of which there are many - some are hidden until other options are chosen). The code is this:
[blue]
Private Sub cmbOne_GotFocus()
cmbOne.BorderColor = vbBlue
End Sub

Private Sub cmbOne_LostFocus()
cmbOne.BorderColor = 52479
End Sub
[/blue]
I would like to be able to say something like :
[green]'where cmbCurrent simply stands for the active control[/green]
[blue]Private Sub cmbCurrent_GotFocus()
cmbCurrent.BorderColor = vbBlue
End Sub

Private Sub cmbCurrent_LostFocus()
cmbCurrent.BorderColor = 52479
End Sub[/blue]

 

[RoyVidar]

If you have access 2000+ versions, check out the conditional formatting (format menu)!

In the first dropdown, select "Field has focus", apply format, then...

(doesn't have border color, though)

Roy-Vidar

 

[kjv1611]

Thanks, I should have realized that is possible, becuase I use conditional formatting in some Excel spreadsheets. But for this purpose, I am wanting each control to change it's border color when selected. The code I am using works great, it will just will be a bunch of code when putting in the code for each control, when there are many controls. I am thankful for that piece of info, b/c I did not know that the conditional formatting option would include "Field has Focus". That would be a good bit of work as well, if I go that route, but not as much as the coding for the border color.

 

[RoyVidar]

Well, you probably will have to use code on each control, don't think there's any "global control movement" events;-)

To work with the "previous" control, to shorten the code, you could perhaps store the name of previous control in a form global, then call a form sub with the name of currentcontrol.

[tt]Private Sub SomeCtr_GotFocus()
Call ctrlBorderChange("SomeCtr")
End Sub

Private Sub ctrlBorderChange(ctrlName as String)
Me(ctrlName).BorderColor = vbBlue
if len(globalvar)>0 then
me(globalvar).BorderColor = 52479
end if
globalvar = ctrlName
End Sub[/tt]

Better?

Roy-Vidar

 

[kjv1611]

So, if I understand your code correctly, when the length of the current control (stored in globalvar) is less than 0, that means it simply does not have the focus? And in that case, I could have just one sub procedure per control as apposed to 2 (GotFocus and LostFocus)? If so, that would be very helpful, as it would pretty much cut the coding in half, and I guess I would just store the public code in a module that is external to the form itself. Does that sound like what you're talking about?

 

[RoyVidar]

Each control needs a call to the changing sub in their gotfocus event, passing the name of the control (the current control, the name of the control invoking the call to the property changing control, the control still having focus), that's one line in one event per control.

When the length of the public variable (declared with the public keyword at the top of the module) holding the name of the previous control is greater then 0, meaning that the current control is not the first control one has entered after opening the form, the previous controls bordercolor property gets assigned 52479. If the length is 0, then there was no previous control, meaning we are currently at the first control after opening the form, there's no need to try to apply a formatting to another control.

I was using the on got focus event, passing the name of the current control, assigning it's bordercolor property to vbBlue, then using the above described method to determine whether there was a previous control, if so, apply normal formatting on that, and store the name of the current control in the global, so that it might serve as a previous control next time the routine invokes.

Whole purpose is to avoid programming several lines and events per control, having a sligtly more dynamic approach, where one of the benefits is that you've only have to alter the colors one place in stead of per each control.

For small snippets like this, I keep it within the forms module. Else you'd have to alter the references (fully qualifying the form) pehaps pass the form?

I'd usually expect routines to be tried out, and a re-post with errormsg... etc if errors...

Roy-Vidar

 

[kjv1611]

Thanks. I will give that a try. Will follow-up with results.

 

[kjv1611]

Okay, tried. I am getting this error:
Run-time error '94':
Invalid use of Null

Here is the code I have used (for now, testing only on one control):

[blue]
Option Compare Database
Public globalvar As String
Private Sub ctrlBorderChange(ctrlName As String)
Me(ctrlName).BorderColor = vbBlue
If Len(globalvar) > 0 Then
Me(globalvar).BorderColor = 52479
End If
globalvar = ctrlName
End Sub

Private Sub cmbCUBS_GotFocus()
Call ctrlBorderChange(cmbCUBS)
End Sub
[/blue]

The error message comes when I select the cmbCUBS control. Any idea as to what my error is?

 

[RoyVidar]

The name of the control is a string, enclose it in "".

[tt]Private Sub cmbCUBS_GotFocus()
Call ctrlBorderChange("cmbCUBS")
End Sub[/tt]

- strongly advice also to include the line

[tt]Option Explicit[/tt]

as the second line of every module (to make it mandatory for all new modules, (in VBE) select Tools | Options - check the "Require Variable Declaration" check box)

Roy-Vidar

 

[kjv1611]

Thanks. Okay, that fixed the error, and it changes the background color to vbBlue when selected, but when go to next control, it stays vbBlue, does not change back to 52479.

 

[nice95gle]

Is'nt there another piece that have to be included. Don't he have to test the Previous control to make sure it is not a command botton.
If I'm following correctly he is just changing the current control and then he will have to set the previous control to some color, but if the previous control is a button would he get an Error?

 

[RoyVidar]

Does the next control you enter call this sub?

- hmm nice95gle, perhaps add another parameter in the sub to test for, so that when current control is a command button, or any other control used, the puplic gets assigned vbNullString?

Roy-Vidar

 

[RoyVidar]

[tt]Private Sub SomeCtr_GotFocus()
Call ctrlBorderChange("SomeCtr", True)
End Sub

Private Sub ctrlBorderChange(ctrlName as String, bChange as Boolean)
' ctrlName - string - name of control
' bChange - boolean - True if the control properties should
be changed, False - no change
if len(globalvar)>0 then
me(globalvar).BorderColor = 52479
end if
if bChange then
Me(ctrlName).BorderColor = vbBlue
globalvar = ctrlName
else
globalvar = vbNullString
end if
End Sub[/tt]

Roy-Vidar

 

[kjv1611]

That was the ticket, Mr. Vidar. The deal is that if I move to a control that does not call the ctrlBorderChange procedure, then the code does not operate correctly. However, when I have all the controls which I move amongst, it works perfectly. Do you think what [darkblue]nice95gle[/darkblue] said is true? If the user clicks on a button or something, then goes back to a control that has this option, it will produce an error? Is it not possible to just add this code to each control on the page, so that whatever is selected will have the blue border?

 

[kjv1611]

Looks like posted at the same time on that one. So, if I were to edit the code to include the boolean, would I still need to include that code with each control on the form (say any buttons, etc)?

 

[RoyVidar]

Yup (but then one line per control vs two events, more lines...;-)

Roy-Vidar

 

[kjv1611]

Okay, I'll be busy copying and pasting for a while, but it will still be less than 2 events per control. Thanks a bunch!

Stephen

 

[kjv1611]

One last note: the deal about including the option explicit did help to fix an error in an unrelated bit of code in the particular module where I had used an incorrect format.

 

[kjv1611]

Mr. Vidar, you seem to be a very knowledgable person. Would you mind taking a look at my thread "Scroll does not work on wheelmouse when in VBA window", and see if you have any clue as to what we're talking about. Or, if you would know of another person who would have some knowledge in this particular area?