"Dick Kusleika" wrote:

Ronbo

Email a copy of the workbook to me.

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

"Ronbo" wrote in message
...


"Dave Peterson" wrote:

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
Ãf¢?oListBox Ãf¢?"
RowSourceÃf¢?Ã,Â�

With a Macro Button Ãf¢?oAdd Item(s)Ãf¢?Ã,Â� (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Ãf¢?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 Ãf¢?oPrivate Sub
OKButton_Click()Ãf¢?Ã,Â�
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Ãf¢?Tt see the error.

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

With ListBox1
.AddItem Ãf¢?oOJÃf¢?Ã,Â�
.AddItem Ãf¢?oBeerÃf¢?Ã,Â�
End With

Now when I click on my Ãf¢?oAdd Item(s)Ãf¢?Ã,Â� button it stops
with
Ãf¢?oPermission
deniedÃf¢?Ã,Â�. Also I do not understand
Ãf¢?oInitialize()Ãf¢?Ã,Â� 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 Ãf¢?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Ãf¢?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 ââ,¬â?o

Guess what? It still does not work!!! I canââ,¬â"¢t 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
Dave â?"

Thanks for the response. First of all, you are correct on using Word and
copying it to the web page. Sorry about how bad it turned out. I have
turned off the smart Quotes and hope this will be better. Secondly, I did
set the RowSource when I designed the UserForm using the ListBox
properties.

What I did is created a list on Sheet2 and named the range FoodList and
then
simply put FoodList in ListBox1â?Ts RowSource. Very simple and easy to
change
as the list changes. I would really prefer not to have to go into the
code
and have to .additem for 100â?Ts of items and then go back in and change
it
each time I make a change to my list. With the way I am doing it now, all
I
have to do is name the range for the initial list and when I make changes,
is
resize the range. Hopefully I can continue with this strategy.

But for the last two days I have been stuck trying to get highlighted
items
( and again you are correct that it is multiple items most of the time)
from
the ListBox to the worksheet.

This really seems all to simple. Here is what I have done and where I am
stuck at and need help with code.

1 open new workbook
2 on sheet1 enter 1 to 25 in A1:a25
3 name range sheet1!a1:a25 â?" FoodList
4 on sheet2 create a macro Button1 and add the code â?oUserform1.Showâ?�
5 create UserForm1 with ListBox1
6 â?" in ListBox1 properties enter FoodList in RowSource and change
MultiSelect to 1

Now click on Button1 and boom you have a nice ListBox with items 1 through
25 and you can highlight all you want.

Now how do I get the highlighted items to fill in the next empty cells on
sheet2 B5..B44 using a CommandButton on UserForm1?

When I use Dickâ?Ts last code, in a new workbook, it still does not do
anything when I click the OK CommandButton

Thanks a lot to all, for all of the help and time.






Dick €“

What is your e-mail address? If you want you can e-mail it to me at
ronbowman32@aol.
Its 10:30 CT. I will be back in about two hours and will e-mail it then if
I have an address.

Thanks