Expanding a list for combobox

[AnnetteTR]

Hi
I am making a userform where you enter "room number", "airvolume" and the name for a "ventilation unit".

I want to make the input for the ventilations unit as a Combobox.
When you start the list i empty - as you haven't defined any yet.
But when you enter the next room it must be possible to either choose one of the previous used or ad a new name.

How can that be done?

Regards
Annette

 

[AnnetteTR]

Hi
In order to do so I define a named range on Sheet1 called rngVentUnit
with:

 =OFFSET(Sheet1!$A$1;0;0;COUNTA(Sheet1!$A:$A);1)

On a Userform I define a Combobox called cbxVentUnit
in the code for the userform I use this:

Private Sub UserForm_Initialize()
With cbxVentUnit
        .RowSource = Sheet1.Range("rngVentUnit").Address
        .ListIndex = 0
    End With
End Sub

This works fine.

When I take this code and use it in a bigger Userform called frmUserInput which has 4 comboboxes and a lot of textboxes it doesn't work.

I have tried to put in the Private Sub UserForm_Activate()
this gives the fault:
"Method 'Range' of object'_Worksheet' failed
and debug points at :

     .RowSource = Sheet1.Range("rngVentUnit").Address

and in the Private Sub UserForm_Initialize() this gives the fault:
"Method 'Range' of object'_Worksheet' failed
and debug points at the call of the userform

The other comboboxes works fine and they have fixed ranges.

So the first step of the way is to get the named range to work.
Help Please.

Regards Annette

 

[Andrew Poulsom]

Have you tried?

.RowSource = "rngVentUnit"

 

[AnnetteTR]

Hi Andrew
Thank you - now that part works.
I found a thread about expanding the list - I will try that before I return.

Regards Annette