Forum Discussion

kadirguler's avatar
kadirguler
Brass Contributor
Jan 15, 2021

Userform To Hide-Unhide Worksheet’s Columns

The userform that we created to hide the columns in the workbook and unhide the hidden columns contains also a button to minimize userform. With to the drop-down list in the userform can be navigated between worksheets, the selected worksheet from the combobox is active and the column management(hide-unhide) of this worksheet is provided.

 

I think it will be useful when working on large sheets with many columns.

 

 

For details and to download sample workbook : https://eksi30.com/excel-hide-unhide-columns-using-userform/

developer
excel
Macros and VBA
office 365

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jan 16, 2021

    kadirguler 

     

    Show / hide columns according to UserForm ListBox specifications

    Problem:

    How can I show and hide columns of different worksheets depending on the specification in ListBoxes of a UserForm MultiPage control?

     

    Solution:

    Enter the following code into the class module of the UserForm.

    See example workbook is in German - The source site is in German too.

     

     

    StandardModule: basMain

Sub CallForm()
   frmTabellen.Show
End Sub

ClassModule: frmTabellen

Private Sub cmdWeiter_Click()
   Unload Me
End Sub

Private Sub lst1_Change()
   Call EinAus(MultiPage1.Value + 1)
End Sub

Private Sub lst2_Change()
   Call EinAus(MultiPage1.Value + 1)
End Sub

Private Sub MultiPage1_Change()
   Worksheets(MultiPage1.Value + 2).Select
End Sub

Private Sub UserForm_Initialize()
   lst1.Column = Worksheets("Tabelle1").Range("A1").CurrentRegion.Value
   lst2.Column = Worksheets("Tabelle2").Range("A1").CurrentRegion.Value
   Worksheets("Tabelle1").Range("A1").CurrentRegion.Columns.Hidden = True
   Worksheets("Tabelle2").Range("A1").CurrentRegion.Columns.Hidden = True
   Worksheets("Tabelle1").Select
   MultiPage1.Value = 0
End Sub

Private Sub EinAus(iSheet As Integer)
   Dim iCounter As Integer
   With Controls("lst" & iSheet)
      For iCounter = 0 To .ListCount - 1
         Columns(iCounter + 1).Hidden = _
            Not .Selected(iCounter)
      Next iCounter
      Cells(1, 1).Activate
   End With
End Sub


Source: https://www.herber.de/mailing/Spalten_ein-_ausblenden_nach_UserForm-ListBox-Vorgabe.htm

     

    Here's another example, maybe it will help with your plans.

     

    Maybe that will bring you a little closer to your goal.

     

    Hope I could help you at least a little.

    I would be happy to know if I could help.

     

    I wish you continued success with Excel

     

    Nikolino

    I know I don't know anything (Socrates)