none
Excel and Access - Is it possible to have a Combobox “integrated” with Excel cells? RRS feed

  • Question

  • I want to use data from an Access query in an Excel spreadsheet. I have the code for it: “DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel ………”

    When the data is within an Excel file (ExcelOne) I want to use another Excel file (ExcelTwo) where I link the data from ExcelOne into Comboboxes on a spreadsheet.

    I want every Cell in a certain Column to have Comboboxes. An idea on what I want is shown below

    BrewDate

    Batches

    Recipe

    2016-11-12

    2

    2016-11-13

    4

    2016-11-14

    1

    PLACE OF THE COMBOBOX

    2016-11-15

    3

    S:t Eriks Capital IPA

    Sigtuna Black Soil IPA

    2016-11-16

    2016-11-17

    2016-11-18

     

    I can add a Combobox, manually cell by cell. But I want “every” cell in Column “Recipe” to have a Combobox. When I now add a Combobox it appear to float in front of the active cell, and not being the cell.

    Is it possible to have a Combobox “integrated” with cells? Or is there another solution?

    The data in the Combobox is coming from the file ExcelOne and looks like this:

    RecipeNo

    Name

    55

    S:t Eriks APA (Reducerat)

    56

    S:t Eriks IPA (Reducerat)

    70

    Sigtuna Pale Ale (Fullt)

    90

    Sigtuna Winter IPA EKO (Fullt)

    92

    Sigtuna Organic Ale (Reducerat)

    102

    S:t Eriks IPA (Fullt)

    104

    Sigtuna Organic Ale (Fullt)

    111

    S:t Eriks APA (Fullt)

    141

    S:t Eriks Capital IPA

    152

    S:t Eriks FolkölsIPA Session 3,5%

    153

    Sigtuna Lager Organic

    154

    Sigtuna Folköl Organic ale 3,5%

    159

    Sigtuna Jumbo Dubbel IPA

    162

    Sigtuna Black Soil IPA

    168

    S:t Eriks Julale EKO 2016 (fullt)

     


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Wednesday, December 7, 2016 8:47 PM

Answers

  • It seem that you are describing a Data / Data Validation / List with an in-cell dropdown. The drawback is that you won't have the multiple columns as you do for a combobox (for example, to associate the RecipeNo to the Name selection).

    You could use a Match() function to get the proper source table row, and then an INDEX() can grab whatever additional data you need from the ExcelOne range. To make this work, you would have to assure each name is unique.
    Also, to make this work, it's best to set the DataValidation Settings Source to a Defined Name. The defined name may also need to be 'scoped' to the worksheet in ExcelTwo, the worksheet you want to have the comboboxes.

    Hope this helps or gets you started


    -MainSleuth

    Wednesday, December 7, 2016 9:27 PM

All replies

  • It seem that you are describing a Data / Data Validation / List with an in-cell dropdown. The drawback is that you won't have the multiple columns as you do for a combobox (for example, to associate the RecipeNo to the Name selection).

    You could use a Match() function to get the proper source table row, and then an INDEX() can grab whatever additional data you need from the ExcelOne range. To make this work, you would have to assure each name is unique.
    Also, to make this work, it's best to set the DataValidation Settings Source to a Defined Name. The defined name may also need to be 'scoped' to the worksheet in ExcelTwo, the worksheet you want to have the comboboxes.

    Hope this helps or gets you started


    -MainSleuth

    Wednesday, December 7, 2016 9:27 PM
  • Hi MainSleuth

    I found your advice helpful. The "Data / Data Validation / List with an in-cell dropdown" works fine.
    Will continue with this on Friday. Today is a pre Xmas party day for all the staff at brewery :-)

    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Thursday, December 8, 2016 7:11 AM
  • I was thinking that there should be production sampling as part of the (ahem) 'solution' (sorry about the pun...).
    Merry Xmas

    -MainSleuth

    Thursday, December 8, 2016 2:37 PM
  • Lots of people has helped me in this forum.
    I say to all of you. Please come and visit my brewery.
    We ha a tasting room too :-)

    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Friday, December 9, 2016 11:25 AM