Search code examples
excelvbauserform

How to set controlsource to specific row of selected/active column?

Just a question if there's a way to define controlsource for, say, combobox value to a dynamic range where it's always corelates to the same row but selected/active column. I am trying to reuse the same UserForm to fill out similar data in a sheet, moving to the right by one column and it would be extremely helpful if control source would move along with the selection.

Kinda like

UserForm_Format.FilmType_ComboBox.ControlSource = Cells(3, ActiveCell.Column)

But this particular code doesn't work

Could someone, please, help me with this question?

Solution

  • If you read the manual of the ControlSource property you see that the value you can set has to be a String.

    So this line

    UserForm_Format.FilmType_ComboBox.ControlSource = Cells(3, ActiveCell.Column)

    actually defaults to

    UserForm_Format.FilmType_ComboBox.ControlSource = Cells(3, ActiveCell.Column).Value

    what you do here is setting the ControlSource the value in that cell. This won't work unless that value is a valid address.

    So you probably meant to do the following:

    UserForm_Format.FilmType_ComboBox.ControlSource = Cells(3, ActiveCell.Column).Address