Creating a Sheet List using Listbox

Question

In a User form using a Listbox1 I would like to make a list of the Opened Workbooks and in the Listbox2 in the same form the sheets of the selected workbook in the listbox1 But also in the lisbox2 I would like to create with each sheet name a checkbox with five command buttons in the form to import, Export, Erase, Hide or Unhide the selected sheets from Wb1 to Wb2 and vice versa. So far I receive assistance from you guys to make a do a form with a list of opened workbooks and a list of the respective worksheets here also I'm trying to get to work a code to import the sheets from one workbook to another here,. Do you know a Way to make this happen. thank You

By the way this is the code use from a sheet to erase the sheets that might be put in the list

Sub DeleteSheets()


    Dim wks As Worksheet
    Dim MyRange As Range
    Dim cell As Range

    Set wks = Worksheets("Controls")

    With wks
    Set MyRange = Range("D5:D34", .Cells(.Rows.Count, "H").End(xlUp))

    End With

    On Error Resume Next
    Application.DisplayAlerts = False
    For Each cell In MyRange
    Sheets(cell.Value).Delete

    Next cell
    Application.DisplayAlerts = True
    On Error GoTo 0
    Sheets("Controls").Range("D5:D34").ClearContents

End Sub

And this ones for hide and unhide the sheets:

Sub Hide_Sheets()

    'In use
    'Hide the sheets in Controls Sheet
    Dim cell As Range
    On Error Resume Next
    For Each cell In Sheets("Controls").Range("E5:E34")
    ' Hide sheets
    Sheets(cell.Value).Visible = False

    Next cell
End Sub

Sub Unhide_Sheets()

    'In use
    'Unhide the sheets in Controls Sheet
    Dim cell As Range
    On Error Resume Next
    For Each cell In Sheets("Controls").Range("G5:G34")
    ' Hide sheets
      Sheets(cell.Value).Visible = True
      Next cell
End Sub

Answer 1

I suggest the following structure of your userform:

Since you do what two lists one with workbooks and one with the worksheets of the currently selected workbook. Then you want to have five command buttons for the actions you want to perfom.

In order to update the workbooks and worksheets you will want to place code in the userform inside the Userform_activate and Listbox1_Change events. So You get the code to list all the workbook names into the listbox1 and put it into Userform_Activate. Evertime the userform is activated the list of workbooks will be updated.

If you now select a entry of listbox1 you want the code to update your sheet names. So you get the code to update the sheet names of a workbook with name "wbname" and put it into listbox1_Change. You then do the following code:

Private Sub ListBox1_Change()
  Dim wbname as string
  wbname=ListBox1.Value

  call GetSheetNamesIntoListBox2(wbname)

End Sub

Where of course GetSheetNamesIntoListBox2 is the sub were you get all the sheetnames into ListBox2.

Lastly you need to setup the Buttons. Each Button has a Click Event which you will want to use. So if the Button is clicked the following code will run:

Private Sub CommandButton1_Click()
 Dim wbname as string, wsname as string

 wbname=Listbox1.Value
 wsname=Listbox2.Value

  ' You may want to check if wbname and wsname are valid before running the Task

 PerformAction(wbname,wsname)
End Sub

Where PerformAction is the routine you use to import, export, clear, hide or unhide the sheet.

I know it is no workedout solution but you will be able to adjust this general solution to your specific case. If you run into problems using my approach just ask in the comments.