I have created a UserForm with a ListBox. If the user clicks on the macro
button €œAdd to List€� the ListBox is opened with all of the options, and now
you can simply click on the item(s) you want to add to the worksheet sheet in
column B, starting at B8. I have it all set up to the point of transferring
the data (or item(s) selected) to the worksheet. i.e The ListBox of "item
options" comes up perfectly, I can select (highlight) the items I want to add
to the list on the worksheet€¦ but I dont know how to transfer the selected
(highlighted) items to the worksheet in the next open cell, starting with B8
by clicking on the OK command button.

Any help with the code needed for the OK button to transfer the data would
be greatly appreciated.

P.S. Thanks to Dick Kusleika for the previous suggestions, but neither
solved my problem.

Option Explicit

Sub TransferToSheet()
Dim i&, n&, vaItems As Variant

With Me.ListBox1
ReDim vaItems(1 To .ListCount)

For i = 0 To .ListCount - 1
If .Selected(i) Then
n = n + 1
vaItems(n) = .List(i)
End If
Next
End With

'remove the unused items from the array
ReDim Preserve vaItems(1 To n)
'if we're going to store this in a "vertical" range,
'we must use transpose
Range("a1").Resize(n, 1) = Application.Transpose(vaItems)

End Sub

Private Sub UserForm_Click()
TransferToSheet
End Sub


Private Sub UserForm_Initialize()
add some dummy data
Me.ListBox1.MultiSelect = fmMultiSelectExtended
Dim i&
For i = 0 To 99
Me.ListBox1.AddItem "item " & Format(i, "00")
Next

End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam

?B?Um9uYm8=?= wrote in message
:

I have created a UserForm with a ListBox. If the user clicks on the
macro button €œAdd to List€� the ListBox is opened with all of the
options, and now you can simply click on the item(s) you want to add
to the worksheet sheet in column B, starting at B8. I have it all set
up to the point of transferring the data (or item(s) selected) to the
worksheet. i.e The ListBox of "item options" comes up perfectly, I
can select (highlight) the items I want to add to the list on the
worksheet€¦ but I dont know how to transfer the selected
(highlighted) items to the worksheet in the next open cell, starting
with B8 by clicking on the OK command button.

Any help with the code needed for the OK button to transfer the data
would be greatly appreciated.

P.S. Thanks to Dick Kusleika for the previous suggestions, but
neither solved my problem.

Ronbo

P.S. Thanks to Dick Kusleika for the previous suggestions, but neither
solved my problem.


Don't start a new thread, reply to my message and tell me WHY it doesn't
work. At the risk of being boastful, I'm quite sure that my answer was
adequate based on the information you provided.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Dick Kusleika" wrote:

Ronbo

P.S. Thanks to Dick Kusleika for the previous suggestions, but neither
solved my problem.

Thanks a lot to Dick and keepITcool for your help. As you can see I struggle with VBA but this one is ridiculous. I am still not getting anywhere other than creating the UserForm named Food List with the ListBox (ListBox1) and two CommandButtons (OKButton) and (CancelButton). The data in the FoodList is created by using a named range in the €œListBox €“ RowSource€�

With a Macro Button €œAdd Item(s)€� (code - FoodList.Show) on the worksheet it
brings up the list and I can highlight the item(s) I want to add. With the
Cancel Button (code - Unload FoodList) it closes the UserForm.

But I cant get my OK button to transfer the selected items to the worksheet
in column B.

With Dicks suggestion I put the code in the €œPrivate Sub OKButton_Click()€�
and nothing happens. Also it looks like the code is finding the next open
cell going up column B. If so this will not work because B45 has text. The
open cells to transfer data to, are B10..B44. As for the link, there are two
errors in the code. One in the sTitle line and the other in the If line and I
cant see the error.

With keepITcools suggestion I develop the macro/routine €œTransferToSheet€�
and add the two Private subs €œPrivate Sub UserForm_Click()€� and €œPrivate
Sub UserForm_Initialize()€� and add two items with;

With ListBox1
.AddItem €œOJ€�
.AddItem €œBeer€�
End With

Now when I click on my €œAdd Item(s)€� button it stops with €œPermission
denied€�. Also I do not understand €œInitialize()€� at all, and seems like I
would have to add items to the routine every time a wanted to add something,
rather than just using the range in €œRowSource.??

