Using two listboxes to show or hide worksheets

[Waimea]

I found this piece of code https://www.ozgrid.com/forum/forum/...-worksheet-based-on-selection-made-on-listbox that hides or shows spreadsheets, I want to change this code to have two list boxes and two additional buttons to hide or show worksheets and when I transfer a sheet from listbox1 to listbox2 I want it to be able to hide with xlVeryHidden.

Option Explicit


Public CurrentBook As Workbook
Dim DisableMyEvents As Boolean


Private Sub CommandButton1_Click()
    Dim i As Long
    DisableMyEvents = True
    With ListBox1
        For i = 0 To .ListCount - 1
            .Selected(i) = False
        Next i
    End With
    DisableMyEvents = False
    Call ListBox1_Change
End Sub


Private Sub CommandButton2_Click()
    Dim i As Long
    DisableMyEvents = True
    With ListBox1
        For i = 0 To .ListCount - 1
            .Selected(i) = True
        Next i
    End With
    DisableMyEvents = False
    Call ListBox1_Change
End Sub


Private Sub CommandButton3_Click()
    Unload Me
End Sub


Private Sub ListBox1_Change()
    If DisableMyEvents Then Exit Sub
    Dim i As Long
    Application.ScreenUpdating = False
    With ListBox1
        For i = 0 To .ListCount - 1
            IndicatedSheet(i).Visible = IIf(.Selected(i), xlSheetHidden, xlSheetVisible)
        Next i
    End With
    Application.ScreenUpdating = True
End Sub


Function IndicatedSheet(Index As Long) As Worksheet
    If -1 < Index And Index < ListBox1.ListCount Then
        Set IndicatedSheet = CurrentBook.Sheets(ListBox1.List(Index, 0))
    End If
End Function


Private Sub UserForm_Activate()
    Dim i As Long
    Dim N As Long


    DisableMyEvents = True
    
    For N = 3 To CurrentBook.Sheets.Count
        ListBox1.AddItem ActiveWorkbook.Sheets(N).Name
    Next N
    
    With ListBox1
        For i = 0 To .ListCount - 1
            .Selected(i) = Not (IndicatedSheet(i).Visible = xlSheetVisible)
        Next i
    End With
    DisableMyEvents = False
End Sub


Private Sub UserForm_Initialize()
    With ListBox1: Rem these properties can be Set at design time
        .ColumnCount = 2
        .ColumnWidths = ";0"
        .BackColor = Me.BackColor
        .ListStyle = fmListStyleOption
        .MultiSelect = fmMultiSelectMulti
        .SpecialEffect = fmSpecialEffectFlat
    End With
    
    Set CurrentBook = ThisWorkbook
End Sub

 

[Waimea]

I understand how to add items/worksheets to a listbox but not how to transfer a worksheet from listbox1 and listbox2?

 

[My Aswer Is This]

I never like looking at scripts found someplace else and then try to modify them to do what I want.

Please explain in detail what you wanting to do.

You subject title says:

Using two listboxes to show or hide worksheets

So are you saying if you choose a sheet name in listbox1 you want that sheet hidden?

And if you choose a sheet name in listbox2 you want that sheet now visible?

If so tell me is this a activex listbox


​

 

[Waimea]

Good morning My Aswer Is This,

I have two listboxes, listbox1 and listbox2. In listbox1 I add all worksheets that are in the workbook.

I have 4 commandbuttons. When I click commandbutton1 I want to hide all sheets but one named "Start", when I click commandbuttom2 I want to unhide all sheets including very hidden sheets.

When I click commandbutton3 and commandbutton4 I want to transfer sheets from listbox1 to listbox2 and make that sheet hidden. So listbox1 shows all items and listbox2 shows hidden sheets.

Also if possible, I want to be able to click the item in the listbox and go to that worksheet.

The controls are in an userform and I am using the toolbox.

 

[My Aswer Is This]

Let me see if I understand this:

When you click command1 you want all sheets hidden but sheet named Start

When you click command2 you want all sheets visible.

And in Listbox1 you always want to see all visible sheet names
And in Listbox2 you always want to see all hidden sheet names

