Vba listbox RowSource dynamic range

So I need to fill a list box with information from cells C2:G2

It can go past G2 depending on the previous user inputs

So far I have

I know this is not correct, but I have no idea which part of the code to change

1. 2017-12-02, 02:00 AM #1

   Register for a FREE account, and/
   or Log in to avoid these ads!

   HI, I have encountered an error 'runtime 380' while trying to have a listbox update via dynamic range in a userform. It seems the problem I have is when there is no information in the dynamic range. When I put a test line into the range the listbox populates with the test. Can you assist with this problem? relevant information: Dynamic range "group1": =OFFSET[$E$5,0,0,COUNT[$E:$E],5] Combobox1 [sheet selection][used to populate the ROWSOURCE in the listbox]: Sub combobox1_click dim x as integer x = combobox1.listindex select case x case is 0 listboxname.rowsource = "group1" end select

   end sub

2. 2017-12-02, 09:29 AM #2

   Please wrap your code with code tags. Thank you

   Thank you Ken for this secure forum.

3. 2017-12-02, 03:29 PM #3

   And never use Rowsource:

   Code:

   Sub combobox1_click if combobox1.listindex=0 then listbox1.List =cells[5,5].resize[100].specialcells[2].resize[,5].value end sub

4. 2017-12-02, 06:24 PM #4

   Hi,

   Thank you for your replies.

   Pecoflyer: I am not sure what you mean to wrap the code. I am assuming its the same as the example that snb has provided. If yes, I am not sure how to do this. I am relatively new to this forum and have not been exposed to all its intricacies. I typed the code in.

   SNB: Could you explain why I shouldn't use rowsource? The tutorial I was following suggested this as the correct model to follow. I am not sure what the code you have written is doing exactly. I appears that its referring to row 5 column 5 and not sure what the rest of the code is implying. Combobox1 is designating a sheet for the rest of the user form and in the code for the combobox1 change it refers to the dynamic range from each sheet depending on what selection was made. Is the code you provided creating a dynamic range that is 5 columns across?

5. 2017-12-03, 06:09 AM #5

   Click the Go Advanced button in lower right of a reply, then click the # icon to insert the code tags. I would copy and paste code between the tags but I guess you can type it if you like typing. Or, type them: e.g. [code]MsgBox "Hello World!"[/code] but replace []'s with []'s. Most are forums do it that way. Why not just not fill the listbox if it errors? e.g.

   Code:

   Private Sub UserForm_Initialize[] Dim s$ s = "group1" ListBox1.ColumnCount = 5 On Error Resume Next ListBox1.RowSource = s End Sub

   Rowsource is not as flexible as List. I generally like List best but if you want to include row1 column headings, Rowsource can do it.

   Of course Count does numerical data and not string data. Use CountA if you want both strings and number counts. So, E1:E10 might be strings so count is 0 and it would error. If E11=1, then count is 1 so E2:I2 is shown in the listbox.

6. 2017-12-03, 09:29 AM #6

   Originally Posted by Shoeboxken

   Hi,

   Thank you for your replies.

   Pecoflyer: I am not sure what you mean to wrap the code. I am assuming its the same as the example that snb has provided. If yes, I am not sure how to do this. I am relatively new to this forum and have not been exposed to all its intricacies. I typed the code in.

   You will find answers in the FAQ

   Thank you Ken for this secure forum.

7. 2017-12-04, 04:41 AM #7

   Hi Kenneth, Thank you for the reply. If you have the time, please have a look at the project I am working with and if the code you suggested is applicable. Not all the buttons are working as I have not placed any code into them. The userform will open and allow selections but no addition or clearing. I had to place a dummy line of data in the Sheets where the BGList is applied so that it would populate the Listbox. Regards Ken

   UserForm.xlsm

8. 2017-12-04, 05:45 AM #8

   If you want your listbox to update, then change the Rowsource values in that worksheet. You need to refer to your dynamic named range using the sheet's name.I also added the Clear button code which triggers the clear in the combobox change.

   Code:

   Private Sub ComboBox1_Change[] Dim x As Integer x = ComboBox1.ListIndex Select Case x Case Is = 0 lstBG.RowSource = "BattleGroup1!BG1List" Case Is = 1 lstBG.RowSource = "BattleGroup2!BG2List" Case Is = 2 lstBG.RowSource = "BattleGroup3!BG3List" Case -1 lstBG.RowSource = "" End Select End Sub Private Sub CommandButton2_Click[] ComboBox1.ListIndex = -1 End Sub

   Last edited by Kenneth Hobson; 2017-12-04 at 05:54 AM.

9. 2017-12-04, 10:55 AM #9

   Code:

   Private Sub ComboBox1_Change[] if combobox1.listindex>-1 then lstBG.list=sheet1.columns[11 + 2*combobox1.listindex].specialcells[2] End Sub

10. 2017-12-04, 07:10 PM #10

    Hi Kenneth,

    Thanks for your reply. I will add the line for the unspecified sheet selection. From what I gather, if there is no lines of data in the dynamic range there will be an error message so there will always have to be one line of data existing in the dynamic range to prevent this. I will just keep the dummy line.
    Many Thanks

    
    Ken

    Hi SNB,

    I am unsure how the code you have written is intended to work. If you could give me a quick explanation it would be extremely helpful.

    Regards

    
    Ken

    Last edited by Shoeboxken; 2017-12-04 at 07:14 PM.