[b] combo box in Excel - Not the simple one [/b]

[Benoni2]

I am trying to program a combo box using the one on the control toolbox. I don't want to use excel's simple built in combo box because it leaves the dropdown arrow outside the cell. I can't figure out the code to use to program it in vba however. I get bits and pieces of what I need to do, but I can't get enough of the whole picture to move forward. Any help?

 

[SkipVought]

hi,

Please explain your business case.

There are 3 "dropdowns" you can use: Data/Validation, Forms & Control Toolbox.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Benoni2]

I am creating a form type looking document in excel. I am limiting choices to my defined list. I don't care for the Data/validation for cases because the dropdown arrow shows outside the cell in question. I want one that looks like it sits in the cell.

I finally figured out how to reference the column I want in the properties box. but a couple of questions:

1. How do you locate it in your list of tab stops? Is there even a list of tab stops?

2. How can I reference the selected value so that it displays in another cell? For example. once the quarter is selected, I want the value to show up on the top of the next sheet.

 

[SkipVought]

There are no tab stops. You control the position of the control using the Top and Left properties.

So if you want the box within the active cell...

with Combobox1
   .top = activecell.top
   .left = activecell.left
   .width = activecell.width
   .height = activecell.height
end with

You can do this with ONE COMBOBOX PER SHEET.

If a cell is selected in the column for the combobox, the combobox Visible property is set to TRUE in the Worksheet_SelectionChange event. Any other cell selection makes the combobox Vispble property FALSE.

In the Click event for the combobox, you assign the selected value and then set the Visible property to FALSE.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}