Assign cell with value chosen in a combobox

  • This should be pretty easy, but I'm a bonehead and can't get it to work.


    Say I have a combobox in A1,


    Later, in B2, I want to show the selected value from the combobox. Not the index but the value.


    example - user makes choices across multiple sheets which are then nicely presented as a summary elsewhere in the same book.


    I've tried playing with indirect and offset but I can't get it.


    Thanx

    <b><font color="#23069E">_NFio_</font></b>

  • Re: assign cell with value chosen in a combobox


    I am not really sure what you want, try attaching an example workbook. This might be it

    Code
    Range("B2").Value=Combobox1.Value
  • Re: assign cell with value chosen in a combobox


    If the value is placed in a cell
    combox value goes in A1
    then cant you just put =A1 in B2 to pull the value.

    [SIZE=2]I should change my name to STUMBED![/SIZE]

  • Re: assign cell with value chosen in a combobox


    Quote


    If the value is placed in a cell
    combox value goes in A1
    then cant you just put =A1 in B2 to pull the value.


    The combobox is placed in Cell A1 (and not the value) :)

  • Re: assign cell with value chosen in a combobox


    What type of combobox are you referring to?


    If it's from the Forms toolbar then the index will be placed in the linked cell.


    If it's from the Control ToolBox toolbar then the value will be placed in the linked cell.

  • Re: assign cell with value chosen in a combobox


    The combobox is from the forms toolbar
    contains an input range from a defined name list
    is located over cell A1
    is also linked to cell A1 (shows index)


    It would be nice to do this as a formula instead of a change event in vba ...


    The control toolbar idea which will link the value is interesting, but it doens't seem the combobox cound be assigned a control outside of vba ..?


    I've attatched an example


    thanks a lot!

  • Re: assign cell with value chosen in a combobox


    Yeh, funny thing is that, when the user was done, I was thinking about having a button control - say 'create summary page'. The sub would then create a new printer freindly sheet and fill in all the relevent information and their corresponding values automatically. At the time it seemed like overkill as most of my referencing is straight text (as easy as =A1 in the formula bar) and checkboxes (cell-linking) ... Then I got started on obtaining the value of a combobox (instead of the index) and it's digging into the back of my head. Some way, any way, I must solve this absurdly simple task ... It's so logical and it's driving me nuts :D guess I'm wierd like that ...

    <b><font color="#23069E">_NFio_</font></b>

  • Re: assign cell with value chosen in a combobox


    Quote from _NFio_


    The control toolbar idea which will link the value is interesting, but it doens't seem the combobox cound be assigned a control outside of vba ..?


    What do you mean? If you use a combobox from the Control Toolbox you don't need VBA to either populate it or put the value in a cell.


    You just set the appropriate properties.

  • Re: assign cell with value chosen in a combobox


    You could try this


    =CHOOSE(B3,resource!A2,resource!A3,resource!A4,resource!A5,resource!A6,resource!A7,resource!A8,resource!A9)

    [SIZE=2]I should change my name to STUMBED![/SIZE]

  • Re: assign cell with value chosen in a combobox


    thanx chrisxs5, worked perfectly, and i see what it does, kinda a backwards way of referencing the combobox value, but hey - it works :D


    norie - I couldn't get it to work the way I wanted without putting the combobox on a form in the vb editor, if I just put the combox on the page then a left click selects the combobox and doesn't open the options .. ? thanx anyway

    <b><font color="#23069E">_NFio_</font></b>

  • Re: assign cell with value chosen in a combobox


    Quote from _NFio_;205649

    Yeh, funny thing is that, when the user was done, I was thinking about having a button control - say 'create summary page'. The sub would then create a new printer freindly sheet and fill in all the relevent information and their corresponding values automatically. At the time it seemed like overkill as most of my referencing is straight text (as easy as =A1 in the formula bar) and checkboxes (cell-linking) ... Then I got started on obtaining the value of a combobox (instead of the index) and it's digging into the back of my head. Some way, any way, I must solve this absurdly simple task ... It's so logical and it's driving me nuts :D guess I'm wierd like that ...



    Today, I had the exact and same issue. One page with Text Boxes and a Command Button. I just cannot get the Drop Down Box to work. I used .index just to stop it debugging and it returns the index. Instead I want to return the text selected in the combo box.


    Currently, All Text Box data is placed in a row on a separate worksheet in individual cell ranges. It all works, except for the combo box. I am stumped!!!!


    Wish I had an answer for you.


    Private Sub DateOfIncident_Change()
    DateOfIncident.Text = Date
    End Sub



    Private Sub EnterIncidentButton_Click()
    Sheets("Incident Report").Select
    Sheets("Incident Report").Range("A4").Value = NameOfPerson.Text
    Sheets("Incident Report").Range("B4").Value = DateOfIncident.Text
    Sheets("Incident Report").Range("C4").Value = IncidentComment.Text
    Sheets("Incident Report").Range("E4").Value = QuantityUsed.Text
    Sheets("Incident Report").Range("D4").Value = ItemUsedDropDown.Index
    End Sub



    Private Sub EnterIncidentButton_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Sheets("Incident Report").Select
    End Sub

  • Re: assign cell with value chosen in a combobox


    Hi Norie,


    Would that work for my example below. Everything works except the combo box called ItemUsedDropDown
    Do I code a different event? What dot notation would I use to refer to the actual text selected at time of clicking the button?



    Private Sub DateOfIncident_Change()
    DateOfIncident.Text = Date
    End Sub



    Private Sub EnterIncidentButton_Click()
    Sheets("Incident Report").Select
    Sheets("Incident Report").Range("A4").Value = NameOfPerson.Text
    Sheets("Incident Report").Range("B4").Value = DateOfIncident.Text
    Sheets("Incident Report").Range("C4").Value = IncidentComment.Text
    Sheets("Incident Report").Range("E4").Value = QuantityUsed.Text
    Sheets("Incident Report").Range("D4").Value = ItemUsedDropDown.Index
    End Sub



    Private Sub EnterIncidentButton_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Sheets("Incident Report").Select
    End Sub

  • Re: assign cell with value chosen in a combobox


    Hello,


    This topic is 12 years old.
    Please start your own topic, you can refer to this topic if you wanted.


    Wigi

    Regards,


    Wigi


    Excel MVP 2011-2014


    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes


    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!