none
Hide sheet names in a listbox RRS feed

  • Question

  • Hi,

     

    Not sure if this is the correct forum for Excel VBA question, if not then I'm sorry.

    I have this code:-

    Private Sub SheetListBox_Click()
    
    
    
    If SheetListBox.ListIndex <> -1 Then
     
     'Worksheets(InsertRows).Select.Visible = False
     
     Worksheets(SheetListBox.Value).Activate
    End If
    End Sub
    
    Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    
    For Each ws In Worksheets
     If ws.Visible = True Then
      SheetListBox.AddItem ws.NAME
     End If
    Next
    
    End Sub
    
    Private Sub OK_Button_Click()
     Unload Me
    End Sub
    


    This opens a list box and shows all the sheets within a workbook.

    I'm having a couple of problems here:-

    1. I'm trying to not disply the "InsertRows" sheet in the list box, but can't get this working.

    2. I would like to display all sheets in all workbooks that are open, is this possible?

     

    Can someone please help,

    Thanks

    Best regards

    Scott

    • Changed type styoda Monday, July 4, 2011 3:56 PM
    Monday, July 4, 2011 3:54 PM

Answers

  • Try the following. Note the comments.

    Private Sub UserForm_Initialize()
      'Note: this code could be run from a command Button if desired.
      Dim WB As Workbook
     
      'WorkBookList is the list box for WorkBook Names
     
      For Each WB In Application.Workbooks
            WorkBookList.AddItem WB.Name
      Next WB

    End Sub

    Following edited to work with SheetList Change

    Private Sub WorkBookList_Change()
      On Error GoTo ReEnableEvents
      Application.EnableEvents = False
      Dim WB As Workbook
      Dim aSheet As Worksheet
     
      'SheetList is the ListBox for WorkSheet Names
      Set WB = Workbooks(Me.WorkBookList.Value)
     
      SheetList.Clear 'Remove existing list
     
      For Each aSheet In WB.Sheets
        If aSheet.Visible And aSheet.Name <> "InsertRows" Then
          SheetList.AddItem aSheet.Name
        End If
      Next aSheet
     
    ReEnableEvents:
      Application.EnableEvents = True
    End Sub

    Following added with a later Edit:

    Example below of the ListBox change event if you want to be able to Activate the Selected Workbook and Worksheet after you select the required worksheet. Also are you aware that if you want to be able to work on the selected worksheets after they are activated, you will need the forms ShowModal property set to False.

    Private Sub SheetList_Change()
      Workbooks(WorkBookList.Value).Activate
      If SheetList.Value <> "" Then
        Worksheets(SheetList.Value).Activate
      End If
    End Sub


    Regards, OssieMac


    • Marked as answer by styoda Wednesday, July 6, 2011 1:55 PM
    Wednesday, July 6, 2011 1:36 AM

