• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Renew combobox with values in column a instead of row 1

Hello everybody,
I have this code
Code:
Private Sub CommandButton3_Click()
ComboBox1.Clear
 Dim i As Long, lastcol As Long
  lastcol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
  For i = 1 To lastcol
  ComboBox1.AddItem ActiveSheet.Cells(1, i)
  Next i

End Sub
But instead of looking in row 1 and repopulate the combobox, it should look in kolom A and renew the combobox
Thanks
 
Hi ,

Try this :
Code:
Private Sub CommandButton3_Click()
            ComboBox1.Clear
            Dim i As Long, lastrow As Long
            lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
            For i = 2 To lastrow
                ComboBox1.AddItem ActiveSheet.Cells(i, 1)
            Next i
End Sub
I have assumed row 1 is your header ; even though you now want to look at column A , I have assumed that cell A1 is blank , which is why the For ... Next loop starts from 2 ; if this is not correct , change this 2 to 1.

Narayan
 
Hello Narayan,
Thank you for your support, it works great but it fills the combobox
Like this
Week 16
Week 16
Week 16
etc
And then
Week 17
Week 17
Week 17
Is it possible to have only
Week 16
Week 17
Thank you , you are doing a great job
 
Hi ,

Then the code will be more complicated , since it is not just a matter of using rows instead of columns.

The easiest way would be to create a pivot table to get the unique entries , and then use this to populate the combobox. If you can upload your workbook with the data and the combobox in it , it will be easier to post the code.

Narayan
 
Hello Narayn,
Thank you for your reply, for some reason i cannot upload a file, when I press upload file I cannot choose a file, when i open the location where the files are i do not see any file, do you have an idee why?
thanks
 
I found a solution
Here is the code, maybe it is helpfull for somebody

Code:
Private Sub CommandButton3_Click()
With ActiveSheet
 If .AutoFilterMode Then .AutoFilterMode = False
 sn = .Range("A4:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
  For i = 1 To UBound(sn)
  If InStr(1, naam, "|" & sn(i, 1), vbTextCompare) = 0 Then naam = naam & "|" & sn(i, 1)
  Next i
  ComboBox1.List = Split(Mid(naam, 2), "|")
End With
End Sub
Thank you again for your help
Your are doing a great job
Greetz
Jack
 
Back
Top