Failing miserably at populating combobox

[EirikDaude]

I've tried to populate a combobox for about an hour now, but no matter what I do, it refuses to get any visible values. It is populated by... something, but I'm not at all sure what.

Can any of you guys give me a pointer on what I am doing wrong?

Initialization code, and output to immediate window:

What the dropdown menu looks like upon initialization:

The range I try to put into the combobox:


The text in the cells is generated by a formula, if that matters. The formula is

=OFFSET($D$1;0;0)
=OFFSET($D$1;0;7)
(...)
=OFFSET($D$1;0;70)

Any help would be much appreciated

[Bob Phillips]

Post the workbook, half the code is cut-off in your pictures.

[snb]

Made for you:

http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

[EirikDaude]

Post the workbook, half the code is cut-off in your pictures.

Ok... I figured that since what was cut off was just the setting of the range, and the output in the immediate window indicates that the range is set (more or less) correctly, that shouldn't matter. Hopefully having a look at the book can be of some help to you: OEE - endeleg 2.xlsm

Made for you:

http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

Thanks! Actually I've seen several of the solutions you suggest on your very informative page before, and I have tried to implement them for my worksheet, but obviously I am failing somewhere along the way. If you can see what it is I am doing wrong that would be great!

Here is all of the code of the form, just in case you don't want to go looking for it, I think everything relevant was visible in the OP, but just in case:

Private Sub cmbUtstyr_Change()
  Me.btnOK.Enabled = cmbUtstyr.ListIndex > -1 And cmbKategori.ListIndex > -1
End Sub

Private Sub UserForm_Initialize()
  Dim c As Range, i As Long, r As Range
  'Me.cmbKategori.List = årsakslister.Columns("K:K").SpecialCells(2).Value
  'For Each c In årsakslister.Columns("A").SpecialCells(-4123)
    'Me.cmbType.List = årsakslister.Columns("A").SpecialCells(-4123).Value
  '  Me.cmbType.AddItem c.Value, i
  '  i = i + 1
  'Next
  'Me.cmbType.List = Range(årsakslister.Columns("A:A").SpecialCells(-4123).Address(external:=True))
  Set r = Range(årsakslister.Columns("A:A").SpecialCells(-4123).Address(external:=True))
  Debug.Print (r.Address)
  Me.cmbType.List = r.Value
  Debug.Print (årsakslister.Columns("A:A").SpecialCells(-4123).Address(external:=True))
  'Debug.Print (årsakslister.Columns("K:K").SpecialCells(2).Address(external:=True))
End Sub

Private Sub cmbKategori_Change()
  Me.cmbUtstyr.ListIndex = -1
  With Me.cmbKategori
    If .ListIndex > -1 Then
      'Me.cmbUtstyr.BackColor = vbWhite
      'Me.cmbUtstyr.Enabled = True
      'Me.cmbUtstyr.List = Filter(Evaluate("transpose(if(Årsakslister!D1:D200=""" & .List(.ListIndex, 0) & """,Årsakslister!E1:E200,""~""))"), "~", False)
    End If
  End With
End Sub

Private Sub btnOK_Click()
  Me.Hide
End Sub
Private Sub btnAvbryt_Click()
  Unload Me
End Sub

As you can see I am trying to migrate some of the solutions I was presented with in this thread, which you may remember, into a larger setting - judging from my success so far I am sure I'll run into a ton of trouble :P

[snb]

This suffices:

Private Sub UserForm_Initialize()
  cmbType.List = årsakslister.Columns(1).SpecialCells(-4123).Value
End Sub

In designmode:
cmbType columncount =1
cmbtype columnwidths ""

[EirikDaude]

Thanks! I will give it a go once I get to work tomorrow

[EirikDaude]

It worked perfectly, snb, thank you so much!

I think I said at some previous occassion when I got help with a userform that I had to learn to pay more attention to the properties of the objects I work with, maybe now that it's bitten me again I'll remember

Hmm, just for future reference, can anyone recommend a good source for looking up what the various properties do? Apart from Excel-help, I mean?