Show Only One Column


          Suppose you have a worksheet with a lot of columns. There are headers in Column A, other columns of sheets have data.
You want column A to remain constant, and you want to see one by one next to column A to examine the other columns.

         We have prepared a userform for this purpose.We've added a combobox and toggle button to Excel userform.
The column selected from the Combobox in the userform is displayed next to column A, the other columns are hidden.

"Private Sub ComboBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Me.ComboBox1.DropDown
End Sub
Private Sub ToggleButton1_Click()
ComboBox1.Value = ""
ActiveSheet.Cells.EntireColumn.Hidden = False
End Sub

Private Sub UserForm_Initialize()
  Dim lst_column As Integer
    lst_column = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
For j = 2 To lst_column
   ComboBox1.AddItem Split(ActiveSheet.Cells(1, j).Address, "$")(1) & " " & "-" & Cells(1, j).Value
Next j
End Sub

Private Sub ComboBox1_Change()
   Dim lst_column As Integer
lst_column = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
    For j = 2 To lst_column
         Columns(Split(ActiveSheet.Cells(1, j).Address, "$")(1)).EntireColumn.Hidden = True
Next j
Columns(ComboBox1.ListIndex + 2).EntireColumn.Hidden = False
ToggleButton1.Value = False
 End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        ActiveSheet.Cells.EntireColumn.Hidden = False
    End If
End Sub"


No comments:

Post a Comment