combo box
 

I am trying to create a combo box that pulls the dates from column C i
the current worksheet for however many dates there are in the column
ie. sometimes there are 20 rows sometimes there are 35 rows. I jus
want the combo box to read and pull in the cells with values. The way
have it set now, it just pulls a set range because I know there wil
never be more than 150 rows, so I set the range to C2:C150. The proble
here is that the combo box pulls all the empty cell values into th
combo box within this range. I had a similar problem with settin
dynamic ranges but that code doesn't seem to work for this problem.
think it has something to do with setting the range in the combo bo
rowsource property? But then again I have no idea.

Any ideas?

Also, after I select a certain date, I would like to attach a comman
to my OK button that will select and delete all the rows beneath th
selected date and then run the rest of my macro.

Any help would be greatly appreciated.
thanks

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

combo box
 

The text does not look right because it wraps, however if
you copy the code and put it into a VBA module everything
should be ok.

Fill ComboBox with dynamic row set:

Private Sub UserForm_Initialize()
Dim LR As String
LR = ActiveSheet.Cells.SpecialCells
(xlCellTypeLastCell).Address
ComboBox1.RowSource = "A1:" & LR 'LR = address of last row
in the WS
End Sub

Delete rows below a selected date from ComboBox3:
Note: You will have to supply the trigger event that runs
this sub. Like an On_Change, On_Enter, or whatever.

Private Sub ComboBox3()
test = ComboBox3.Value
Range("c1").Select

Do
If ActiveCell.Value < test Then
ActiveCell.Offset(1).Activate
Else
ActiveCell.Offset(1).Activate
Selection.End(xlToLeft).Select
Range(Selection, ActiveCell.SpecialCells
(xlLastCell)).Select
Selection.Delete
Range("A1").Activate
GoTo Line1
End If
Loop Until ActiveCell.Value = ""
Line1:

End Sub

To speed up this sub put the following line in after the
Dim Statement.
Application.ScreenUpdate = False

-----Original Message-----

I am trying to create a combo box that pulls the dates
from column C in
the current worksheet for however many dates there are in
the column,
ie. sometimes there are 20 rows sometimes there are 35
rows. I just
want the combo box to read and pull in the cells with
values. The way I
have it set now, it just pulls a set range because I know
there will
never be more than 150 rows, so I set the range to
C2:C150. The problem
here is that the combo box pulls all the empty cell
values into the
combo box within this range. I had a similar problem with
setting
dynamic ranges but that code doesn't seem to work for
this problem. I
think it has something to do with setting the range in
the combo box
rowsource property? But then again I have no idea.

Any ideas?

Also, after I select a certain date, I would like to
attach a command
to my OK button that will select and delete all the rows
beneath the
selected date and then run the rest of my macro.

Any help would be greatly appreciated.
thanks!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.

combo box
 

In the code pane for the particular worksheet, use the
Worksheet Activate subroutine (assuming the the combo box
is called ComboBox1, and is from the Controls toolbox)
viz.

Private Worksheet_Activate()
ComboBox1.ListFillRange = Range(Range("C2"), Range
("C2").End(xlDown)).Address(True, True, xlA1, True)

End Sub

Kevin Beckham

-----Original Message-----

I am trying to create a combo box that pulls the dates
from column C in
the current worksheet for however many dates there are in
the column,
ie. sometimes there are 20 rows sometimes there are 35
rows. I just
want the combo box to read and pull in the cells with
values. The way I
have it set now, it just pulls a set range because I know
there will
never be more than 150 rows, so I set the range to
C2:C150. The problem
here is that the combo box pulls all the empty cell
values into the
combo box within this range. I had a similar problem with
setting
dynamic ranges but that code doesn't seem to work for
this problem. I
think it has something to do with setting the range in
the combo box
rowsource property? But then again I have no idea.

Any ideas?

Also, after I select a certain date, I would like to
attach a command
to my OK button that will select and delete all the rows
beneath the
selected date and then run the rest of my macro.

Any help would be greatly appreciated.
thanks!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.

combo box
 

Thanks for the input from both of you, my macro finally works!

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com