If you're creating an Excel file for other people to use,
you might want to leave some instructions for them so they can get started or to
answer questions as they work in the file. In this workbook,
we have three sheets that are visible and each one has a little text box with
notes to the user. Instead of putting notes on the worksheet,
you can put those notes in a UserForm and then add a Help button at the top of
each worksheet so they can open that Help file if they'd like some assistance
and ignore it if they don't need any help.
So the first step is to go to the Developer tab, click Visual Basic.
That opens the Visual Basic Editor.
And in here we can see any workbooks that are open.
And here's the one where I want to add the UserForm for Help.
Make sure that file is selected and click the Insert menu and UserForm. That just
gives us a blank User Form, so the name is UserForm1.
We'll call that Help Form.
And as the caption, we'll call it Help.
So we've changed the name and the caption. So now, Help shows in that title bar.
We'd like this UserForm to open showing the specific instructions for whatever
sheet the user has clicked the Help button on.
Just click the title bar and now I can see the toolbox here and this control is
the multi page,
so I'll click it and then just draw a rectangle that almost fills this
UserForm. I have three sheets in the workbook. We can see Orders,
Pivot sales and Prices. I'd like a Help tab for each of those.
So I've got page one, two I'm going to right click here,
I can put in a new page. So now there are three pages.
When I click this one, it's just called Page1.
I'm going to call that Order,
so I'll call it pgOrders and make the caption Orders.
That will be the Help for that sheet. This one right now it's Page2.
It will be pgPivot and the caption will be Pivot.
And for the third one, it's pgList and the caption will be List.
I'll have an S on that as well. Going back to the workbook,
here's the Orders sheet.
I'm just going to select all that and press Ctrl C to copy.
Then go back to Visual Basic and on this Orders tab, we're going to put in a
label. So click Label.
Just draw another rectangle to fill that space. I'll click within it,
highlight that existing text, Ctrl V to paste.
Then I'm going to do the same thing for the pivot. Just go here,
highlight that and copy it.
Put it in another label and paste and the same for the list.
Go to the prices where the list of prices is,
copy that and in here,
add a final label and paste.
So now each tab is set up with specific Help
for that worksheet. When we click on Orders,
it used to say Page1 but if we look in the properties,
we can see its index is zero, so the numbering starts at zero.
So this will be page 0, 1, and 2.
So we want to get to the right page when this Help UserForm opens.
So to do that,
we're going to create three macros that will open the right page. To store the
macros,
I'm going to insert a module. At the top of the module,
I'll create a public variable called iPage and this will be as an integer.
That's just going to store those page numbers and a little sub Help0 and
this will set the iPage equals zero.
Our UserForm, we can see here, is HelpForm, HelpForm show.
When we use that macro, it's going to open the first page.
I'll just Ctrl C to copy that and Ctrl V to paste it twice.
This will be 1 to show page one, 2, to show page two.
Now going back to the UserForm,
we're going to right click on it and click View Code, creates this UserForm
click event, which I don't want to use.
So I'm going to select initialize instead and I can delete this click event.
So within here when this form is initialized,
I want it to Me, which is the UserForm.
Multipage is the name of that multipage control.
Just press the Tab key to select that, then dot value equals, we want it to use that
iPage value, so when it opens,
it's going to go to whatever page our macro told it
the iPage value is. Now back into the workbook,
we'll go to the orders sheet and on the Developer tab, we're going to insert a
button and just at the top of the sheet,
this one will use Help0. Click OK.
Instead of it saying Button 1,
we'll put Help. When I click it
now, it opens the Help at the first page, which is page zero.
I'm going to copy that.
Go back to the next sheet and Ctrl V to paste it, right click and click
Assign Macro and this will be Help1 and click OK. On the Prices.
We'll paste again, right click,
assign macro, and this is Help2, OK,
and then just click away from the button on each sheet so it's not selected.
Now if I click Help here,
we're on the first tab. On the pivot sales, if I click Help,
it goes to the second tab and from Prices,
click Help and it goes to the third.
So it opens the Help at the applicable Help screen,
but you can go back and look at the others.
So if someone wants to look at all the help while they're in here, they can,
but it takes them immediately to the most important help for the page that
they're on. To close it, just click X and you're back in the workbook.