Fill a ComboBox from a Column

RWayneH,

You may find the comments in the code helpful...

Patrick

Private Sub ListBox1_Click()
Dim i As Long
 
'when an item is selected in the ListBox, loop through all the items until the item is
'identified as selected, then output it to cell A1 on Sheet1
For i = 0 To Me.ListBox1.ListCount - 1
    If Me.ListBox1.Selected(i) = True Then
        Sheets("Sheet1").[A1] = Me.ListBox1.List(i)
    End If
Next i
End Sub
 
Private Sub UserForm_Initialize()
Dim rng As Range
Dim celle As Range
Dim coll As New Collection
Dim i As Long
 
'define the range with the data
With Sheets("Sheet1")
    Set rng = Range(.Cells(7, "D"), .Cells(.Cells.Rows.Count, "D").End(xlUp))
End With
 
'create a Collection of unique items
For Each celle In rng
    On Error Resume Next
    'only strings can be added to the Collection
    coll.Add CStr(celle), CStr(celle)
Next celle
 
'add all the items in the Collection to the ListBox
For i = 0 To coll.Count
    'convert the numerical string back to numbers by using --
    Me.ListBox1.AddItem --coll(i)
Next i
 
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:

Couple things.  I need to save one line in table.  I made some edits to your sample file where the userform should fail to show.  Stating something like "There are no valid records, please add one". then Exit Sub.  It is the part in the request that refers to the cell name on Sheet1 called: OneCellUpFromD7.

The only other thing that I wanted to do was after a selection was made from the userform>combox (row), that the whole row is cut out of Sheet1 (shifting cells up) and inserted in Sheet2, right at row 7 shifting all cells down.

Think of it as, Sheet1 as a working sheet and Sheet2 is HistoricalData.  Hope this make sense.  -R-

RWayneH,

1. A message box will now show if cell D7 is blank

2. The requirement to cut and paste the row corresponding to the item selected in the ListBox doesn't make sense to me. You have asked for the ListBox to display only unique items (ie no duplicates). That implies there are duplicates. So having made a selection in the ListBox how is the macro meant to know which row containing a duplicate should be copied, pasted and deleted?

Meantime - file attached with update.

Patrick

I may have miss communicated the "no duplicates" statement above.  It was meant to mean that there will be no duplicates in column D.  I have other code that checks column D prior to a record add to the table.  If it already exists in Column D, sub exits saying that you cannot add the same record twice.  This way there is not doubt what row is the target for transfer.

Sorry bout that. -R-

