Free Microsoft Excel 2013 Quick Reference

Embedding a List or Combo Box in a Cell

Hi everyone,

I am trying to embed a combo box into an excel cell such that if I hide the row or column, the box is hidden as well. I have tried linking it to a cell. However, when I hid the rows containing these boxes, only some of them are hidden while others simply stay in the same place and appear to be located in a different row. Any suggestions?

Thanks,
Mike


Post your answer or comment

comments powered by Disqus
Hi All,

I'm new here (and to excel) so hope I have posted this in the right palce.

I have a worksheet in which Col. A contains the names of London boroughs and col. B contains the name of each Ward in that borough.

I'd like to create a list (or combo) box showing all the London boroughs, and a second list (or combo) box which will show all the Wards for the Borough selected in the London list (combo) box. I'd also like the option to be able to select all the Wards for the borough selected so that they can be used in a chartgraph.

I have no idea to do this.....and am hoping that someone o=here can at least point me in the right direction.

Thanks in advance

XLV

Am i able to determine the values shown in either a validation list or combo
box being dependant on a value in another cell?

ie: Cell A1 = BOB then validation or combo box would then base it's list
from the named range (or whatever the solution may be) based on Bob.
if i was to change A1 to ROY then it would also change the underlying list?

I have tried everything that i think SHOULD work but that it pretty
limited...

thanks in advance

rich

Is there a way to put a multicolumn LIST BOX OR COMBO Box in the first column(A) of a row and have it autofill the row with values to the right with the ListFillRange values of that selection BUT NOT appear in the cell UNTIL the user arrives in that Cell on that row.

Said another way: you can use the built in Data Validation list box with predifined list values
and put it in cell A1 of a blank spreadsheet. If you Copy Paste that control down column A to say, row 10, the list box arrow will not appear until the user arrives at that cell and row. The problem with a data validation list box is it allows only ONE column of values. I just want to simulate this but use a multicolumn values to auto fill the cell row to the right but not see the list box until the user arrives on that cell row. If an entered value is not in the ListFillRange a user will have the flexibility of adding any value. New values are added to the ListFillRange automatically and sorted automatically.

The first part seems easy enough. The dynamic expanding ListFillRange, the appending of the complete set of values and the automatic sorting seems like it would be more challenging.

I'll settle for the invisible multi column list or combo box if the latter can't be done.

Thanks for all your help.

CR

Thanks for your help.

I am hopeful that someone will know how to accomplish this:

In a single column of a worksheet
1. Create a selection list (or choices) which can populate the cells of
that column
2. Create a list box or combo box to hold the choices
3. Have that list or combo box appear when one of the individual cell of
this column becomes active.

I've had several attempts at this, but haven't accomplished this yet. I've
created these in the past in Word, but I know it is more complex than what
I've done in Word. Any help you can provide is greatly appreciated!!!!!!
Sincerely, mary trueblood :-)

I have a spreadsheet with multiple control buttons used to execute several optional macro functions and sort routines. Instead of cluttering up the spreadsheet with a lot of buttons, is there a way to list all of optional macros in a List or Combo Box? The idea is to have one cell or button location with a pull down or pop-up list of macros, select the one you need, and it runs automatically.

Is there a way to put a multicolumn LIST BOX OR COMBO Box in the first column(A) of a row and have it autofill the row with values to the right with the ListFillRange values of that selection BUT NOT appear in the cell UNTIL the user arrives in that Cell on that row.

Said another way: you can use the built in Data Validation list box with predifined list values
and put it in cell A1 of a blank spreadsheet. If you Copy Paste that control down column A to say, row 10, the list box arrow will not appear until the user arrives at that cell and row. The problem with a data validation list box is it allows only ONE column of values. I just want to simulate this but use a multicolumn values to auto fill the cell row to the right but not see the list box until the user arrives on that cell row. If an entered value is not in the ListFillRange a user will have the flexibility of adding any value. New values are added to the ListFillRange automatically and sorted automatically.

The first part seems easy enough. The dynamic expanding ListFillRange, the appending of the complete set of values and the automatic sorting seems like it would be more challenging.

I'll settle for the invisible multi column list or combo box if the latter can't be done.

Thanks for all your help.

CR

Dear all

I've got a situation where I have a lot of underlying Excel files linked to a single Excel report. I'd now like to make it easy to navigate in the Excel report by using a combo or list box, and I'd like the content in a worksheet to change depending on what the user chooses from the combo/list box.

