Dynamic named range to populate Userform Listbox

[Shoeboxken]

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

 

[Pecoflyer]

Please wrap your code with code tags. Thank you

 

[snb]

And never use Rowsource:

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

 

[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.

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?​

 

[Kenneth Hobson]

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.

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.

 

[Pecoflyer]

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

 

[Shoeboxken]

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

View attachment UserForm.xlsm​

 

[Kenneth Hobson]

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.

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

 

[snb]

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

 

[Shoeboxken]

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​

 

[snb]

Test it.

 

[Pecoflyer]

Please read post #2 and comply. Thank you