unbound textbox to display Status Bar Text when control has focus

[phillikl]

Good day,

I have racked my head against the wall so much over this I believe I have a flat spot....

Customer desires a form with a larger font for the Status Bar Text and located elsewhere on the screen - make shift instructions when a user clicks on a field.

While the simple answer is to utilize on.gotFocus and on.lostFocus the form has around 100 controls and not only a pain to code, but clutter is horrible.

I built a public function to grab the Screen.ActiveControl, however I would still have to code 200+ lines and makes the vba look horrid with that many lines of the same call function.


Any ideas or push in the right direction would be fantastic!

Thanks in advance,
Kendall

[NeoPa]

Hi Kendal.

I'm not seeing why your public function is complicated or long. Once you know the control simply grab its .StatusBarText property value and show it where you need to. If you're clever and use code to set up the function call for .OnGotFocus & .OnLostFocus to include the name of the control too then it can be even shorter. You do know you can call a public function directly from the properties don't you? And that public function can also be a method of the form too?

If that's no help how about sharing a few more details so we have a better understanding of what you're talking about.

[phillikl]

Thanks Neo!

Forgot all about driving public functions from the properties. It's the easiest things that always get me - and being the only programmer, makes it hard to bounce ideas.

For anyone else requiring a solution similar:

create unbound text box of form (txtStatus)

set the properties to:

On Got Focus: =setStatusBarText()
On Lost Focus: =clearStatusBarText()

Public Functions:

'Get data from the Status Bar Text field from the control and set it to unbound text box control
Public Function setStatusBarText()
 
Dim ctlCurrentControl As Control
Dim strControlName As String
 
On Error GoTo setStatusBarText_Err
'If control is not a drop down, just stop the code instead of throwing an error
'This is sloppy coding and control type should be pulled and executed with if/then statement
 
 
Set ctlCurrentControl = Screen.ActiveControl
strControlName = ctlCurrentControl.Name
 
Forms("frm_api").txtStatus = Forms("frm_api").Controls(strControlName).StatusBarText
Forms("frm_api").Controls(strControlName).Dropdown
 
setStatusBarText_Exit:
    Exit Function
setStatusBarText_Err:
    Resume setStatusBarText_Exit
 
End Function
 
Public Function clearStatusBarText() 'set the Status Bar Text control to empty
 
On Error GoTo clearStatusBarText_Err
 
    Forms("frm_api").txtStatus = Null
 
clearStatusBarText_Exit:
    Exit Function
 
clearStatusBarText_Err:
    Resume clearStatusBarText_Exit
 
End Function
 

Thanks again,
Kendall

[NeoPa]

Just so my earlier points were fully understood :

1. The Public Function can be a Private Function and needn't be in a Standard Module. It can be referenced in the Module associated with the Form it's used by.
2. I believe it's also possible to pass objects to such functions from the property sheet. IE. Instead of just the name of the item you can pass the Control itself and save the Function having to work it out for itself.

The code could be as simple as :

Private Function setStatusBarText(ctlVar As Control _
                            , Optional ByVal blnSet As Boolean = True) As Boolean
    Me.txtStatus = IIf(blnSet, ctlVar.StatusBarText, Null)
End Function

So, you could set it up like :
On Got Focus: =setStatusBarText([ControlName])
On Lost Focus: =setStatusBarText([ControlName],False)

Some code run from the Immediate Pane (Ctrl-G) for Form=X & Controls=A, B & C might be :

S="A,B,C":For Each C In Split(S,","):Forms("X").Controls(C).OnGotFocus=Replace("=setStatusBarText([%C])","%C",CStr(C)):Forms("X").Controls(C).OnLostFocus=Replace("=setStatusBarText([%C],False)","%C",CStr(C)):Next C

[phillikl]

Thanks Neo! Will definitely add this to my toolbox of goodies!

Kendall

[NeoPa]

Always happy to help Kendall.

PS. I'll just delete that duplicate post for you ;-)