VBA Status Bar

Question

Hi,

I am working on a Word VBA macro app for 80 or so users. The office has high staff turnover, so training suffers, and so one of the self imposed requirements for this project is comprehensive, friendly documentation. However, to supplement this, and to save newbies having to open up a 100 page document when they want to try something new, I want a status bar on every userform (there are five) that provides contextual help. I find tooltips annoying.

I don't have a lot of experience, so I was wanting to

Essentially, I have a file containing every status string. (This is currently a text file, but I was wondering if I should use a spreadsheet or csv for ease of editing by other staff in future.) Every control has a MouseMove event which refers to a function: getStatus(cID) that opens the file, grabs the line and displays it in the status label. It also grabs a few parameters from the same line in the file, such as whether the label is clickable (to link to a page in the help file), and what colour the label should be.

So a few questions really:

Will the application be slow if a userform is constantly referring to a file? It feels fine to me, but I've been in it far too long, and I'm the only user accessing that file. There will be 80 constantly accessing it.

Is MouseMove over a control the best way? Should I instead use co-ordinates?

Most importantly (in terms of me having to do as little work as possible) is there some way to do this so that I do not have to have a MouseMove event on every single control? I have a good few hundred or so controls, each with their own identifier (well, not yet, but they will if this is the only way to do it). Maybe when the form loads I could load ALL the possible status lines so they're ready for whenever the control is moused over. But then, maybe the loading time is negligible?

Appreciate any ideas or thoughts - especially if VBA already has a whole range of functions to do this already and I'm just trying to reinvent the wheel. I can't use the application status bar, because the user rarely sees the application itself.

Thanks!

EDIT:

It is for both data entry, clicking around and a bit of document generation.

It is a controlled environment so macro security issues aren't a big concern for me - and if something goes wrong it's someone else's fault or problem :)

Answer 1

Word blocks VBA by default, users will have to jump through hoops to enable it. The warnings look kind of nasty so to be honest, people won't enable VBA macros. Of course it depends on what version of Office they are using.

You need to take this into account before you spend any more time on this setup to even be sure it's a good idea. Also don't forget enabling Word macros is a possible security hole as a malicious macro they encounter in the future might do something naughty.

Answer 2

Is this data entry app or do they just click stuff? Because often the field with focus is different to the item the mouse is hovering over, this can cause a lot of confusion.

Constantly reading from a file is a huge waste of time and resources - it is much better to load them only once into an array or collection when the form is loaded.

On MouseMouse event is better than coordinates because you can move things around without worrying. It's a lot of code but you should be able to generate most of that if you have a list of control names because the code should be identical.

ie

Sub Control_MouseMove()
  DisplayStatus(Control)
End sub

Answer 3

I would consider the StatusText property and ControlTipText property of controls for this kind of help.

StatusText This example sets the status bar help text for the form field named "Age."

With ActiveDocument.FormFields("Age")
    .OwnStatus = True
    .StatusText = "Type your current age."
End With

ControlTipText This can be assigned from the property sheet for the control.

Private Sub UserForm_Initialize()
    MultiPage1.Page1.ControlTipText = "Here in page 1"
    MultiPage1.Page2.ControlTipText = "Now in page 2"

    CommandButton1.ControlTipText = "And now here's"
    CommandButton2.ControlTipText = "a tip from"
    CommandButton3.ControlTipText = "your controls!"
End Sub