VBA Click Command Button
 

I built a userform where the user selects an ObjectButton and in a listbox
(depending on whats selected) A list of data shows up. My main Goal is to
get all of that data to goto the first available cell of a certain column.
Currently (see code below) all I can get it to do is whatever specific
text/data out of the listbox data is selected repeats in the given cells. I
am new to all of this, and would appreciate any help or easier way to
accomplishing. Thank You in advance for anyone that responds.

VBA Click Command Button
 

Sorry here is the code I have.

Private Sub ListBox1_Click()

End Sub

Private Sub obMonths_Click()
ListBox1.RowSource = "Sheet1!Months"
End Sub
Private Sub obCars_Click()
ListBox1.RowSource = "Sheet1!Cars"
End Sub
Private Sub obColors_Click()
ListBox1.RowSource = "Sheet1!Colors"
End Sub

Private Sub OKButton_Click()
Range("F11:F200") = ListBox1.Text
Unload Me
End Sub


"Benz" wrote:

I built a userform where the user selects an ObjectButton and in a listbox
(depending on whats selected) A list of data shows up. My main Goal is to
get all of that data to goto the first available cell of a certain column.
Currently (see code below) all I can get it to do is whatever specific
text/data out of the listbox data is selected repeats in the given cells. I
am new to all of this, and would appreciate any help or easier way to
accomplishing. Thank You in advance for anyone that responds.

VBA Click Command Button
 

You need to replace

Range("F11:F200") = ListBox1.Text
with
Range("F11:F200") = ListBox1.List

If your range has more rows than list items you will get #N/A in those cells

ChasAA

"Benz" wrote:

Sorry here is the code I have.

Private Sub ListBox1_Click()

End Sub

Private Sub obMonths_Click()
ListBox1.RowSource = "Sheet1!Months"
End Sub
Private Sub obCars_Click()
ListBox1.RowSource = "Sheet1!Cars"
End Sub
Private Sub obColors_Click()
ListBox1.RowSource = "Sheet1!Colors"
End Sub

Private Sub OKButton_Click()
Range("F11:F200") = ListBox1.Text
Unload Me
End Sub


"Benz" wrote:

I built a userform where the user selects an ObjectButton and in a listbox
(depending on whats selected) A list of data shows up. My main Goal is to
get all of that data to goto the first available cell of a certain column.
Currently (see code below) all I can get it to do is whatever specific
text/data out of the listbox data is selected repeats in the given cells. I
am new to all of this, and would appreciate any help or easier way to
accomplishing. Thank You in advance for anyone that responds.

VBA Click Command Button
 

What would I put in, if I wanted it to put the info in the first available
cell of Column F everytime?

"ChasAA" wrote:

You need to replace

Range("F11:F200") = ListBox1.Text
with
Range("F11:F200") = ListBox1.List

If your range has more rows than list items you will get #N/A in those cells

ChasAA

"Benz" wrote:

Sorry here is the code I have.

Private Sub ListBox1_Click()

End Sub

Private Sub obMonths_Click()
ListBox1.RowSource = "Sheet1!Months"
End Sub
Private Sub obCars_Click()
ListBox1.RowSource = "Sheet1!Cars"
End Sub
Private Sub obColors_Click()
ListBox1.RowSource = "Sheet1!Colors"
End Sub

Private Sub OKButton_Click()
Range("F11:F200") = ListBox1.Text
Unload Me
End Sub


"Benz" wrote:

I built a userform where the user selects an ObjectButton and in a listbox
(depending on whats selected) A list of data shows up. My main Goal is to
get all of that data to goto the first available cell of a certain column.
Currently (see code below) all I can get it to do is whatever specific
text/data out of the listbox data is selected repeats in the given cells. I
am new to all of this, and would appreciate any help or easier way to
accomplishing. Thank You in advance for anyone that responds.

VBA Click Command Button
 

Include this in your Option Button Code

Private Sub CommandButton1_Click()
Cells(1, 6).End(xlDown).Select ' select the last non blank cell
downfrom cell F1
Selection.Offset(1, 0).Select ' select netx cell down ie next blank
cell
nbroflistitems = ListBox1.ListCount ' count nbr of list items
Range(Selection, Selection.Offset(nbroflistitems - 1)).Select ' select that
many cells
Selection = ListBox1.List ' fill that range with list items
End Sub

ChasAA

"Benz" wrote:

What would I put in, if I wanted it to put the info in the first available
cell of Column F everytime?

"ChasAA" wrote:

You need to replace

Range("F11:F200") = ListBox1.Text
with
Range("F11:F200") = ListBox1.List

If your range has more rows than list items you will get #N/A in those cells

ChasAA

"Benz" wrote:

Sorry here is the code I have.

Private Sub ListBox1_Click()

End Sub

Private Sub obMonths_Click()
ListBox1.RowSource = "Sheet1!Months"
End Sub
Private Sub obCars_Click()
ListBox1.RowSource = "Sheet1!Cars"
End Sub
Private Sub obColors_Click()
ListBox1.RowSource = "Sheet1!Colors"
End Sub

Private Sub OKButton_Click()
Range("F11:F200") = ListBox1.Text
Unload Me
End Sub


"Benz" wrote:

I built a userform where the user selects an ObjectButton and in a listbox
(depending on whats selected) A list of data shows up. My main Goal is to
get all of that data to goto the first available cell of a certain column.
Currently (see code below) all I can get it to do is whatever specific
text/data out of the listbox data is selected repeats in the given cells. I
am new to all of this, and would appreciate any help or easier way to
accomplishing. Thank You in advance for anyone that responds.

VBA Click Command Button
 

Thank You, you are whiz. Have a great weekend!!!

"ChasAA" wrote:

Include this in your Option Button Code

Private Sub CommandButton1_Click()
Cells(1, 6).End(xlDown).Select ' select the last non blank cell
downfrom cell F1
Selection.Offset(1, 0).Select ' select netx cell down ie next blank
cell
nbroflistitems = ListBox1.ListCount ' count nbr of list items
Range(Selection, Selection.Offset(nbroflistitems - 1)).Select ' select that
many cells
Selection = ListBox1.List ' fill that range with list items
End Sub

ChasAA

"Benz" wrote:

What would I put in, if I wanted it to put the info in the first available
cell of Column F everytime?

"ChasAA" wrote:

You need to replace

Range("F11:F200") = ListBox1.Text
with
Range("F11:F200") = ListBox1.List

If your range has more rows than list items you will get #N/A in those cells

ChasAA

"Benz" wrote:

Sorry here is the code I have.

Private Sub ListBox1_Click()

End Sub

Private Sub obMonths_Click()
ListBox1.RowSource = "Sheet1!Months"
End Sub
Private Sub obCars_Click()
ListBox1.RowSource = "Sheet1!Cars"
End Sub
Private Sub obColors_Click()
ListBox1.RowSource = "Sheet1!Colors"
End Sub

Private Sub OKButton_Click()
Range("F11:F200") = ListBox1.Text
Unload Me
End Sub


"Benz" wrote:

I built a userform where the user selects an ObjectButton and in a listbox
(depending on whats selected) A list of data shows up. My main Goal is to
get all of that data to goto the first available cell of a certain column.
Currently (see code below) all I can get it to do is whatever specific
text/data out of the listbox data is selected repeats in the given cells. I
am new to all of this, and would appreciate any help or easier way to
accomplishing. Thank You in advance for anyone that responds.