So I am still stuck. Any additional help will be truly appreciated.

Again thanks for you help, patience, and expertise.

P.S. Dick, I am sure you know how to make this work, I am the one that
cant. As for using the thread, this is one of my first times on the new
board and using the old board, if you posted a thread it never seemed to get
read. Hopefully this will find its way to you and keepITcool.


Don't start a new thread, reply to my message and tell me WHY it doesn't
work. At the risk of being boastful, I'm quite sure that my answer was
adequate based on the information you provided.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Ronbo

I created a userform with a listbox (ListBox1) and a commandbutton
(OKButton). Here is the click event for the commandbutton

Private Sub OKButton_Click()

If Me.ListBox1.ListIndex -1 Then
Sheet1.Range("B44").End(xlUp).Offset(1, 0).Value _
= Me.ListBox1.Value
End If

End Sub

If nothing happened when with the last bit of code I gave you, then it's
likely that your commandbutton is not named OKButton. With the userform
open, double click on the commandbutton and the VBE with put the Sub and End
Sub lines in there for you. Something should have happened even if it was
wrong.

You can't use the AddItem method and the RowSource property. It's one or
the other. That's why you got errors from KIC's code.

Make sure your controls are named the same as mine (or change the code to
reflect your names) and this code should work.

Post back if it doesn't or if you have further questions.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Ronbo" wrote in message
...


"Dick Kusleika" wrote:

Ronbo

P.S. Thanks to Dick Kusleika for the previous suggestions, but
neither
solved my problem.

Thanks a lot to Dick and keepITcool for your help. As you can see I
struggle with VBA but this one is ridiculous. I am still not getting
anywhere other than creating the UserForm named Food List with the ListBox
(ListBox1) and two CommandButtons (OKButton) and (CancelButton). The data
in the FoodList is created by using a named range in the ?oListBox ?"
RowSource?�

With a Macro Button ?oAdd Item(s)?� (code - FoodList.Show) on the
worksheet it
brings up the list and I can highlight the item(s) I want to add. With
the
Cancel Button (code - Unload FoodList) it closes the UserForm.

But I can?Tt get my OK button to transfer the selected items to the
worksheet
in column B.

With Dicks suggestion I put the code in the ?oPrivate Sub
OKButton_Click()?�
and nothing happens. Also it looks like the code is finding the next open
cell going up column B. If so this will not work because B45 has text.
The
open cells to transfer data to, are B10..B44. As for the link, there are
two
errors in the code. One in the sTitle line and the other in the If line
and I
can?Tt see the error.

With keepITcool?Ts suggestion I develop the macro/routine
?oTransferToSheet?�
and add the two Private sub?Ts ?oPrivate Sub UserForm_Click()?� and
?oPrivate
Sub UserForm_Initialize()?� and add two items with;

With ListBox1
.AddItem ?oOJ?�
.AddItem ?oBeer?�
End With

Now when I click on my ?oAdd Item(s)?� button it stops with
?oPermission
denied?�. Also I do not understand ?oInitialize()?� at all, and seems
like I
would have to add items to the routine every time a wanted to add
something,
rather than just using the range in ?oRowSource.??

So I am still stuck. Any additional help will be truly appreciated.

Again thanks for you help, patience, and expertise.

P.S. Dick, I am sure you know how to make this work, I am the one that
can?Tt. As for using the thread, this is one of my first times on the
new
board and using the old board, if you posted a thread it never seemed to
get
read. Hopefully this will find its way to you and keepITcool.


Don't start a new thread, reply to my message and tell me WHY it doesn't
work. At the risk of being boastful, I'm quite sure that my answer was
adequate based on the information you provided.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Dick Kusleika" wrote:

Ronbo

I created a userform with a listbox (ListBox1) and a commandbutton
(OKButton). Here is the click event for the commandbutton

Private Sub OKButton_Click()

If Me.ListBox1.ListIndex -1 Then
Sheet1.Range("B44").End(xlUp).Offset(1, 0).Value _
= Me.ListBox1.Value
End If

End Sub

