Download and extract the workbook linked to in the Files Needed section above.

Returning the Contents of a Cell

You can use an Excel input box to ask the user to select a cell and capture its contents in a variable.  Insert a new module and create a subroutine called ReturnCellContents.  Add code to return the result of an Excel input box to a String variable as shown below:

Here we're using a String variable and have not specified the Type parameter of the input box.

When you execute the code show above, you can click on a cell while the input box is displayed:

Click on a cell to select it and read its address into the input box.  Click OK to return the cell's contents to the variable.

After clicking OK on the input box, a message box will appear which shows the value of the selected cell:

The message box shows the value captured in the variable.

Selecting Multiple Cells

You can select multiple cells when the input box is displayed:

Here we've selected a block of six cells.

The results of this selection may not be quite what you expect:

The variable only captures the value of the top left cell in the selection.

You can also select non-adjacent cells by holding Ctrl while clicking on them:

Hold Ctrl and click to select non-adjacent cells.

You won't be able to proceed with such a selection, however:

This is the message you'll see if you select non-adjacent cells.

Selecting Numbers

You can set the Type parameter of the Excel input box to 1, which means that it will only accept numeric values.  Create a new subroutine called SelectANumber.  Add code to return the result of an Excel input box to a Double variable as shown below:

Set the Type parameter to 1 to make sure the input box returns a number.

After executing the code shown above, you can select a cell containing a number:

Select any cell containing a number.

After clicking OK on the input box, a message box appears and shows the value that was selected:

This basic message box shows that the value of the cell you selected was captured in the variable.

You can try to select a cell which doesn't contain a number:

This cell clearly doesn't contain a number.

You'll see the standard warning message whenever you enter a non-numeric value into an input box with a Type of 1:

Maybe try reading the instructions next time?

To practise using an input box to select cells:

1. Using the same module, create a new subroutine called MoviePicker.
2. Declare a String variable and populate it using an Excel input box which asks the user to pick the best film:

3. Repeat this to ask the user to pick the worst film:

4. Step through the procedure with the F8 key and use the Locals window to check that your choices are being captured:

There's only really one choice for worst film.

5. Add code to place the values you have captured into the appropriate cells:

6. Run the procedure and check that the values you selected appear in the correct cells:

Your selected films should appear as shown here.

7. Add two If statements which will exit the procedure if the user cancels either input box:

8. Save and close the workbook.