Populate listbox with sheet names and make selectable.

yegarboy · Nov 11th 2010

Hey guys, i need a point in the right direction please. I have a form with a listbox. Apon initializing i want the listbox to be populated with all the sheet names. I would then like to be able to select the sheet i want and click a command button to send my data to that sheet. Here is a sample description of my task.

I open my first form and fill it in.
I then select special job by clicking the command button.
My form with the listbox now opens.
"i would select the sheet that the special job is in and press the command button"
All data from the first form is transfered to the selected sheet.

The good news is i have already programmed the transfer code all i need is to populate the listbox and have it seletable.

Also is there a way to not display the first 6 or 7 pages?

Thanks in advance for any ideas.

**mikerickson** · Nov 11th 2010

Re: Populate listbox with sheet names and make selectable.

You could put this in the userform's code module

Code

Private Sub UserForm_Initialize()
    Dim ExcludedSheets As Variant
    Dim oneSheet As Worksheet
    ExcludedSheets = Array("sheet2")
    For Each oneSheet In ThisWorkbook.Sheets
        With oneSheet
            If IsError(Application.Match(.Name, ExcludedSheets, 0)) Then
                ListBox1.AddItem .Name
            End If
        End With
    Next oneSheet
End Sub


Function selectedSheet() As Worksheet
    On Error Resume Next
    Set selectedSheet = ThisWorkbook.Sheets(ListBox1.Value)
    On Error GoTo 0
End Function

Display More

The intialize routine fill the listbox with the sheets' names (except for the explicitly ExcludedSheets)
You transfer routine would use the SelecteSheet function to specify the destination sheet for the data. Note: selectedSheet might be Nothing if no selection is made.

yegarboy · Nov 11th 2010

Re: Populate listbox with sheet names and make selectable.

i am trying it now.

yegarboy · Nov 11th 2010

Re: Populate listbox with sheet names and make selectable.

It will only hide 1 sheet

yegarboy · Nov 11th 2010

Re: Populate listbox with sheet names and make selectable.

Scratch that last one, i figured it out.

yegarboy · Nov 11th 2010

Re: Populate listbox with sheet names and make selectable.

Im not sure how you implimented the last bit of code. How will that transfer my data? How do i use it?

**mikerickson** · Nov 11th 2010

Re: Populate listbox with sheet names and make selectable.

The selecteSheet function will not transfer anything. It will return the Worksheet object selected by the user. It is a tool that your existing transfer code. It would be used somewhat like

Code

Private Sub butOK_Click()
    Dim sendToSheet as Worksheet
  
    sendToSheet = SelectedSheet()


    If Not sendToSheet is Nothing Then
        With sendToSheet
            .Range("A1").Value = TextBox1.Text
            .Range("B1").Value = TextBox2.Text
            Rem etc.
        End With
    End IF
End Sub

Display More

yegarboy · Nov 11th 2010

Re: Populate listbox with sheet names and make selectable.

Thanks Mic i got it going! Your awesome!!

Populate listbox with sheet names and make selectable.

Participate now!

Share