If nothing happened when with the last bit of code I gave you, then it's
likely that your commandbutton is not named OKButton. With the userform
open, double click on the commandbutton and the VBE with put the Sub and End
Sub lines in there for you. Something should have happened even if it was
wrong.

You can't use the AddItem method and the RowSource property. It's one or
the other. That's why you got errors from KIC's code.

Make sure your controls are named the same as mine (or change the code to
reflect your names) and this code should work.

Post back if it doesn't or if you have further questions.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Ronbo" wrote in message
...


"Dick Kusleika" wrote:

Ronbo

P.S. Thanks to Dick Kusleika for the previous suggestions, but
neither
solved my problem.

Thanks a lot to Dick and keepITcool for your help. As you can see I
struggle with VBA but this one is ridiculous. I am still not getting
anywhere other than creating the UserForm named Food List with the ListBox
(ListBox1) and two CommandButtons (OKButton) and (CancelButton). The data
in the FoodList is created by using a named range in the â?oListBox â?"
RowSourceâ?�

With a Macro Button â?oAdd Item(s)â?� (code - FoodList.Show) on the
worksheet it
brings up the list and I can highlight the item(s) I want to add. With
the
Cancel Button (code - Unload FoodList) it closes the UserForm.

But I canâ?Tt get my OK button to transfer the selected items to the
worksheet
in column B.

With Dicks suggestion I put the code in the â?oPrivate Sub
OKButton_Click()â?�
and nothing happens. Also it looks like the code is finding the next open
cell going up column B. If so this will not work because B45 has text.
The
open cells to transfer data to, are B10..B44. As for the link, there are
two
errors in the code. One in the sTitle line and the other in the If line
and I
canâ?Tt see the error.

With keepITcoolâ?Ts suggestion I develop the macro/routine
â?oTransferToSheetâ?�
and add the two Private subâ?Ts â?oPrivate Sub UserForm_Click()â?� and
â?oPrivate
Sub UserForm_Initialize()â?� and add two items with;

With ListBox1
.AddItem â?oOJâ?�
.AddItem â?oBeerâ?�
End With

Now when I click on my â?oAdd Item(s)â?� button it stops with
â?oPermission
deniedâ?�. Also I do not understand â?oInitialize()â?� at all, and seems
like I
would have to add items to the routine every time a wanted to add
something,
rather than just using the range in â?oRowSource.??

So I am still stuck. Any additional help will be truly appreciated.

Again thanks for you help, patience, and expertise.

P.S. Dick, I am sure you know how to make this work, I am the one that
canâ?Tt. As for using the thread, this is one of my first times on the
new
board and using the old board, if you posted a thread it never seemed to
get
read. Hopefully this will find its way to you and keepITcool.


Don't start a new thread, reply to my message and tell me WHY it doesn't
work. At the risk of being boastful, I'm quite sure that my answer was
adequate based on the information you provided.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com





Dick €“

Guess what? It still does not work!!! I cant believe it. When I highlight
a couple of items and click on the OK button absolutely nothing happens
(except the button pushes in). I copied your code with copy and paste to my
OKCommandButton code. The following is my Private Sub OKButton_Click()
copied and pasted;

Private Sub OKButton_Click()

If Me.ListBox1.ListIndex -1 Then
Sheet1.Range("B44").End(xlUp).Offset(1, 0).Value _
= Me.ListBox1.Value
End If

End Sub

As you can see my CommandButton is named OKButton and is is assigned to the
OK Button on the UserForm. My worksheet is named Sheet1.

Is it possible that I need to change some of the properties for the OK
commandbutton?

Any other ideas?

Again, thanks for your help.

P.S. Can I e-mail it to you to take a look at. Its very simple and straight
forward.

I think Dick missed the fact that you wanted to choose multiple items from that
listbox.

And I bet you either set the .rowsource for that listbox (manually when
designing the form) or via code:

Me.ListBox1.RowSource _
= Worksheets("sheet1").Range("a1:a10").Address(exter nal:=True)

For testing purposes, add this line right above your code:

with me.listbox1
.RowSource = ""
'then your code
.additem "beer"
.additem "chips"
end with

Then test it with KeepItCool's suggestion for dumping all the selected items.

ps.

Your posts are difficult to read with those funny characters in them. Are you
composing in MSWord, then copying to the web page.

