Form, listbox multiselect = yes problem

benjansen · 07-02-2008, 01:03 AM

Dear all,

I have the following problem. I created a form in Excel with a listbox, multiline select. When opening the form this executes a loop for writing the selected entries of the listbox to a range of cells in sheet 1

Dim lItem As Long
For lItem = 0 To lsboxMainframe.ListCount - 1
If lsboxMainframe.Selected(lItem) = True Then
Sheet1.Range("B60").End(xlUp)(2, 1) = lsboxMainframe.List(lItem)
lsboxMainframe.Selected(lItem) = False
End If
The problem:
When I open the document it should show the selected items of the listbox......so it should read the written items in the cells....

Well....how the h*ll should I do this.....I haven't got any clue....I did this for a textbox...but this is simple....on opening the form...

Worksheets("Detailsheet").Range("E15") = DRPC.txtNamebsd.Value

Can anyone help me out here....??????

Thanx for the effort
Ben

David M58 · 07-02-2008, 11:30 AM

Here are some suggestions that you may find helpful.

To fill the ListBox with values and open the form when the workbook is opened, you can place the following code in the ThisWorkbook object:

Code:

Private Sub Workbook_Open()
    UserForm1.lsboxMainframe.RowSource = "Sheet2!A2:A50"
    UserForm1.Show
End Sub

If you want CommandButton1 on Sheet1 to open the form with values, you can place similar code in the Sheet1 object:

Code:

Private Sub CommandButton1_Click()
    UserForm1.lsboxMainframe.RowSource = "Sheet2!A2:A50"
    UserForm1.Show
End Sub

To transfer the selected items to Sheet1 when the Update button is clicked, you can use the code you have in UserForm1:

Code:

Private Sub CommandButton2_Click()
    Dim lItem As Long
    For lItem = 0 To lsboxMainframe.ListCount - 1
        If lsboxMainframe.Selected(lItem) = True Then
            Sheet1.Range("A65536").End(xlUp)(2, 1) = lsboxMainframe.List(lItem)
            lsboxMainframe.Selected(lItem) = False
        End If
    Next lItem
End Sub

To make the form close when you click the Close button, you can use the following code in UserForm1:

Code:

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Modify as necessary.

benjansen · 07-03-2008, 12:16 AM

David,

Thanx for the fast reply....problem is when I close the form after I selected several entries and clicked Update cells and I then open the form again...it only lists the selected items that I updated just before....What would be my suggestion....I realy would like to list all entries.....only I want the ones I selected to be selected when I open the form for the second time...

Other question......is there a tool on the market(macro or addon) to generate HTML code or append data from the form to a HTML document? Let's say a existing word document or HTML document?

I tried it with linked objects (Paste Special) together with word. only problem is....I want to use one excel sheet per project and i want the path to be relative....any ideas?

Greetings,

Ben

Thanx for the help.

David M58 · 07-03-2008, 02:14 PM

Some code modifications may be necessary. Feel free to attach a copy of the updated Excel file you have. Provide a few details about the operation of the form (describing the specific ranges that are involved and what you expect to see on the form and on the spreadsheet when the form is working properly) and we may be able to provide further assistance.

07-02-2008, 11:30 AM	#2 (permalink)
David M58 Registered User Join Date: May 2008 Location: Baltimore, Maryland Posts: 160 OS: Windows XP SP3	Re: Form, listbox multiselect = yes problem Here are some suggestions that you may find helpful. To fill the ListBox with values and open the form when the workbook is opened, you can place the following code in the ThisWorkbook object: Code: Private Sub Workbook_Open() UserForm1.lsboxMainframe.RowSource = "Sheet2!A2:A50" UserForm1.Show End Sub If you want CommandButton1 on Sheet1 to open the form with values, you can place similar code in the Sheet1 object: Code: Private Sub CommandButton1_Click() UserForm1.lsboxMainframe.RowSource = "Sheet2!A2:A50" UserForm1.Show End Sub To transfer the selected items to Sheet1 when the Update button is clicked, you can use the code you have in UserForm1: Code: Private Sub CommandButton2_Click() Dim lItem As Long For lItem = 0 To lsboxMainframe.ListCount - 1 If lsboxMainframe.Selected(lItem) = True Then Sheet1.Range("A65536").End(xlUp)(2, 1) = lsboxMainframe.List(lItem) lsboxMainframe.Selected(lItem) = False End If Next lItem End Sub To make the form close when you click the Close button, you can use the following code in UserForm1: Code: Private Sub CommandButton1_Click() Unload Me End Sub Modify as necessary.

07-03-2008, 12:16 AM	#3 (permalink)
benjansen Registered User Join Date: Jun 2008 Posts: 3 OS: xp	Re: Form, listbox multiselect = yes problem David, Thanx for the fast reply....problem is when I close the form after I selected several entries and clicked Update cells and I then open the form again...it only lists the selected items that I updated just before....What would be my suggestion....I realy would like to list all entries.....only I want the ones I selected to be selected when I open the form for the second time... Other question......is there a tool on the market(macro or addon) to generate HTML code or append data from the form to a HTML document? Let's say a existing word document or HTML document? I tried it with linked objects (Paste Special) together with word. only problem is....I want to use one excel sheet per project and i want the path to be relative....any ideas? Greetings, Ben Thanx for the help.

07-03-2008, 02:14 PM	#4 (permalink)
David M58 Registered User Join Date: May 2008 Location: Baltimore, Maryland Posts: 160 OS: Windows XP SP3	Re: Form, listbox multiselect = yes problem Some code modifications may be necessary. Feel free to attach a copy of the updated Excel file you have. Provide a few details about the operation of the form (describing the specific ranges that are involved and what you expect to see on the form and on the spreadsheet when the form is working properly) and we may be able to provide further assistance.