How to make drop down visible and activated without going to the left cell

[michelleponan]

Hi,

Is there anyone who knows how to make drop down arrow visible without going on the left cell of it? Please find the attachment for reference.
Thank you.

Regards,
Michelle

 

[Hui]

The whole idea of dropdowns is to maintain access to a list but only when the list is required, as otherwise it will take over the cells below it.

You could add a ListBox or Combobox as found in the Developer Tab

 

[michelleponan]

Hi Hui,

But how will I link it to cell C2 or C4? Kindly show in my attached excel.
Thanks.

Regards,
Michelle

 

[PCosta87]

Hi Hui,

But how will I link it to cell C2 or C4? Kindly show in my attached excel.
Thanks.

Regards,
Michelle

Hi Michele,

Maybe try this way and let us know if it is what you were looking for:

Step 1: create a list of the possible data for the combo box... just place the values in a column and give it a name in the up left corner as shown below:

Step 2: add a combo box (ActiveX control):


Step 3: Go to properties (you should be in structure mode):


Step 4: Find the Linked cell (set the cell where the output will be inserted... C7 in this ex.) and ListFillRange (set the the list of possible values... the one we created earlier "List"):

Step 5: Exit structure mode (note: to edit the properties of the combo box as in step 3, you will need to reactivate this mode):


This way the combo box is always visible and can be accessed without selecting the left cell

Best regards

 

[PCosta87]

See attached

 

[michelleponan]

Hi Michele,

Maybe try this way and let us know if it is what you were looking for:

Step 1: create a list of the possible data for the combo box... just place the values in a column and give it a name in the up left corner as shown below:View attachment 35984

Step 2: add a combo box (ActiveX control):
View attachment 35985

Step 3: Go to properties (you should be in structure mode):
View attachment 35987

Step 4: Find the Linked cell (set the cell where the output will be inserted... C7 in this ex.) and ListFillRange (set the the list of possible values... the one we created earlier "List"):
View attachment 35988
Step 5: Exit structure mode (note: to edit the properties of the combo box as in step 3, you will need to reactivate this mode):
View attachment 35989

This way the combo box is always visible and can be accessed without selecting the left cell

Best regards

Hi Hui,

Please find below the attachment. This is what I actually want to happen.
See cell D4. It worked on the 1st sheet but did not work on 2nd sheet.

Thanks & Regards,
Michelle

 

[PCosta87]

Please see attached... should work if you use named ranges

And also, I'm not Hui

 

[Hui]

Thanx PCosta

 

[michelleponan]

Please see attached... should work if you use named ranges

And also, I'm not Hui

Please see attached... should work if you use named ranges

And also, I'm not Hui

Dear PCosta,

Apologies for the wron name. This is good but is there a way of not creating a new sheet it onto it. In the workbook I tried putting this code. It worked on the Sheet 1 but not in Sheet 2. How can I make it work in Sheet 1 and 2?

Private Sub Workbook_Open()
With Sheet1.ComboBox1
  .AddItem "Double Box Built Up Section"
  .AddItem "Welded Box Section"
  .AddItem "Circular Section"
  .AddItem "Square Section"
  .AddItem "I Section"
End With
End Sub

Thanks & Regards,
Michelle

 

[Hui]

Private Sub Workbook_Open()
With Sheet1.ComboBox1
  .AddItem "Double Box Built Up Section"
  .AddItem "Welded Box Section"
  .AddItem "Circular Section"
  .AddItem "Square Section"
  .AddItem "I Section"
End With

With Sheet2.ComboBox1
  .AddItem "Double Box Built Up Section"
  .AddItem "Welded Box Section"
  .AddItem "Circular Section"
  .AddItem "Square Section"
  .AddItem "I Section"
End With

End Sub

make sure the Combobox on Sheet 2 is named ComboBox1 or change the code to suit

 

[michelleponan]

Dear Hui,

Thanks for the suggestion. What if I have 10sheets, how am I going to write it?

Regards,
Michelle

 

[PCosta87]

Dear Hui,

Thanks for the suggestion. What if I have 10sheets, how am I going to write it?

Regards,
Michelle

Hi again Michelle,

Using the provided code, you can try something like below...
You can have as many sheets as you wish, just make sure the combo boxes are all named "combobox1" and it should work

Private Sub Workbook_Open()

    Dim ws As Worksheet
       
    For Each ws In ActiveWorkbook.Sheets
        With ws.OLEObjects("combobox1").Object
            .AddItem "Double Box Built Up Section"
            .AddItem "Welded Box Section"
            .AddItem "Circular Section"
            .AddItem "Square Section"
            .AddItem "I Section"
        End With
    Next ws
   
End Sub

Regards

 

[Hui]

Michelle

Please explain the whole problem in the initial post
Don't drip feed information