By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

unbound textbox to display Status Bar Text when control has focus

P: 16
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
Oct 30 '17 #1

✓ answered by 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.

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,417
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.
Oct 30 '17 #2

P: 16
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:

Expand|Select|Wrap|Line Numbers
  1. 'Get data from the Status Bar Text field from the control and set it to unbound text box control
  2. Public Function setStatusBarText()
  3.  
  4. Dim ctlCurrentControl As Control
  5. Dim strControlName As String
  6.  
  7. On Error GoTo setStatusBarText_Err
  8. 'If control is not a drop down, just stop the code instead of throwing an error
  9. 'This is sloppy coding and control type should be pulled and executed with if/then statement
  10.  
  11.  
  12. Set ctlCurrentControl = Screen.ActiveControl
  13. strControlName = ctlCurrentControl.Name
  14.  
  15. Forms("frm_api").txtStatus = Forms("frm_api").Controls(strControlName).StatusBarText
  16. Forms("frm_api").Controls(strControlName).Dropdown
  17.  
  18. setStatusBarText_Exit:
  19.     Exit Function
  20. setStatusBarText_Err:
  21.     Resume setStatusBarText_Exit
  22.  
  23. End Function
  24.  
  25. Public Function clearStatusBarText() 'set the Status Bar Text control to empty
  26.  
  27. On Error GoTo clearStatusBarText_Err
  28.  
  29.     Forms("frm_api").txtStatus = Null
  30.  
  31. clearStatusBarText_Exit:
  32.     Exit Function
  33.  
  34. clearStatusBarText_Err:
  35.     Resume clearStatusBarText_Exit
  36.  
  37. End Function
  38.  
Thanks again,
Kendall
Oct 30 '17 #3

NeoPa
Expert Mod 15k+
P: 31,417
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Function setStatusBarText(ctlVar As Control _
  2.                             , Optional ByVal blnSet As Boolean = True) As Boolean
  3.     Me.txtStatus = IIf(blnSet, ctlVar.StatusBarText, Null)
  4. 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 :
Expand|Select|Wrap|Line Numbers
  1. 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
Oct 31 '17 #4

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

Kendall
Nov 2 '17 #5

NeoPa
Expert Mod 15k+
P: 31,417
Always happy to help Kendall.

PS. I'll just delete that duplicate post for you ;-)
Nov 2 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.