If yes, you may want to turn off some of the autocorrections within word --
smart quotes really makes it difficult to read/test your code.



Ronbo wrote:

"Dick Kusleika" wrote:

Ronbo

I created a userform with a listbox (ListBox1) and a commandbutton
(OKButton). Here is the click event for the commandbutton

Private Sub OKButton_Click()

If Me.ListBox1.ListIndex -1 Then
Sheet1.Range("B44").End(xlUp).Offset(1, 0).Value _
= Me.ListBox1.Value
End If

End Sub

If nothing happened when with the last bit of code I gave you, then it's
likely that your commandbutton is not named OKButton. With the userform
open, double click on the commandbutton and the VBE with put the Sub and End
Sub lines in there for you. Something should have happened even if it was
wrong.

You can't use the AddItem method and the RowSource property. It's one or
the other. That's why you got errors from KIC's code.

Make sure your controls are named the same as mine (or change the code to
reflect your names) and this code should work.

Post back if it doesn't or if you have further questions.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Ronbo" wrote in message
...


"Dick Kusleika" wrote:

Ronbo

P.S. Thanks to Dick Kusleika for the previous suggestions, but
neither
solved my problem.

Thanks a lot to Dick and keepITcool for your help. As you can see I
struggle with VBA but this one is ridiculous. I am still not getting
anywhere other than creating the UserForm named Food List with the ListBox
(ListBox1) and two CommandButtons (OKButton) and (CancelButton). The data
in the FoodList is created by using a named range in the â?oListBox â?"
RowSourceâ?�

With a Macro Button â?oAdd Item(s)â?� (code - FoodList.Show) on the
worksheet it
brings up the list and I can highlight the item(s) I want to add. With
the
Cancel Button (code - Unload FoodList) it closes the UserForm.

But I canâ?Tt get my OK button to transfer the selected items to the
worksheet
in column B.

With Dicks suggestion I put the code in the â?oPrivate Sub
OKButton_Click()â?�
and nothing happens. Also it looks like the code is finding the next open
cell going up column B. If so this will not work because B45 has text.
The
open cells to transfer data to, are B10..B44. As for the link, there are
two
errors in the code. One in the sTitle line and the other in the If line
and I
canâ?Tt see the error.

With keepITcoolâ?Ts suggestion I develop the macro/routine
â?oTransferToSheetâ?�
and add the two Private subâ?Ts â?oPrivate Sub UserForm_Click()â?� and
â?oPrivate
Sub UserForm_Initialize()â?� and add two items with;

With ListBox1
.AddItem â?oOJâ?�
.AddItem â?oBeerâ?�
End With

Now when I click on my â?oAdd Item(s)â?� button it stops with
â?oPermission
deniedâ?�. Also I do not understand â?oInitialize()â?� at all, and seems
like I
would have to add items to the routine every time a wanted to add
something,
rather than just using the range in â?oRowSource.??

So I am still stuck. Any additional help will be truly appreciated.

Again thanks for you help, patience, and expertise.

P.S. Dick, I am sure you know how to make this work, I am the one that
canâ?Tt. As for using the thread, this is one of my first times on the
new
board and using the old board, if you posted a thread it never seemed to
get
read. Hopefully this will find its way to you and keepITcool.


Don't start a new thread, reply to my message and tell me WHY it doesn't
work. At the risk of being boastful, I'm quite sure that my answer was
adequate based on the information you provided.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com





Dick €“

Guess what? It still does not work!!! I cant believe it. When I highlight
a couple of items and click on the OK button absolutely nothing happens
(except the button pushes in). I copied your code with copy and paste to my
OKCommandButton code. The following is my Private Sub OKButton_Click()
copied and pasted;

Private Sub OKButton_Click()

If Me.ListBox1.ListIndex -1 Then
Sheet1.Range("B44").End(xlUp).Offset(1, 0).Value _
= Me.ListBox1.Value
End If

End Sub

As you can see my CommandButton is named OKButton and is is assigned to the
OK Button on the UserForm. My worksheet is named Sheet1.

Is it possible that I need to change some of the properties for the OK
commandbutton?

Any other ideas?

Again, thanks for your help.

P.S. Can I e-mail it to you to take a look at. Its very simple and straight
forward.

--

Dave Peterson