URGENT HELP NEEDED (SIMPLE LIST AND COMBO BOX)

[amarj]

Hi All,

I have been tasked to set up an automated system (VB) which will report into Excel. I am struggling to create a workable userform that utilises combo, list box and inputs the data selected into an excel spreadsheet. I have been struggling with this for weeks.

Does anyone have a simple code that will populate a list box (user defined) and a combo box with simple instructions on how to do it (Preferably step by step - Serious novice)

If anyone does, please help.

Thank You

 

[Oorang]

All you really need to do is use the .additem method. I tend to embed it in my worksheet activate or form initiatlization event.
Here is an example.

Private Sub UserForm_Initialize()
'Declare the combobox object
Dim cmbx1 As MSForms.ComboBox
'Set the combobox object to the combo box of your choice
Set cmbx1 = Me.ComboBox1
'Clear old entries:
Do Until cmbx1.ListCount = 0
cmbx1.RemoveItem (1)
Loop
'Add new items
cmbx1.AddItem "Item 1"
cmbx1.AddItem "Item 2"
cmbx1.AddItem "Item 3"
End Sub

 

[amarj]

help me please!!!! - urgently required - vb novice

Thanks Oorang

The formulae works properly.

Do you know how to transfer the data selected in the combo box into a spreadsheet?

Is there a formulae that automatically selects the next line, so the data is not inputted on the same line?


how do you delete the selections made?

 

[Oorang]

This will place the value of the combobox in the cell whenever you change the value of the box. Effectivly linking the two. Not sure what you mean by your second question?
<hr>
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()
    <SPAN style="color:#007F00">'Declare the combobox object</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> cmbx1 <SPAN style="color:#00007F">As</SPAN> MSForms.ComboBox
    <SPAN style="color:#007F00">'Set the combobox object to the combo box of your choice</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> cmbx1 = Me.ComboBox1
    <SPAN style="color:#007F00">'Clear old entries:</SPAN>
    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> cmbx1.ListCount = 0
    cmbx1.RemoveItem (1)
    <SPAN style="color:#00007F">Loop</SPAN>
    <SPAN style="color:#007F00">'Add new items</SPAN>
    cmbx1.AddItem "Item 1"
    cmbx1.AddItem "Item 2"
    cmbx1.AddItem "Item 3"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ComboBox1_Change()
    <SPAN style="color:#00007F">Dim</SPAN> cmbx1 <SPAN style="color:#00007F">As</SPAN> MSForms.ComboBox
    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Excel.Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Excel.Range
    <SPAN style="color:#007F00">'Set the combobox object to the combo box of your choice</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> cmbx1 = Me.ComboBox1
    <SPAN style="color:#007F00">'Set the worksheet to name of your choice</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> ws = ThisWorkbook.Sheets("Sheet1")
    <SPAN style="color:#007F00">'Set the range to any cell in the sheet specified.</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rng = ws.Cells(1, 1)
    rng.Value = cmbx1.Value
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[amarj]

HELP -

Thanks - That is brilliant.

Question 2:

When the user selects the input from the combo box list, this automatically goes to range specified.

Im not too sure how to check if any cells in row have comments entered? and if yes for the next row to be selected in this event.

Is this possible?

 

[amarj]

HELP - HELP - HELP

Thanks - That is brilliant.

Question 2:

When the user selects the input from the combo box list, this automatically goes to range specified.

Im not too sure how to check if any cells in row have comments entered? and if yes for the next row to be selected in this event.

Is this possible?

 

[Oorang]

Ahhh now I get it
<hr>
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()
    <SPAN style="color:#007F00">'Declare the combobox object</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> cmbx1 <SPAN style="color:#00007F">As</SPAN> MSForms.ComboBox
    <SPAN style="color:#007F00">'Set the combobox object to the combo box of your choice</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> cmbx1 = Me.ComboBox1
    <SPAN style="color:#007F00">'Clear old entries:</SPAN>
    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> cmbx1.ListCount = 0
    cmbx1.RemoveItem (1)
    <SPAN style="color:#00007F">Loop</SPAN>
    <SPAN style="color:#007F00">'Add new items</SPAN>
    cmbx1.AddItem "Item 1"
    cmbx1.AddItem "Item 2"
    cmbx1.AddItem "Item 3"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ComboBox1_Change()
    <SPAN style="color:#00007F">Dim</SPAN> cmbx1 <SPAN style="color:#00007F">As</SPAN> MSForms.ComboBox
    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Excel.Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> rngLookIn <SPAN style="color:#00007F">As</SPAN> Excel.Range
    <SPAN style="color:#00007F">Dim</SPAN> rngCell <SPAN style="color:#00007F">As</SPAN> Excel.Range
    <SPAN style="color:#007F00">'Set the combobox object to the combo box of your choice</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> cmbx1 = Me.ComboBox1
    <SPAN style="color:#007F00">'Set the worksheet to name of your choice</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> ws = ThisWorkbook.Sheets("Sheet1")
    <SPAN style="color:#007F00">'Set the range to the column you want looked at.</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rngLookIn = ws.Columns(5).Cells
    <SPAN style="color:#007F00">'Loop through range until you encouter an empty cell.</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rngCell <SPAN style="color:#00007F">In</SPAN> rngLookIn
        <SPAN style="color:#00007F">If</SPAN> VBA.LenB(rngCell.Value) = 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> rngCell
    <SPAN style="color:#007F00">'Plug your value into the empty cell you found.</SPAN>
    rngCell.Value = cmbx1.Value
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>