Bacon Bits:

A quick look in the email bag reveals a note from Darius who asks this question:

"In Access, you can call up zoom by hitting Shift+F2 on your keyboard. Is there a way to create zoom functionality in Excel?"

What Darius is referring to is the ZoomBox function in Access. If you are in any text-based input box, you can call up the ZoomBox by hitting Shift+F2 on your keyboard. This will bring up a dialog box, allowing you to see a broader workspace for entering data.

 

There is no such functionality in Excel, but the thought of creating a Zoom box in Excel intrigues me. So Darius, I've put outlined the steps to creating your own Zoom box.

For all of you who are too lazy to build this yourselves, I've loaded a sample file you can download here.

 

 

Step 1: Create a user form

Press Alt+F11 to get to the Visual Basic Editor. There, you can click on Insert->Userform.

 

 

Step 2: Add a TextBox

Use the Toolbox dialog to add a TextBox . If you don't see the Toolbox dialog, click View->Toolbox on the VBE menu.

 

 

Step 3: Configure the TextBox Properties

Right-click on the TextBox and select Properties. This activates the Properties pane. Here, you want to set the MultiLine property to True and the WordWrap property to True. This will ensure that he TextBox wraps your text as you type data into it.

 

 

Step 4: Add a Button

Use the Toolbox dialog to add a CommandButton.

 

 

Step 5: Name your UserForm

Right-click anywhere on the gray background of the userform and select Properties.

This activates the Properties pane. Here, you want to set the Name property to ZoomForm and the Caption property to Zoom.

The Name property gives us a distinct name for the form that we can use in code. The Caption property changes the heading on the form to a friendlier title.

 

 

Step 6: Add Code to your Userform.

Click anywhere on the gray background of the userform and select View->Code in the VBE menu.

Be sure to change the event procedure to Activate. This tells the userform to run your code upon open.

Here, you will enter:

Me.TextBox1.ControlSource = ActiveCell.Parent.Name & "!" & ActiveCell.Address

This tells the userform to tie the TextBox to the active cell. Meaning, the TextBox will read and write against the cell you are currently on.

 

 

Step 7: Add Code to your Button.

Double-click on the button you added to get to the code for the button. Here you will enter:

Unload ZoomForm

This will close the userform once you click OK

 

Step 8: Add a Macro to Call the ZoomBox from the Spreadsheet.

Now you'll have to give the users a way to call up the ZoomBox.

To do this, let's add a macro. Click Insert->Module while still in the VBE.

 

Once you have the module, you can add a Sub Procedure called ShowZoom. This procedure will call up you Zoom form.

 

 

Step 9: Assign a Hot Key to the Macro.

Go back to your spreadsheet and use the Macro dialog box to assign a hot hey.

In this case, I've set the Macro hot key to Ctrl+m. Hitting Ctrl+m on the keyboard will now fire my ShowZoom macro.

 

 

Step 10: Test It!

At this point, we're done! To test, I've hit Ctrl+m on my keyboard. This activates the Zoom box.

Upon opening, the Zoom box ties itself to the currently selected cell.

I enter some data into it and press OK.

Immediately, the data transfers to the cell. Also, the cell is automatically formatted to wrap text and expand to show the text I entered.

So now I have a user-friendly interface that allows me to more comfortably enter long text into a cell.

 

If you want this zoom box to be available at all times (with all workbooks), simply build this solution into your Personal macro workbook.

• Previous Entry: Thanksgiving in the DataPig Household
• Next Entry: Fireside Chat with DataPig