And if you click on a sheet name in listbox1 you want to be taken to that sheet.

Is this correct.


I see no purpose in this:
This could be done automatically when you click on Command button 1 or two

When I click commandbutton3 and commandbutton4 I want to transfer sheets from listbox1 to listbox2 and make that sheet hidden. So listbox1 shows all items and listbox2 shows hidden sheets.​

 

[My Aswer Is This]

Try this:
Put these scripts in your Userform

You need no command Button 2 and Four

Just command Button1 and command button 2 and Listbox1 and listbox2

Private Sub CommandButton1_Click()
'Hide Sheets
'Modified  12/17/2018  1:30:34 AM  EST
ListBox2.Clear
Dim i As Long
For i = 1 To Sheets.Count
    If Sheets(i).Name <> "Start" Then Sheets(i).Visible = False: ListBox2.AddItem Sheets(i).Name
Next
ListBox1.Clear
ListBox1.AddItem "Start"
End Sub
Private Sub CommandButton2_Click()
'Show sheets
'Modified  12/17/2018  1:30:34 AM  EST
ListBox1.Clear
Dim i As Long
For i = 1 To Sheets.Count
    Sheets(i).Visible = True: ListBox1.AddItem Sheets(i).Name
Next
ListBox2.Clear
End Sub
Private Sub ListBox1_Click()
'Modified  12/17/2018  1:30:34 AM  EST
Application.Goto Sheets(ListBox1.Value).Range("A1")
End Sub
Private Sub UserForm_Initialize()
'Modified  12/17/2018  1:30:34 AM  EST
Dim i As Long
For i = 1 To Sheets.Count
    If Sheets(i).Visible = True Then ListBox1.AddItem Sheets(i).Name
    If Sheets(i).Visible = False Then ListBox2.AddItem Sheets(i).Name
Next
End Sub

 

[Waimea]

Hi Aswer Is This,

thank you for your reply and for your code!

Commanbutton1 hides all sheets but Start?

And Commandbutton2 shows all sheets?

It works great and the application.goto is great but how do I hide a single sheet?


I added

Private Sub ListBox2_Click()
Application.Goto Sheets(ListBox2.Value).Range("C1")
End Sub

But this code gives me a runtime error?

And I am trying to add how to move one or more sheets from listbox1 to listbox2 or vice versa?

 

[My Aswer Is This]

Your script is trying to go to a hidden sheet.
Listbox2 has the list of hidden sheets.
You cannot go to a hidden sheet.
To do this the script would have to unhide the sheet first and then goto it.
You said:

And I am trying to add how to move one or more sheets from listbox1 to listbox2 or vice versa?

Why do you need to do that?

Moving a sheet name from one list to the other is not going to hide or unhide the sheet.

You asked how do I hide or unhide a single sheet.
You never asked for this in your original post.

Well you tell me how do you want to do this.

I would say you will need a Listbox3 where we have all the sheet names.
If you click on listbox3 name David then sheet name David would be hidden and that name would be added to listbox2

You may need to sort out all the things you want and I need to write some more code.

So are you now saying you want a way to hide or unhide one sheet at a time.

And have those names added to listbox1 or listbox2

​

 

[Waimea]

Hi My Aswer Is this, thank you for your reply and I apologize for not being clear.

What you suggest with 3 listboxes sounds great.

I want to be able to select a sheet and decide if it is visible or hidden by clicking a button. One button to hide a sheet, another button to show a sheet.

Perhaps also the possibility to select more than one items at a time and hide / show these items.

 

[My Aswer Is This]

Yes I'm getting very confused now for sure.

Now you said:

I want to be able to select a sheet and decide if it is visible or hidden by clicking a button. One button to hide a sheet, another button to show a sheet.

You want to select a sheet how?
You mean you want to click on the sheet tab?

And so then does this sheet name need to be added to or removed from the listbox.

And you said:

Perhaps also the possibility to select more than one items at a time and hide / show these items.

You plan to select more then one sheet tab at a time.

And now you keep moving the Goal Post.

You never mentioned any of this in your original post
​

<strike></strike>
​