There's an area of cells in one of the worksheets in the Excel report which I'd like to change depending on the chosen item in a combo/list box, and there's about 30 equal (though with different content) underlying files for this cell-area. What I need is guidance to is the VBA code that can solve this.

I'm not even sure whether I should use a list or combo box, so I've got a pretty long way ahead of me... Any help will be highly appreciated on this.

Hi,

I have a list of numbers example,
In a2
1
2
1
3
3
4
2
2
2
What I want to do is create a combo box in a1 that has a list 1, 2 ,3, 4.
Once the user select e.g. 1 , auto filter will be applied on the range from
a2 to ….. showing only cells that have 1 . how to do that??

I need to set up something close to like this

1) Item Name / Does it play music? / yes or no (list box)

The problem I am having is I have 175 items I need that Yes or No box
in but I can't figure out how to do that.

So if someone could speak to me with instructions that a 7 year old
could understand as to how I can put 175 different combo or list boxes
in one worksheet that have the choices Yes or No..

Thank You for any help in advance.

--
Samantha

Dear All,

For some time I am using the method below to fill a list or combo box. It works but is slow when there are to many entries.
Is there a more elegant way to do this? I heard there was a method using the Excel filter function that makes the whole procedure below not necessary.

Thanks,

Wang Faye.

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item
Set AllCells = Range(theRangeProj)
On Error Resume Next
For Each Cell In AllCells
If Cell = "" Then GoTo theSkip
theCounter = theCounter + 1
NoDupes.Add Cell.Value, CStr(Cell.Value)
theSkip:
Next Cell
On Error GoTo 0
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, Before:=j
NoDupes.Add Swap2, Before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

Hi All,

Wondering if it is possible to populate a combobox or list box with the names of files in a specific folder.

What i am trying to do is have a master file which through a list or combo box selecting a specific file will be able to open that file.

Is this posssible? If so what functions do i need to be looking at?

Thanks!

Hi, is it possible to have Combo Boxes in cells in Excel?
How could I link a particular cell in excel with the selected value from the
combobox.
Kindly help me in this regard.

Joseph J

The specific workbook opens each worksheet as a resized window about 95% of
screen with no minimize, resize, or close box in the top blue title bar of
the worksheet. One worksheet Columns A and B appear hidden or off screen
with a windows split pane. They cannot be restored (not even with the format
column unhide executed from the worksheet global box selected using
format/columns/ unhide. The windows split and unfreeze options are grayed
out.

I really hope someone can help me....

I am creating a quote form. It is for a roofing company and I want to be
able to create a combo box which lets me select an item, and then in an
adjacent column bring up the dollar value for that selected item. ie.

MATERIAL AMOUNT COST
TOTAL
Colorbond 10m² $25
$250

Under 'Material' I want a list or combo box to select items from. When I
select an item, I want the cost of it to show under the 'cost' column.

How can I do this? Please help...

Terry

I created a word doc with drop a drop down list for my staff. One staffer
wants to be able to make multiple selections from the drop down list. Should
I recreate this in excel, and do I use a list or combox box option? i see
how to set the box to multi instead of single but then I can not figure out
how to get the data into the box.

Can someone give me good directions on how or point me to a tutorial on how
to do this?
--
Thanks,,
Kim

Hi,

Please check the attached sample data. I am trying to create the graph with two lists or combo boxes with which I can dynamically get the graph plotted.

I need to create two combo boxes; one for Region and another for the column months.

I am using Excel 2007 but I want users in Excel 2003 to be able view the data.

Regards,
Ravinder

Hi all,

I have a question reqarding using a series of combo boxes in a form, to filter results in a table when a command button is clicked.

For instance -

In my main raw data table, I have a series of colums,

Date|Area|PowerStations|FlowRate

I would like to create a form, where the user can select a date and an area, then click a command button to query a table, returning results for the specific time period and area.

Eventually I will be adding sever further combo boxes to further refine the search, and will be auto populating the gainde results into excel, and automatically plotting some graphs. I think that if anyone can show me how to take this first step, I will be able to figure out the rest my self.

I am currently at the stage where I have the combo boxes created in the form named cbodate and cboarea. My problem is that several of the combo boxes are based on one another via queries, and the bound column in each combo box, necessary for the link, i snot the same column as needed to run the query, so when running the query based on the combo boxes, it just returns blank results.

Many thanks for the help

Hi all,

I am using Excel 2007 and I've created a macro which allows one to create a Pivot Table from a macro. One thing that I'm stuck on is instead of having the user of the macro hand enter the variables, I would like them to be able to use a list (or combo) box in a userform to select the variable/field, which will then populate the macro and create the pivot table when they click a button. The other thing that I can't figure out is how to get the macro to automatically create a new sheet rather than manually changed the worksheet (as you can see below I am at Sheet13. Thanks so much!

Sub MakePivotTable()
    Dim pt As PivotTable
    Dim strField As String
    Dim WSD As Worksheet
    Set WSD = Worksheets("Sheet1")
    Dim PTOutput As Worksheet
    Set PTOutput = Worksheets("Sheet13")
    Dim PTCache As PivotCache
    Dim PRange As Range

    ' Find the last row with data
    Dim finalRow As Long
    finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    
    ' Find the last column with data
    Dim finalCol As Long
    finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
    
    ' Find the range of the data
    Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

    ' Create the pivot table
    Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
    TableName:="SamplePivot")
    
    ' Define the layout of the pivot table
    
    ' Set update to manual to avoid recomputation while laying out
    pt.ManualUpdate = True
    
    ' Set up the row fields
    pt.AddFields RowFields:=Array( _
       "class")

    ' Set up the data fields
    With pt.PivotFields("dorm")
        .Orientation = xlDataField
        .Function = xlCount
        .Calculation = xlPercentOfTotal
        .NumberFormat = "0.00%"
        .Position = 1
    End With
    
  ' Now calc the pivot table
    pt.ManualUpdate = False
    
