Pull down menu in grid

[Anton Sommer]

Hello folks,

is it possible to place any object (i.e. Pull down menu or checkbox) in a
cell of a table. And then of course beeing able in vba to read out the
values like any other object in a userform.
Is there an event to use in vba to find out if a user has just left a cell
like the lost focus event of a textbox?

Thank you folks,


Anton

 

[JakeyC]

I don't think you can place an object as if it were in a cell (ie the
'contents' of that cell) but you can certainly place it over a cell and
then refer to it in vba using expressions like:

ComboBox1.Value
or
TextBox3.Text

etc.

Hope this helps.

 

[Anton Sommer]

Hallo JakeyC

thank you for answering.

'contents' of that cell) but you can certainly place it over a cell and
then refer to it in vba using expressions like:

how could I place it over a cell? In a Form? or how would it work?


Thank you

Anton

 

[STEVE BELL]

Have you tried Data Validation?
You can use this to put a list into a cell for selecting different options.

 

[Anton Sommer]

Hello Steve,

thank you for answering.

Have you tried Data Validation?

No.

Can you please be a bit more specific how it works

Thanks

Anton

 

[STEVE BELL]

Anton,

While in Excel (not VBE) go to the Data menu and select Data Validation.
A window pops up to set up the validation.
On the Settings tab
select List for the Allow box
go to the source box and than select the range on the spreadsheet where
the list resides.
Note that if you use cell references you can only use a list on the
same sheet.
If you have the list on any other sheet - you must name the list and
use the name
You can also type in the list say like 1,2,3,4
check out the other tabs and set them to what suits you
than click OK
When you enter the cell you will see a drop-down arrow and it works like a
listbox or combobox.
And when you make a selection from the list it will activate a worksheet
change event (if you have one).

Play with this in a new workbook to get the feel for it.

Than build a new Data Validation with the recorder on and you will get code
you can modify...

Let me know if this helps...

 

[JakeyC]

To use a drop-down list, make sure the Control ToolBox is visible (if
not, go to View-> Toolbars-> Control Toolbox) then choose the
'ComboBox' button.

Click the position that you want it to appear, then right-click and
select Properties. In this window, enter the range of cells that
contain the values you want to appear in the list in the ListFillRange
box.

Now, exit the Design Mode (by clicking the button with the Set Square,
ruler and pen symbols on it) and the list can be used.

To enter code in VBA that applies to it, go back to Design Mode then
right-click and View Code.