0

So what I'm trying to achieve is to display help text in a TextBox everytime a new field is selected (or gains focus). I am working with Excel 2013 and my Form has multiple tabs (multipage form).

So far here is what I know is possible: Everytime a field is selected, I can use one of the events (i.e. Click) to update the help text in help text box (just for info, help text is held on a worksheet and each field has a tag. I use this tag to pull the help text from the worksheet). What I cant figure out (or find on the web) is how to do this dynamically: so when a new field gets focus, maybe there is a form event I can use to get the ActiveControl and pass it to my sub? (which pulls the help text).

I've tried using Myform.Click (and Multipage.Click) event but that doesn't work when I click on a new field or tab to a new field

Please let me know if I can provide more information

Help much appreciated

Zac
  • 1,924
  • 1
  • 8
  • 21
  • Try using the `Enter` event. – Brian M Stafford Jul 31 '18 at 12:32
  • @BrianMStafford: just tried that but unfortunately it behaves in a similar manner as the `Click` event.. I get something if I actual click anywhere on the tab but not when I change the fields on a tab – Zac Jul 31 '18 at 12:35

1 Answers1

0

Unless I misunderstand what you need, why not something like this?

Private Sub TextBox1_Enter()
   UpdateHelp TextBox1
End Sub

Private Sub TextBox2_Enter()
   UpdateHelp TextBox2
End Sub

Private Sub TextBox3_Enter()
   UpdateHelp TextBox3
End Sub

Private Sub TextBox5_Enter()
   UpdateHelp TextBox5  'this is on a multipage control
End Sub

Private Sub TextBox6_Enter()
   UpdateHelp TextBox6  'this is on a multipage control
End Sub

Private Sub UpdateHelp(ByRef c As Control)
   TextBox4.Text = c.Name
End Sub

You could pass ActiveControl, but for controls on a Multipage control the ActiveControl is the parent control and not the TextBox.

Brian M Stafford
  • 8,483
  • 2
  • 16
  • 25
  • Thanks for the reply mate but this is what I'm trying to avoid. This approach means that everytime a field is added to the form, I have to add the call to `Enter` event for that field. If I can dynamically get the active field everytime the field focus is changed, I wont have to do that. I will just be able to catch the change when that triggers and send the `ActiveObject` to `UpdateHelp` UDF. Any thoughts? – Zac Jul 31 '18 at 13:02
  • I'm not sure there is a straight-forward way of doing this. I'll try a couple of things and if I find something I will update this answer. – Brian M Stafford Jul 31 '18 at 13:43
  • That would be brilliant mate. I am working on it as well. If I find a solution, i'll post it here as well – Zac Jul 31 '18 at 14:38