Actually after reviewing your edits, because of other code dependences row 7 of Sheet1 and specifically cell D7, will never be blank.  There is a placeholder record there.  If I place a value in D7, the userform should still fail, because the cell two up from the blank (in this example it would be D6, has the cell named "OneCellUpFromD7".  I was not sure how else to keep the placeholder record there. -R-

RWayneH,

>It was meant to mean that there will be no duplicates in column D.

That changes the code for loading the ListBox! I will alter it accordingly. It makes it much simpler.

See attached file for hopefully all that you've asked for.

Patrick

Here is what I see in your example 03 file.  The "Show UserForm" commandbutton should fail if there is one record. (re-read the need to keep a placeholder record in the table or the use of the named cell "OneCellUpFromD7".  The other thing I see is that when I select a row that is in the middle of the bunch, it does not shift the cells up after it is cut over to Sheet2.  The last thing is the the userform stays open after a record is selected.  Can we close it?

A thought here that I would like another command button on the userform that would execute the transfer, just in case the user get click happy and mistakenly clicks before the actual one they want.  Like missing the vertical scroll etc.  Which happened to me when I was testing the file.  By accident of course...  

Thanks -R-

RWayneH,

I have no idea what you mean by:

The "Show UserForm" commandbutton should fail if there is one record. (re-read the need to keep a placeholder record in the table or the use of the named cell "OneCellUpFromD7"

You never mentioned a 'placeholder' in the original question. Perhaps you'd like to raise it in a new question.

I have implemented the other items you requested - they're in the attached file.

Patrick

What part of point number 3 and 4 in the original question do you not understand?  Maybe I can clarify a bit.  

The way you have it written it displays the bottom record all the time.  That should not be there.  That is the placeholder record.  As records are added to the table it pushes that placeholder down the sheet, but as they are transfer over to Sheet2 it climbs its way back up the sheet until eventually it is the only record.

If I start a down seach at cell D7, for the first blank cell, the listbox.  If there is only one record in the table (the placeholder) would not the result of a search starting at cell D7 end at cell D8? Offset back up two cells and that will equal D6, what is the cell named: OneCellUpFromD7.  Userform should fail.

There are defined cell names in the placeholder record that are used in other subroutines that I do not want to disrupt.

Does this make better sense?  -R-

RWayneH,

>The way you have it written it displays the bottom record all the time.

No idea what that means. My macro does not affect what is visible on the worksheet. It only moves data when the 'Transfer' button is pressed. Otherwise it does not change what is displayed - thus I cannot understand your comment.

>As records are added to the table it pushes that placeholder down the sheet

I don't know what you mean by a placeholder. What is a placeholder? What purpose does it serve?

>If I start a down seach at cell D7, for the first blank cell, the listbox.

Sorry but that sentence in incomplete...

>If there is only one record in the table (the placeholder) would not the result of a search starting at cell D7 end at cell D8? Offset back up two cells and that will equal D6, what is the cell named: OneCellUpFromD7.  Userform should fail.

From that I assume you mean that if cell D8 is empty then the UserForm initialisation macro should stop the initialisation process. I have now included that in the attached file.

Patrick

You are correct, the macro does not affect what is visible on the worksheet.  What I am referring to is what is displayed in the listbox.  That last record should not be in the listbox, however it will be in the worksheet.

Think of the placeholder as the record, shown on the worksheet but not in the listbox.  The purpose is for other code that is too extensive to explain...  in short there are named cells in it that are used else where in the file and in other procedures.

Your last assumptions is correct.  If D8 is empty kill the process.  However, if D8 is empty that means that D7 is not.  Row seven (the whole row is the placeholder.  I guess the only thing left now is to get whatever is in that bottom row to not show up in the listbox and we have it.

Sorry for the confusion here.  That is why I referenced offset active.cell up two, from the found blank cell.  If two cells up from that blank cell is not the cell named "OneCellUpFromD7" the userform should launch, as it returns false.

I marked  up the sample a little in the hope that it can explain further.
Thanks for hangin in there with me on this.  -R-
" D8 is empty, two up from that is a specific named cell.  "OneCellUpFromD7"  If for some reason that column D is not populated in a record.  The userform will not show, even though there are records there.

Woops! forgot to attach the file.  -R-

RWayneH,

I have now excluded the last row of data from the ListBox. That took all of a few seconds. It was all the talk of placeholders, ranges named "OneCellUpFromD7" and so on that obscured the simple requirement not to include the last row of data in the ListBox and to stop the macro when row 8 was blank.

Hopefully that completes the task - see attached file.

Patrick

Thanks! we are just about there... the last minor detail..

In your file, run Userform and delete a row, (reminder the number and/or position), then run userform again.  You will notice that the value/row previously deleted is still in the listbox, even though it was transfered to Sheet2 and no longer on Sheet1.  Like it is not refreshing the listbox or something.  It is not until I quit and return a second time to the listbox that it is gone.

This may seem petty, but will confuse users.
I believe with that fixed we are golden.   Thanks. -R-

Bravo!  EXCELent.  Exactly what I was looking for.  Thanks a million and thanks for your patience during this reqest.  -R-

Sorry.. probably should have used different terms then placeholder...  It would have been less confusing.

Thanks again.  -R-