Customized Help for Excel Workbooks

Scenario: You have a workbook that is very complex, packed with formulas, data tables, and worksheets that need to be sorted in a particular order, etc. However, it is a very nice, flexible workbook that would allow people to view data in a variety of ways… if they could only be relied on to use the workbook properly.

One route would be to create a manual to go with the workbook, but these things get lost or left behind, and people have to leaf through them to find the part they want.

Another route is to create unobtrusive help within the workbook that is there when needed, but largely out of the way when not.

This workbook uses a minimum amount of VBA, with a form button to control the display/non-display of formatted text boxes in a worksheet. (Please be aware that the workbook contains macros and thus will need to be saved that way). The example below shows the steps.

Before beginning, you will need the VBA Developer Tab on the Excel ribbon. (In Excel 2016 go to File>Options>Customize Ribbon, then choose ‘Main Tabs’ then select ‘Developer’ (See below). Access the example Excel worksheet here.

2018sept1.png

Once this is done, you should have a new tab in your Excel worksheet environment. Now onto the main task: creating custom help.

Step1 – The Help Box

Using the ‘Insert’ tab, insert a text box onto your worksheet. (You can format it with colored borders at this point, but for now a simple text box will do. Be careful to note the name of the textbox; this will appear in the Cell reference box (at the top left corner of your worksheet) once you click on the textbox. In the screenshot below, my textbox is named ‘TextBox 3.’

2018sept2.png

Step 2 – The Help Button

This is where the Developer tab comes in. Click on the ‘Developer’ tab in the ribbon, then choose Insert within the Developer options (do not use the regular Insert tab). It looks like a  briefcase with a wrench and a screwdriver.

This will let you access ‘Form’ and ‘ActiveX’ controls. We want the first item in the Forms control – a button.

Click on the Button control, then click and drag it to place it on the worksheet. NOTE: At this point Excel may ask you to ‘assign a macro’ – click cancel. The button will stay, but will have no macro attached to it.

Step 3 – The Macro

What we want to is record a macro where we select/deselect the text box. This creates our starter code in VBA – we’ll get there in a minute.

So, click on the worksheet (not the textbox).

Start recording a macro (View>Macro>Record Macro).

Click on the textbox.

Stop recording the macro.

Step 4 – Tweaking the Macro

Now we need to get into the Developer code environment, the easiest way is to click on View>Macro, then ‘Edit’, rather than ‘Run.’ This should present you with a screen that looks something like this:

(I messed up a little: you shouldn’t see the ‘Range’ line – the line we really need is the ‘Activesheet’ one).

2018sept3.png

The main element here is the line starting with ‘Activesheet.’ This is mostly so we can use cut and paste to create the following  code to replace what is currently in Macro 1. The line of code below will  toggle the visibility of the textbox each time the macro is run.

ActiveSheet.Shapes.Range(Array("TextBox 3")).Visible = Not ActiveSheet.Shapes.Range(Array("TextBox 3")).Visible

Remember, you may have a different TextBox number. The trick now is to attach the button to the macro. Close the developer environment (Click on the ‘x’ in the top right – it will disappear but stay in the background).

Step 5 – Assign the Macro to the button

Once the developer environment is closed, you should be back in your worksheet with the button and the Textbox.

  1. Right-click on the button.

  2. Choose ‘assign macro’ from the drop-down context menu that appears.

  3. Select the macro you created (In this case ‘Macro1’).

  4. Click OK.

  5. Right-click on the button again.

  6. This time choose ‘Edit text,’ and give the button a more meaningful name/description.

  7. When done, right-click again and choose ‘Exit Edit Text.’

  8. Click somewhere on the worksheet so the button is deselected.

Step 6 – Reap the benefits

If all has gone well, when you click on the button now, the textbox will disappear. When you click on the button again, the text box will reappear.

 

 Comments

 
To add a comment, Sign In
Total Comments: 1
 
Terry posted on 9/20/2018 2:11 PM
I love this tip. I've seen complex VBA routines but what I really like is something simple that increases the professional look of the work.