Populating a Combobox according to cell values

[Regouin]

I want to populate a Combobox with only the cells that contain data within a certain range, say (A2:A44). Now the sheet would look like this

Monthly
grease ball bearings
grease conveyor belt

3-Monthly
refill oil

Yearly
change conveyor belt

4-Yearly
change ball bearings

now the amount of maintenance tasks can vary so instead of 1 or 2 I want to be able to add a maintenance task and have the combobox pick it up.
Preferably I would like to have the combobox not show the monthly, 3-monthly, yearly and 4-yearly but only the maintenance tasks.

Any help would be appreciated, What I have now is that it points to certain cells but when you add one task i doesnt pick it up.

TIA
Frank

[Jacob Hilderbrand]

You can do something like this. This example is for a ComboBox on a User Form, but you can do the same thing with a ComboBox on a worksheet (Just change the Event to Worksheet_Activate and/or Worksheet_Change).



Option Explicit

Private Sub UserForm_Initialize()
Dim i As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
If Range("A" & i).Value <> "" And _
Range("A" & i).Value <> "Monthly" And _
Range("A" & i).Value <> "3-Monthly" And _
Range("A" & i).Value <> "Yearly" And _
Range("A" & i).Value <> "4-Yearly" Then
Me.ComboBox1.AddItem Range("A" & i).Text
End If
Next i
End Sub

[Regouin]

Ok, thanks Jake, that works, but only with the active sheet, I want to combobox to select the data from sheet2 without activating sheet2 and with the following



With worksheets("sheet2")
Dim i As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For i = 1 To LastRow
If Range("A" & i).Value <> "" And _
Range("A" & i).Value <> "Monthly" And _
Range("A" & i).Value <> "3-Monthly" And _
Range("A" & i).Value <> "Yearly" And _
Range("A" & i).Value <> "4-Yearly" Then
Me.ComboBox1.AddItem Range("A" & i).Text
End If
Next i
end with



that doesnt work, i couldnt find out what I did wrong, but I can only get it to work when I activate the sheet and thats not what I want.

tia




Ok, I fixed it by adding the worksheet in front of each range, dont really know why it wouldnt work with the with worksheets("sheet2") but it works now so I stop complaining :)