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]

Hi, thank you for your reply. English is not my mother tongue so it is easy getting lost in translation!

Listbox1 shows all sheets. Listbox2 shows visible sheets. Listbox3 shows hidden sheets.

I want to be able to click a sheet in listbox2 (selecting it) or listbox3 (selecting it) and then click two different buttons, one button to hide and one button to show that sheet that is selected.

That would mean that I would have to doubleclick on a sheet in listbox1 or listbox2 for Application.Goto.

 

[Waimea]

Yes, I am sorry for moving the goal post this is n

ot intentional but I am trying to explain what I want. Each time I explain something it seems like I want to change something.

This link shows an image with what I am trying to explain:

https://ibb.co/HtsT404

 

[My Aswer Is This]

This script will load all the sheet names into the proper listbox when userform is open

Listbox1 Listbox2 Listbox3

See if this is still what you want.
Then we can work on other parts of what you want.

Private Sub UserForm_Initialize()
'Modified  12/17/2018  10:37:38 PM  EST
Dim i As Long
For i = 1 To Sheets.Count
    ListBox1.AddItem Sheets(i).Name
    If Sheets(i).Visible = True Then ListBox2.AddItem Sheets(i).Name
    If Sheets(i).Visible = False Then ListBox3.AddItem Sheets(i).Name
    
Next
End Sub

 

[Waimea]

Good morning My Aswer Is this,

thank you for your patience. Your code does what I want!

What parts do you think the code needs?

 

[My Aswer Is This]

I do not know what else you need. I have become confused what you have and what you still need.

 

[Waimea]

I am also confused. Right now I have 3 listboxes.

Listbox1 shows all sheets, listbox2 shows all visible sheets, listbox3 shows all invisible sheets.

I have two commandbuttons, one hides all sheets except "Start", the other unhides all sheets.

I would like two more buttons, one button to hide a worksheet and one button to show a worksheet.

So when I click button3 or 4 the item in listbox 2 or 3 becomes visible or invisble.

Also, if possible, I would like application.goto for listbox1 and listbox2.

I am not sure if am making you more or less confused?

 

[My Aswer Is This]

I still think it would be easier to do this.

When you click on a value in Listbox2 that sheet is now hidden and that sheet name is added to listbox3

When you click on a value in Listbox3 that sheet is now visible and that sheet name is added to listbox2

When you double click on any sheet in Listbox1 you will be taken to that sheet if it's visible.
you cannot be taken to a hidden sheet. Unless you want the script to unhide the sheet and then go to it.

Would this not work for you.

This then would not require any more buttons.

See if we use buttons then we have to determine which list listbox2 or listbox3 is selected and then either hide or unhide that sheet.

And the same for the other button.

I have written code to do it the way I suggest and can send it to you if you want.

To do it your way I would need to know the name of the button you want to use so I can figure out how to do that. I have not tested a way to do it your way yet.

I would have to figure out which listbox is selected​

 

[Waimea]

Hi,

your suggestion sounds great and you are right, it's easier without the extra buttons.

I am thankful for your help with this and I would very much like the code that you have written!

 

[My Aswer Is This]

Try this:

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Modified  12/18/2018  5:23:19 AM  EST
'Listbox1 Goto Sheet
If Sheets(ListBox1.Value).Visible = True Then
    Application.Goto Sheets(ListBox1.Value).Range("A1")
Else
MsgBox "Sheets  " & ListBox1.Value & "  Is Hidden and we cannot go there"
End If
End Sub
Private Sub ListBox2_Click()
'Modified  12/18/2018  5:23:19 AM  EST
'Hide sheets
Sheets(ListBox2.Value).Visible = False
Dim i As Long
ListBox3.AddItem ListBox2.Value
ListBox2.Clear
For i = 1 To Sheets.Count
If Sheets(i).Visible = True Then ListBox2.AddItem Sheets(i).Name
Next
End Sub
Private Sub ListBox3_Click()
'Modified  12/18/2018  5:23:19 AM  EST
'Show sheets
Sheets(ListBox3.Value).Visible = True
Dim i As Long
ListBox2.AddItem ListBox3.Value
ListBox3.Clear
For i = 1 To Sheets.Count
If Sheets(i).Visible = False Then ListBox3.AddItem Sheets(i).Name
Next
End Sub
Private Sub UserForm_Initialize()
'Modified  12/18/2018  5:23:19 AM  EST
Dim i As Long
For i = 1 To Sheets.Count
    ListBox1.AddItem Sheets(i).Name
    If Sheets(i).Visible = True Then ListBox2.AddItem Sheets(i).Name
    If Sheets(i).Visible = False Then ListBox3.AddItem Sheets(i).Name
    
Next
End Sub

 

[Waimea]

Hi My Aswer Is This,

thank you for your code and I am trying to understand it right now.

I have added:

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 CommandButton3_Click()
    Unload Me
End Sub

Commandbuttom3_Click works as expected. Commandbutton1 and 2 doesn't work with the new code?