locked
Excel 2010/13 Form ComboBox - Return Additional Column Data (Not Value or Index) RRS feed

  • Question

  • Hi,

    Here is my situation.  I have an Excel sheet where users can insert and copy columns and rows to their heart's content.  Some of the data entry fields are ComboBoxes and when they copy/insert the ComboBox needs to be copied as well.  The kicker is that the linked cell refreence needs to be dynamic.  Because of these requirements I am using the Form ComboBox object and some VBA.  I add the ComboBox and set the Input Range to be an absolute range (i.e., $A$1:$B$3), leave the Cell Link blank and then have added a Module and added the following VBA code: 

    Sub DropDown_Change()
      With ActiveSheet.DropDowns(Application.Caller)
      .TopLeftCell.Value = .Value
      End With
    End Sub

    This works close to what I want but it is not exactly what I need. My Input Range has the following data in A1 to B3:

    Alpha       200
    Bravo       300
    Charlie     600

    So here is my problem.  I can get the name to store in the cell (i.e., Alpha, Bravo, Charlie) or I can get the Index (i.e., 1, 2, 3) to store in the cell, but what I really need is for the dropdown to show Alpha, Bravo or Charlie, but get 200, 300 or 600 to store in the cell.   Any suggestions?

    Alan 


    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Wednesday, July 31, 2013 2:12 PM

Answers

  • Try this then:

    Sub DropDown_Change()
        With ActiveSheet.DropDowns(Application.Caller)
            .TopLeftCell.Value = Range(.ListFillRange).Cells(.Value, 2)
        End With
    End Sub


    Regards, Hans Vogelaar

    Wednesday, July 31, 2013 7:18 PM
  • I forgot to mention that you have to set the List Fill Range of the combo box to B17:B20 instead of to B17:D20. A Forms combo box uses one column anyway, but the code fails if the List Fill Range has more columns.

    See https://www.dropbox.com/s/sdsxndon02vqec4/SampleForPosting.xlsm


    Regards, Hans Vogelaar

    Friday, August 2, 2013 3:43 PM

