Add Help buttons to each worksheet in an Excel workbook. Click a help button to open a UserForm with MultiPage tabs. Each sheet's button open the UserForm with its specific page tab active, to show relevant help.
This video shows how to create a simple UserForm with a MultiPage control, where you will add help messages, on separate tabs.
The workbook's sheets have Help buttons, which open the UserForm. The button names control which tab is active in the UserForm, when it opens, at the applicable page in the MultiPage control.
Click here to go to the full Video Transcript, which is below the video.
This screen shot shows the UserForm Help page that open when you click a Help button on a worksheet. In this example, the PivotSales sheet is active, and its Help button opens the Pivot page in the UserForm's MultiPage control.
To get the completed workbook with the Help pages UserForm, go to the Download Section below.
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 UserForm, 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, Control 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 zero, one, and two. 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.
Now on 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 that 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.
To see how the completed UserForm with Help pages works, get the UserForm Help Pages sample file. The zipped file is in xlsm format, and contains macros. Enable macros to test the code.
Last updated: November 22, 2023 9:37 PM