F3 function key in maintaining Controls (Excel 2000)

[ChrisGreaves]

I love the F3 function key; so handy for calling up a list of named ranges when defining formulae, inserting (fx) functions, and so on.
This morning I tried to use it to maintain (then to create) drop-down listbox controls from the Controls toolbar.
No luck.
Am I missing something (again!)?
If you don't know about the F3 key, in a workbook with named ranges, choose an empty cell then
tap the equals key to start a formula, then tap the F3 function key or
choose the function picker for SUM and when the help box pops up, tap the F3 function key.

[HansV]

I'm sorry, I don't understand what you mean by "to maintain (...) drop-down listbox controls", or what F3 could have to do with it. Could you explain in more detail what you're trying to do?

[ChrisGreaves]

Could you explain in more detail ...

Glad to. (And I know that you know all this ...)
The attached workbook has a block of four numeric data cells to which I have assigned a range name "Block_Of_Data".

To see this tap the F5 function key (GoTo) and choose "Block_Of_Data" and we go to and select the four numeric data cells.

Now go to an empty cell, use the function-picker (fx), choose Sum, and when the text box presents itself with the text cursor in the indicated position, tap the F3 function key (Range names), choose the range name "Block_Of_Data" and enjoy.

1.JPG

Now go to an empty cell, Choose the Controls Toolbar, place a list-box control on the worksheet, choose Properties and in the space indicated, try using the F3 key to pull up the range names. They are not available via the F3 key.
The range name "Block_Of_Data" can be pasted in and works in that manner, so range names are acceptable as ListFillRange entries.

2.JPG

I am disappointed that the F3 key, so useful, and especially in encouraging users to make use of named ranges, seems to fail in this instance.

[HansV]

The Properties window for the list box is not managed by Excel, but by the Visual Basic Editor. This will become apparent if you do the following:
- Activate the Visual Basic Editor.
- Close all module windows in the Visual Basic Editor.
- Switch to Excel.
- Place a list box from the Control Toolbox on the worksheet.
- Switch to the Visual Basic Editor.
- You'll see the list box featured in the Properties pane.

x66.png

The function key F3 has an entirely different role in the Visual Basic Editor - it's used to search for text within code.

[ChrisGreaves]

The Properties window for the list box is not managed by Excel, but by the Visual Basic Editor.

Thanks, Hans. This makes sense and leaves me appointed (!) with you but still dis-appointed with Excel.
You and I know VBA, VBE, project manager et al.
But I think of the end-user with no real need (as yet) to delve into VBA, who learns that the F3 calls up range names in two obviously useful areas, but then learns that this is not the case for Controls.

Rationally I can't think of a good reason NOT to allow the F3 to behave (at the end-user level) in a properties box as it does in other areas accessible to the end-user (End-user: as distinct from the Developer)

[HansV]

I suspect that Microsoft's view is that the controls from the Control Toolbox should be set up by a developer who knows VBA, not by an end user.

[Rudi]

I love the F3 function key; so handy for calling up a list of named ranges when defining formulae, inserting (fx) functions, and so on.
If you don't know about the F3 key, in a workbook with named ranges, choose an empty cell then
tap the equals key to start a formula, then tap the F3 function key or
choose the function picker for SUM and when the help box pops up, tap the F3 function key.

Hey Chris,

It seems you are one who likes those SHORTCUT KEYS!!!

Here is one you may not know :)

If you select a blank cell in Excel and type "=SUM" and then press CTRL+A, this will activate the function palette. Of course then you can press your F3 key to immediately access the Range Names dialog and insert those names into the formula.

PS: You can type any function name after an = symbol and press CTRL+A. It activates the function palette for any existing function in Excel. No need for that Fx button on the formula bar anymore!

[Jan Karel Pieterse]

Shift+F3 does the same.

[Rudi]

Were you replying to me Jan Karel?

If so, Shift+F3 opens the Insert Function dialog, which is a little longer than CTRL+A. (Unless I am missing the point?)

[HansV]

Shift+F3 does double duty: depending on the situation it displays either the Insert Function dialog or the Function Arguments dialog. It works whether you're editing a cell or not.

Ctrl+A selects the entire sheet if you're not editing a cell, and if you're editing a cell but the insertion point is not after the name of a function, it doesn't do anything.

So Shift+F3 is more specific to formulas and more versatile at the same time.

[Rudi]

OKieDokie...Here's the code...

=SUM & CTRL+A = function palette
=SUM( & CTRL+A = function palette
SHIFT+F3 = insert function
=SUM & SHIFT+F3 = insert function
=SUM( & SHIFT+F3 = function palette
F3 = insert range name into formula argument

[ChrisGreaves]

I suspect that Microsoft's view is that the controls from the Control Toolbox should be set up by a developer who knows VBA, not by an end user.

Right, but they've done another half-baked job here.
The end user gets to set up the controls without any knowledge of VBA, that is, need not be a VBAer.
GUI forms are the province of the VBAer, for sure, but the Control Toolbar is made available (and even recommended) for the end-user.
I suspect that this is another area that got released in an incomplete form, and remains frozen in amber, as it were.

[Jan Karel Pieterse]

If you ask me, they should NOT have unleashed the ActiveX controls to the user. In most cases the forms controls are the better alternative anyway.

[Jan Karel Pieterse]

Were you replying to me Jan Karel?

Yes, I was!

[ChrisGreaves]

... the forms controls are the better alternative anyway.

Jan Karel, now I'm confused.
When you say 'Forms Controls are better ...' are you referring to the GUI forms we write as VBA modules (frmInput etc.)?
My preference is to write such a form because it offers control over the relationships between user inputs (If your phone area code is (416) your postal code better begin with "M" ...)

[HansV]

Chris,

Jan Karel is referring to the Forms Toolbar within Excel itself, not to userforms in the Visual Basic Editor.

[Jan Karel Pieterse]

Hi Chris,

As Hans said, I was referring to the controls you can put on your worksheet, which are devided in two camps: the form controls and the control toolbox controls, AKA ActiveX controls. The Form controls have my preference.

[ChrisGreaves]

... divided in two camps

Thanks Hans and Jan Karel. I've demonstrated the Toolbars(Controls) in class, but never the Toolbars(Forms).
Now that I've seen Toolbars(Forms), I still prefer a well-constructed GUI!

I see users who have "mastered" these Avtivex controls, and all too often the controls are poorly constructed; in most cases users would have been better served by choosing Data, Validation and opting for a List

[Jan Karel Pieterse]

Now that I've seen Toolbars(Forms), I still prefer a well-constructed GUI!

I see users who have "mastered" these Avtivex controls, and all too often the controls are poorly constructed; in most cases users would have been better served by choosing Data, Validation and opting for a List

What do you mean "Well-constructed"? are you saying you like the ActiveX ones better?
Their looks may be better, but they are often the cause of problems in Excel workbooks. This is why I avoid them like the plague. Also, the Forms controls do a better job of working "together" with Excel.

[ChrisGreaves]

What do you mean "Well-constructed"? are you saying you like the ActiveX ones better?

Jan Karel, I prefer "userforms in the Visual Basic Editor" to on-sheet controls.
I believe that for anything other than a simple drop-down list, a GUI form with some intelligent data validation and corroboration coded into it saves a lot of anguish.
But there again, I've not spent a lot of time with the Forms Controls toolbar ....