CODEWITHSUNDEEP
excelcomboboxresizerangevba
user2938867
user2938867

Reputation: 187

VBA excel: update rowsource for combobox control by resizing range

I am trying to make an VBA update rowsource routine for a series of 3 combobox controls in a userform. The three comboboxes all reside in a frame named "frm1" The rowsource of the comboboxes are three seperate columns in a worksheet. The worksheet are named "options" and the top cell in each of the three colums holding the rowsources are given the same name as the combobox that refers to it. The idea is that i can write in extra rows in the rowsource for any of the comboboxes (simply by typing it into the right column of the worksheet) and then run the routine and thereby automatically expand the rowsource selection for the combobox in question.

However, running the script (by calling it from an UserForm_Activate() routine) gives me "runtime-error '13': Type mismatch"

Can any of you help me out?

Private Sub UpdateCB()

    Dim j As Control
    Dim i As Integer

        For Each j In frm1.Controls

            i = 1

            Do Until ThisWorkbook.Sheets("options").Range(j.Name).Offset(i, 0).Value = ""

                i = i + 1

            Loop

            j.RowSource = ThisWorkbook.Sheets("options").Range(j.Name).Resize(i)

        Next j
End Sub

Upvotes: 0

Views: 1535

Answers (2)

snb
snb

Reputation: 343

if you use the .tag property to store the column number:

Private Sub Userform_initialize()
  with thisworkbook.sheets("options")
    combobox1.list=.columns(val(combobox1.tag)).specialcells(2).value
    combobox2.list=.columns(val(combobox2.tag)).specialcells(2).value
    combobox3.list=.columns(val(combobox3.tag)).specialcells(2).value
  end with
end sub

Upvotes: 0

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

ComboBox.rowsource expect range.address not range.value or range reference which you have now. So, add .address property at the end of problem line in this way:

j.RowSource = ThisWorkbook.Sheets("options").Range(j.Name).Resize(i).address

Upvotes: 1

Related Questions