Question Only: Can You Reference a userform combobox in a spreadsheet formula?

[TheScott]

I have a formula that references data validation dropdowns on a sheet. I would like to relocate those dropdowns to a userform in the form of a combobox but leave the reference list and the processing on the spreadsheet. Is it possible?

[Jonmo1]

Short answer, NO, you can't refer to a combobox with a sheet formula.

However, you CAN refer to the Cell(s) that combobox get's its values from and/or where it put's the value that is selected in the combobox..
Which type of combobox is on the form, ActiveX or Userform?

[TheScott]

It is a userform control.

I have added an example of what I am doing on the spreadsheet. The dropdown interacts in two ways. The formula sees what happened in the dropdown selection and then changes the choices available. I don't know if you can't give direct control to a combobox if it can be done but If you would have a look I would appriciated it.

Thanks
Scott

Adjusting List Content - Allocations.xlsm

[Jonmo1]

If your goal (according to the notes in the book you posted) is to do it all in VBA
Then there's no need for a sheet formula to refer to the combobox.
Or did I miss something?

With VBA, you can refer to the value selected in the combobox like

MsgBox UserForm1.ComboBox1.Value

[TheScott]

Yeah... that little bit about too little knowledge to do the coding. I am learning... slowly so just trying to do the things I think I can understand.

Thanks

[Jonmo1]

OK, no worries..

Again, the cold hard answer to the question is still NO.
A formula can't refer to a combobox.


But speaking in general terms..
The use of a userform is generally for a user to enter data. And that data is then transferred to the sheet somewhere. (adding a record)
So your formulas don't 'need' to refer to the combobox.
Instead, they can refer to the cell(s) that the combobox put it's value in.

Also, generally speaking, comboboxes Get their initial 'list' of values (which a user selects one from) from a range of cells already on the sheet.
So your formulas can refer to those cells as well, instead of trying to refer to the list contained in the combobox.

[TheScott]

Thanks for the little push. Sometimes I just need a little kick.... It wasn't as difficult as I had thought. Perhaps one day I will figure this stuff out without what now looks like an elementary question.... but atleast I know that you can't reference a userform control in a spreadsheet formula.:-)

Thanks
Scott

[Jonmo1]

Glad to help, thanks for the feedback.