All replies

  • You can't do that with a Forms combo box, at least not easily, but it is possible with an ActiveX combo box:

    Set its ColumnCount to 2, ColumnWidths to (for example) 72pt;0pt, BoundColumn to 2 and RowSource to A1:B3.


    Regards, Hans Vogelaar

    Wednesday, July 31, 2013 3:18 PM
  • Because of some I won't bore everyone with the use of a ActiveX control is problematic although it does support this concept more readily.  So any suggestions on doing it with the Form control?  In my perfect world it would be a Data Validation item but it is the same issue with a display name vs. value to save.

    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Wednesday, July 31, 2013 3:25 PM
  • Try this then:

    Sub DropDown_Change()
        With ActiveSheet.DropDowns(Application.Caller)
            .TopLeftCell.Value = Range(.ListFillRange).Cells(.Value, 2)
        End With
    End Sub


    Regards, Hans Vogelaar

    Wednesday, July 31, 2013 7:18 PM
  • That worked!  Thanks.

    One follow up question.  I just noticed that if I close and reopen the workbook I am not seeing the labels in the dropdown.  I see the values in the cells (i.e., the 200, 300 or 600) but the dropdown is blank.  I can click the dropdown and see my labels and if I select one it updates the cell properly but if I close and reopen I don't see the labels - they are blank.

    Thoughts?


    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Wednesday, July 31, 2013 7:26 PM
  • Do you mean that the dropdown list is blank after closing and reopening the workbook?

    Regards, Hans Vogelaar

    Wednesday, July 31, 2013 7:48 PM
  • Yes and no.   If the initial time I select Alpha it will save 200 to the cell.  I can move the cursor into the cell and see 200 in the formula bar and see Alpha in the dropdown list.  I reopen it, I can see 200 in the formula bar but the dropdown list is blank.  If I click the dropdown list and expand it, I can see my choices again (Alpha, Bravo, Charlie).  However, on reopen I would expect that since the cell has 200 in it and the dropdown is linked to that cell that I would see Alpha show up in the dropdown list before I touch it.  

    I hope that explains it and it makes sense.


    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Wednesday, July 31, 2013 7:53 PM
  • Ah, OK. Since the combo box is not connected to the cell through its Cell Link property, it won't reflect the cell value automatically. You'd have to use code in the Workbook_Open event procedure in the ThisWorkbook module, or in the Worksheet_Activate event procedure in the worksheet module to set the value of the combo box.

    Regards, Hans Vogelaar

    Wednesday, July 31, 2013 8:20 PM
  • You have been incrediblly helpful so far.  Thank you.  I know I will have to tie this to either the worksheet activate or the workbook open event but I am having trouble with my logic to get the dropdown to populate correctly. 

    With ActiveSheet.DropDowns()
    DropDowns.Value = ???  (No matter what I try here I can't seem to get it to work)
    End With

    Any suggestions?


    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Thursday, August 1, 2013 1:28 PM
  • Try this:

        Dim obj
        For Each obj In ActiveSheet.Dropdowns
            obj.Value = obj.ShapeRange.TopLeftCell.Value
        Next obj


    Regards, Hans Vogelaar

    Thursday, August 1, 2013 3:30 PM
  • To test I put it on the worksheet activate event.  When I enter the sheet I am getting run-time error 438  object doesnt support this property or method and it debugs to the obj.Value - obj.SharpRange.TopLeftCell.Value line.


    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Thursday, August 1, 2013 3:40 PM
  • Sorry about that, I hadn't tested it. Try this:

    Private Sub Worksheet_Activate()
        Dim obj As DropDown
        For Each obj In ActiveSheet.DropDowns
            obj.Value = Application.Match(obj.TopLeftCell.Value, _
                Range(obj.ListFillRange), 0)
        Next obj
    End Sub


    Regards, Hans Vogelaar

    Thursday, August 1, 2013 4:36 PM
  • Run-time error '13'.  Type mismatch.  On the line:

     obj.Value = Application.Match(obj.TopLeftCell.Value, Range(obj.ListFillRange), 0)


    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Thursday, August 1, 2013 4:58 PM
  • I'm out of ideas - this worked for me.


    Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as Windows Live SkyDrive (https://skydrive.live.com), FileDropper (http://filedropper.com) or DropBox (http://www.dropbox.com). Then post a link to the uploaded and shared file here.

    Or register at www.eileenslounge.com (it's free) and post a message in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).


    Regards, Hans Vogelaar

    Thursday, August 1, 2013 8:18 PM
  • http://sdrv.ms/16mWpQ5

    This is a cut down workbook.  If I can get this sample working then my more complex one will work.  I changed the event to fire on a worksheet change that way I could just change a cell linked to a formula and see if it fired without having to enter and exit the worksheet.

    Thanks again for all the help.


    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Thursday, August 1, 2013 8:37 PM
  • I wouldn't use the Worksheet_Calculate event to set the value of the combo box. Use the Workbook_Open event in the ThisWorkbook module.

    Change the code for DropDown_Event_2 to:

    Sub DropDown_Event_2()
        Dim obj As DropDown
        For Each obj In ActiveSheet.DropDowns
            obj.Value = Application.Match(obj.TopLeftCell.Value, _
                Range(obj.ListFillRange).Offset(0, 1), 0)
        Next obj
    End Sub


    Regards, Hans Vogelaar

    Thursday, August 1, 2013 9:47 PM
  • Still getting the type mismatch even on an On Open event even in the sample workbook I provided.  I am beginning to think I might have to look a different route.  Hans, thank you very much for all your help.  You went above and beyond.

    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Friday, August 2, 2013 3:30 PM
  • I forgot to mention that you have to set the List Fill Range of the combo box to B17:B20 instead of to B17:D20. A Forms combo box uses one column anyway, but the code fails if the List Fill Range has more columns.

    See https://www.dropbox.com/s/sdsxndon02vqec4/SampleForPosting.xlsm


    Regards, Hans Vogelaar

    Friday, August 2, 2013 3:43 PM
  • So frustrating.  I change the obj.Value manually to something like obj.Value = 4 and it puts in the correct dropdown text value.  When I get the type mismatch error and I hover over the obj.Value = Application.Match.... statement for obj.Value I see the correct value, for TopLeftCell.Value I see the correct value and for ListFillRange I see the correct range.

    Why doesn't this Excel VBA code like me!!!


    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Friday, August 2, 2013 3:58 PM
  • SUCCESS!!!  That is is!  Thank you very, very much for all your help.

    My Ramblings @ http://alanwhitehouse.wordpress.com and My Tweets @ http://www.twitter.com/alanwhitehouse

    Friday, August 2, 2013 5:19 PM