Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering Data from ListBox to Worksheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering Data from ListBox to Worksheet
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering Data from ListBox to Worksheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering Data from ListBox to Worksheet
"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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering Data from ListBox to Worksheet
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering Data from ListBox to Worksheet
"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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering Data from ListBox to Worksheet
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering Data from ListBox to Worksheet
"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 â?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 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 ListBox1s 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 100s 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 Userform1.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 Dicks 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering Data from ListBox to Worksheet
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¢?" RowSourcef¢?, 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 canf¢?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 canf¢?Tt see the error. With keepITcoolf¢?Ts suggestion I develop the macro/routine f¢?oTransferToSheetf¢?, and add the two Private subf¢?Ts f¢?oPrivate Sub UserForm_Click()f¢?, and f¢?oPrivate Sub UserForm_Initialize()f¢?, and add two items with; With ListBox1 .AddItem f¢?oOJf¢?, .AddItem f¢?oBeerf¢?, End With Now when I click on my f¢?oAdd Item(s)f¢?, button it stops with f¢?oPermission deniedf¢?,. 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 canf¢?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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering Data from ListBox to Worksheet
"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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering Data from ListBox to Worksheet
Dick's address is in the header:
"Dick Kusleika" ps. When I turned off smart quotes in MSWord, it fixed the apostrophe problem, too. Your apostrophes still look funny. Ronbo wrote: <<snipped 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 -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering Data from ListBox to Worksheet
"Dave Peterson" wrote: Dick's address is in the header: "Dick Kusleika" ps. When I turned off smart quotes in MSWord, it fixed the apostrophe problem, too. Your apostrophes still look funny. Ronbo wrote: <<snipped 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 -- Dave Peterson Dave - Thanks |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering Data from ListBox to Worksheet
Ronbo
Well, Dave was right. I thought the Value property of the Listbox would give you an error in a mulitselect listbox, but it's just Null. First, Sheet1 is the CodeName of the sheet, not the name. Sheet1's name is RESULTS. Sheet2's name is "Sheet1" Look at the Project Explorer (Cntl+R) to see how that works. Here's the code that you need Private Sub OKButton_Click() Dim i As Long For i = 0 To Me.ListBox1.ListCount - 1 If Me.ListBox1.Selected(i) Then Worksheets("Sheet1").Range("B43").End(xlUp) _ .Offset(1, 0).Value = Me.ListBox1.List(i) End If Next i Unload Me End Sub That looks like a very useful spreadsheet you're building. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com Ronbo wrote: "Dave Peterson" wrote: Dick's address is in the header: "Dick Kusleika" ps. When I turned off smart quotes in MSWord, it fixed the apostrophe problem, too. Your apostrophes still look funny. Ronbo wrote: <<snipped 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 -- Dave Peterson Dave - Thanks |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering Data from ListBox to Worksheet
"Dick Kusleika" wrote: Ronbo Well, Dave was right. I thought the Value property of the Listbox would give you an error in a mulitselect listbox, but it's just Null. First, Sheet1 is the CodeName of the sheet, not the name. Sheet1's name is RESULTS. Sheet2's name is "Sheet1" Look at the Project Explorer (Cntl+R) to see how that works. Here's the code that you need Private Sub OKButton_Click() Dim i As Long For i = 0 To Me.ListBox1.ListCount - 1 If Me.ListBox1.Selected(i) Then Worksheets("Sheet1").Range("B43").End(xlUp) _ .Offset(1, 0).Value = Me.ListBox1.List(i) End If Next i Unload Me End Sub That looks like a very useful spreadsheet you're building. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com Ronbo wrote: "Dave Peterson" wrote: Dick's address is in the header: "Dick Kusleika" ps. When I turned off smart quotes in MSWord, it fixed the apostrophe problem, too. Your apostrophes still look funny. Ronbo wrote: <<snipped 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 -- Dave Peterson Dave - Thanks Dick - I just got your last response. I was looking for an e-mail reply and just came back here and found you last response. It WORKS PERFECT!!! Thanks a lot for your help, time, patiance and expertise. As for the sheet name, I named it sheet1 as to not make it more confusing by changing names through this discussion. Ronbo P.S. Thanks a lot to Dave P. also |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transfering of data from 1 worksheet to another | Excel Discussion (Misc queries) | |||
Transfering data from worksheet to another | Excel Discussion (Misc queries) | |||
Transfering a row of data to a template word or worksheet | Excel Discussion (Misc queries) | |||
Transfering data from 1 worksheet to another | Excel Discussion (Misc queries) | |||
Transfering data from one worksheet to another Worksheet | Excel Programming |