Forcing RowSource in ComboBox to increase

Please Login or Register  to view this content.

I have the above code to add a new 'consultant' to an existing list should the user wish to do so. I've tried to set the code to automatically increase the size of the RowSource currently being used in VBA (which at the moment is set as C1:C10) however it just doesn't work.

I've tried setting the RowSource manually with VBA code however it doesn't seem to work for me so I've had to use the properties window. Does anyone know a way of forcing VBA to change a value set in itself (if a new consultant was added now it should automatically change the RowSource to C1:C11, then C1:C12 for the next one etc)?

Many thanks in advance for anyones help.

~Liam

Hello Liam,

It would help to know if you are getting errors messages when the code runs, and which ones. If your UserForm isn't loaded, you can't access any of the controls on it. I suspect this maybe the case.

Sincerely,
Leith Ross

Originally Posted by LiamPotter

I have the above code to add a new 'consultant' to an existing list should the user wish to do so. I've tried to set the code to automatically increase the size of the RowSource currently being used in VBA (which at the moment is set as C1:C10) however it just doesn't work.

I've tried setting the RowSource manually with VBA code however it doesn't seem to work for me so I've had to use the properties window. Does anyone know a way of forcing VBA to change a value set in itself (if a new consultant was added now it should automatically change the RowSource to C1:C11, then C1:C12 for the next one etc)?

Many thanks in advance for anyones help.

~Liam

Google Excel dynamic named ranges.

If you create a named range called ConsultantList and set its refers to value to

=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C),1)

the range becomes dynamic and you can use the ConsultantList name as the RowSource property in the ComboBox.

When you add a new consultant to the list the range automatically grows and no further code is required, except to sort the list.

I have some code if you need any more help.

I've never used dynamic ranges before however doing as you have mentioned has worked perfectly.

Upon testing once by adding and removing the consultant XXX, the ListBox doesn't shrink again and leaves a blank entry behind. I've managed to come around this by simply adding the following code:

Please Login or Register  to view this content.

Many thanks for your response, has worked perfectly!

~Liam