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.

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.

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.

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.

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.

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.