CODEWITHSUNDEEP
excelvba
Josh Ng
Josh Ng

Reputation: 198

Auto Checkbox Item Generation

Screenshot1

Hi everyone,

I've been stuck at this for some time... I hope someone could enlighten me on this. So, I have created a 4 list boxes over here (ActiveX) and I am hoping to create checkboxes within each list box so that each time I can hide/unhide an excel sheet from the same workbook just by selecting it. However, I have too many tabs in the workbook alone. Also, these tabs are all sort of like Warehouse 1, Warehouse 2, Security 1,Security 2, etc etc. So there's sort of like a common name before these numbers.

I was wondering if there's anyway to auto generate the checkboxes in accordance to the tab names?

For example, if i select 1 under the Warehouse listbox, it should link over to the tab Warehouse 1 in the workbook.

Any help would be great. Thanks!

Upvotes: 1

Views: 92

Answers (2)

Samuel Everson
Samuel Everson

Reputation: 2102

To achieve your goal you will first need to make some changes to the properties of the listbox on the sheet.

Enter design mode and right click the listbox > Properties.

Change the following properties:

  • 'ListStyle' to 1 - fmListStyleOption
  • 'MultiSelect' to 1 - fmMultiSelectMulti

The ListStyle change will put option buttons to the left of each list entry and the MultiSelect adjustment will change the option buttons to checkboxes.

Here is some sample code to populate the ListBox with the Worksheet names - I have assumed your worksheet containing the ListBox is named "Main" AND I have named the "Generate CheckBox" button cmdGenerateCheckBox.

Private Sub cmdGenerateCheckBox_Click()

Dim WorksheetArray() As Variant
Dim WorksheetIndex As Long
Dim ArrayElement As Variant
Dim NumberOfSheets As Long
Dim MainSheet As Object

Set MainSheet = ThisWorkbook.Sheets("Main")

NumberOfSheets = ThisWorkbook.Sheets.Count
ReDim WorksheetArray(1 To NumberOfSheets)

For WorksheetIndex = 1 To UBound(WorksheetArray)
    WorksheetArray(WorksheetIndex) = ThisWorkbook.Sheets(WorksheetIndex).Name
Next WorksheetIndex

MainSheet.lstWarehouse.Clear
MainSheet.lstSecurity.Clear

For Each ArrayElement In WorksheetArray()
    If ArrayElement Like "Warehouse*" Then
        MainSheet.lstWarehouse.AddItem ArrayElement
    ElseIf ArrayElement Like "Security*" Then
        MainSheet.lstSecurity.AddItem ArrayElement
    End If
Next ArrayElement

Dim ListItemCounter As Long
Dim ListItem As Variant

'----------------lstWarehouse ListBox-----------------
ListItemCounter = 0
For Each ListItem In MainSheet.lstWarehouse.List
    For Each ArrayElement In WorksheetArray()
        If ListItem = ArrayElement Then
            If ThisWorkbook.Sheets(ArrayElement).Visible = True Then
                MainSheet.lstWarehouse.Selected(ListItemCounter) = True
            ElseIf ThisWorkbook.Sheets(ArrayElement).Visible = False Then
                MainSheet.lstWarehouse.Selected(ListItemCounter) = False
            End If
        Else
            'Do nothing
        End If
    Next ArrayElement
    ListItemCounter = ListItemCounter + 1
Next ListItem

'----------------lstSecurity ListBox-----------------
ListItemCounter = 0
For Each ListItem In MainSheet.lstSecurity.List
    For Each ArrayElement In WorksheetArray()
        If ListItem = ArrayElement Then
            If ThisWorkbook.Sheets(ArrayElement).Visible = True Then
                MainSheet.lstSecurity.Selected(ListItemCounter) = True
            ElseIf ThisWorkbook.Sheets(ArrayElement).Visible = False Then
                MainSheet.lstSecurity.Selected(ListItemCounter) = False
            End If
        Else
            'Do nothing
        End If
    Next ArrayElement
    ListItemCounter = ListItemCounter + 1
Next ListItem

End Sub

Upvotes: 1

Josh Ng
Josh Ng

Reputation: 198

enter image description here

Firstly, I would like to thank Samuel for his precious time taken off to help me solve this issue. Without his patience, this wouldn't have been possible!

Secondly, just to add onto Samuel's answer, you HAVE TO change the name of the listboxes. So for example, 1stWarehouse, 1stSecurity. By changing name, it means to go over to properties and change (as seen on the screenshot).

Upvotes: 0

Related Questions