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.
Code:
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