All replies

  • Hi,

     

    Not sure if this is the correct forum for Excel VBA question, if not then I'm sorry.

    I have this code:-

    Private Sub SheetListBox_Click()
    
    
    
    If SheetListBox.ListIndex <> -1 Then
     
     'Worksheets(InsertRows).Select.Visible = False
     
     Worksheets(SheetListBox.Value).Activate
    End If
    End Sub
    
    Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    
    For Each ws In Worksheets
     If ws.Visible = True Then
     SheetListBox.AddItem ws.NAME
     End If
    Next
    
    End Sub
    
    Private Sub OK_Button_Click()
     Unload Me
    End Sub
    


    This opens a list box and shows all the sheets within a workbook.

    I'm having a couple of problems here:-

    1. I'm trying to not disply the "InsertRows" sheet in the list box, but can't get this working.

    2. I would like to display all sheets in all workbooks that are open, is this possible?

     

    Can someone please help,

    Thanks

    Best regards

    Scott


    For both 1 and 2 use the below that compiles ok but is otherwise untested:

    Sub updateListbox()
        Dim WB As Workbook
        For Each WB In Application.Workbooks
            Dim aSheet As Object
            For Each aSheet In WB.Sheets
                If aSheet.Visible And aSheet.Name <> "InsertRows" Then _
                    SheetListBox.AddItem aSheet.Name
                Next aSheet
            Next WB
        End Sub

     I would use a different UI architecture.  Use one list for workbooks and the 2nd to list the sheets in the workbook selected in the 1st listbox.

    You may also want to see

    Workbook Navigator for Excel

    http://www.tushar-mehta.com/excel/software/utilities/wb_nav.html

     


    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Monday, July 4, 2011 5:03 PM
  • I agree with Tushar that you need some method of identifying the workbook because you could have worksheets with identical names in different workbooks.

    The following code can be used with a 2 column listbox so that the first column contains the workbook name and the second column the worksheet name. This will allow you to address both the workbook and sheet name.

    When setting up a multi column listbox, note that the width of the columns are in points; not inches or centremetres. They default to widths proportional to the total width and no values shown in the properties. Therefore if setting the column widths manually, look at the total width first and set the column widths to sum to the total width. I included this because if you set them too small then you will not be able to see the data in them. In properties just enter the number points wide for each column with a comma between them and excel will insert the pts suffix.

    Private Sub UserForm_Initialize()
      Dim WB As Workbook
      Dim ListItem As Long
      Dim aSheet As Worksheet
     
      For Each WB In Application.Workbooks
        For Each aSheet In WB.Sheets
          If aSheet.Visible And aSheet.Name <> "InsertRows" Then
            SheetListBox.AddItem
            SheetListBox.List(ListItem, 0) = WB.Name
            SheetListBox.List(ListItem, 1) = aSheet.Name
            ListItem = ListItem + 1
          End If
        Next aSheet
      Next WB

    End Sub

    The following is an example attached to a command button to demonstrate how to address the values in each column. Note that in properties the column count is actual (like 2 columns is 1 and 2) but when addressing them in code they are 0 and 1.

    Private Sub CommandButton1_Click()

      If Not IsNull(SheetListBox) Then  'Test that selection made
       
        'Display the Workbook name and WorkSheet name
        MsgBox "Workbook Name = " & SheetListBox.Column(0) & vbCrLf & _
                "WorkSheet Name = " & SheetListBox.Column(1)
               
        Workbooks(SheetListBox.Column(0)).Activate  'Activate the Workbook
        Worksheets(SheetListBox.Column(1)).Activate 'Activate the WorkSheet
       
      End If
     
    End Sub

     


    Regards, OssieMac
    Tuesday, July 5, 2011 8:43 AM
  • Thanks very much to both of you for this help,

     

    Tushar I couldn't get your code to work properly, it didn't diplay any sheets.

     

    OssieMac, I got this to work very well, thanks very much. Is it possible to show the workbooks in one listbox and then when clicked on a workbook it then shows the sheets in another listbox?

     

    Thanks

    best regards

    Scott

     

    Tuesday, July 5, 2011 4:51 PM
  • Try the following. Note the comments.

    Private Sub UserForm_Initialize()
      'Note: this code could be run from a command Button if desired.
      Dim WB As Workbook
     
      'WorkBookList is the list box for WorkBook Names
     
      For Each WB In Application.Workbooks
            WorkBookList.AddItem WB.Name
      Next WB

    End Sub

    Following edited to work with SheetList Change

    Private Sub WorkBookList_Change()
      On Error GoTo ReEnableEvents
      Application.EnableEvents = False
      Dim WB As Workbook
      Dim aSheet As Worksheet
     
      'SheetList is the ListBox for WorkSheet Names
      Set WB = Workbooks(Me.WorkBookList.Value)
     
      SheetList.Clear 'Remove existing list
     
      For Each aSheet In WB.Sheets
        If aSheet.Visible And aSheet.Name <> "InsertRows" Then
          SheetList.AddItem aSheet.Name
        End If
      Next aSheet
     
    ReEnableEvents:
      Application.EnableEvents = True
    End Sub

    Following added with a later Edit:

    Example below of the ListBox change event if you want to be able to Activate the Selected Workbook and Worksheet after you select the required worksheet. Also are you aware that if you want to be able to work on the selected worksheets after they are activated, you will need the forms ShowModal property set to False.

    Private Sub SheetList_Change()
      Workbooks(WorkBookList.Value).Activate
      If SheetList.Value <> "" Then
        Worksheets(SheetList.Value).Activate
      End If
    End Sub


    Regards, OssieMac


    • Marked as answer by styoda Wednesday, July 6, 2011 1:55 PM
    Wednesday, July 6, 2011 1:36 AM
  • Hi OssieMac,

     

    Thanks for your reply, I must be doing something wrong, I can't get it to show the workbooks in the WorkBookList listbox, I'm using Excel 2010, will this make any difference?

     

    Thanks

    Best regards

    Scott

     

    Wednesday, July 6, 2011 10:48 AM
  • It's OK, I've got it working now, typo error.

     

    Thanks very much for your help.

     

    Best regards

    Scott

     

    Wednesday, July 6, 2011 1:55 PM
  • Hi OssieMac,

     

    Just wandered if you would be able to help with an additional part of this code.

    I have this section of the code which adds the sheets into the listbox:-

    Private Sub SheetList_Change()

        Workbooks(WorkBookList.Value).Activate
        If SheetList.Value <> "" Then
            Worksheets(SheetList.Value).Activate
        End If
       
    End Sub

    Private Sub SelectButton_Click()
        curButton = "SelectSheet"
        Unload Me
    End Sub

     

    And I have this other code which when a sheet has been selected in the sheetlist box and a button is pressed to select the sheet, it then does this:-

    If curButton = "SelectSheet" Then
        With ActiveSheet
        .Copy After:=Workbooks("Insert Blank Rows.xlsm").Sheets(1)
        End With
        InsertBlankRows
    End If

    What I'm trying to do is, if a sheet has not been selected from the sheetlist but the button is pressed I would like a message displayed asking the user to retry. I'm ok with the message but I don't know how to check if a sheet has not been selected and the button has been pressed.

     

    Hope this makes a bit of sense.

     

    Thanks

    best regards

    Scott

    Thursday, July 14, 2011 11:43 AM

  • What I'm trying to do is, if a sheet has not been selected from the sheetlist but the button is pressed I would like a message displayed asking the user to retry. I'm ok with the message but I don't know how to check if a sheet has not been selected and the button has been pressed.



    Hi Scott,

    You can insert this code in the beginning of the Click event for the button you mentioned:

    If SheetList.Value = "" Then
           Msgbox "Please select a sheet to continue", vbexclamation, "Error"

           Exit Sub 
    End If

    Hope this helps.

     


    Regards, Abel One often meets his destiny on the road he takes to avoid it.
    Thursday, July 14, 2011 11:53 AM
  • Hi Abel,

    Thanks for your reply, but I've already tried this and can't get it to work, any other suggestions?

    Thanks

    Best regards

    Scott

     

    Thursday, July 14, 2011 3:48 PM
  • Hello again styoda,

    Test for the ListIndex. If = -1 then no selection has been made.

      If SheetList.ListIndex = -1 Then
        MsgBox "No selection made in SheetList"
      Else
        'Insert your code in lieu of the following msgbox
        MsgBox SheetList.Value & " is selected"
      End If

    For added interest the VBA code to clear a listbox selection is by setting the ListIndex property to -1

    SheetList.ListIndex = -1


    Regards, OssieMac
    Friday, July 15, 2011 7:00 AM
  • Cool, Thanks very much OssieMac, works perfectly.

     

    Best regards

    Scott

    Friday, July 15, 2011 2:29 PM