Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
linking a form combo box... results from the combo box to another | Excel Discussion (Misc queries) | |||
combo reference on another combo box for picking address etc. | Excel Worksheet Functions | |||
Can one combo box control the data in a different combo box | Excel Discussion (Misc queries) | |||
"Combo Box - getting control combo box to stick in place in worksh | Excel Discussion (Misc queries) | |||
"Combo Box - getting control combo box to stick in place in worksh | Excel Discussion (Misc queries) |