End Sub


hi,

is there any way by which i can convert a particular cell, say A1 to list
box or combo box without using VBA control tools. i saw this type of
conversion in Autofilter option.

reg

I'm having difficulty doing what I think should be something very simple to do in Excel 2007.

In my attachment, I have a worksheet where I need to pick a rating from a combo box or list (Expert, Leading, Applying, Learning, Under Performing). I will be picking this rating 10 different times on this worksheet and the selections will not be the same in all cases so the definition I need won't always be the same.

I would like to return the definition (definitions are detailed on another worksheet, Data Elements) to the cell to the right.

Can someone get me started here? I've tried IF statements but when I make one selection from the combo box, all the combo boxes select the same thing. I've also tried vlookup but can't figure it out and can't find an explanation in layman's terms.

Any help is appreciated.

hello everyone, i am using a combo box from controls toolbox. in the
properties menu of the combo box i have the list fill range set to a list
created in au822:au846. the linked cell is af16. i put the combo box in ag16.
i want someone to be able to select a name from the combo box drop down list
and have that name enter into af16. that part works but when the name goes
into af16 it wont run all of the if and and statements that are linked to
what is enetered into af16. I fi dont have a drop down box and manuallytype
the name into af16 all of the statements or functions work great. How do i
get the drop down item selected to work when it enters into af16? like i said
i got the name to enter into af16 but it wont run any of the othe functions.
please help me
thanks

I'm trying to incorporate either a list box or a combo box into a spreadsheet.
(My source is How to Programmatically Insert Data from a List Box into the
Active Cell at
http://support.microsoft.com/default...n-us%3BQ213363 )
One of the instructions is to right click on the list box and go to the
Format Control menu. The problem I have at this point is that there is no
"Control" tab in the dialog box. Is that because the instructions are for
Excel 2000 and I'm working in Excel 2003 where the control is no longer
handled the same way?

Trying to find out if anyone knows of a way to embed hyperlinks in either dropdown lists, combo boxes, or even data validation boxes. Trying to find a way to permit users to hyperlink out from these functions based on their selection.
TM

I've posted this a while ago, but I keep getting the same recurring problem, namely that I can't get it to work...and I'm not 100% sure that people really knew what I was on about before anyway

I desperatly need to have a macro that will launch a specific macro when an item is selected in the combo box or drop-down list. i.e. I have a list with item "X" and item "Y" and macros "X" and "Y" which need to run when the corresponding items are clicked on. I need to create macro "Z" which I can assign to the combo box/drop-down list which will allow me to do this.

Please bear in mind I have near to zero knowledge of VBA.

Thank you in advance


No luck finding an answer? You could always try Google.