vba 使用选定的列表框值动态隐藏/显示列

Using selected listbox value to dynamically hide/show columns

提问by user1422641

I could use some help with a multi-select listbox form in excel which has to be done via VBA, the values of the forms are months from jan-june as seen below.

我可以对 excel 中的多选列表框表单使用一些帮助，该表单必须通过 VBA 完成，表单的值是从一月到六月的几个月，如下所示。

• JAN
• FEB
• MARCH
• APRIL
• MAY
• JUNE

• 一月
• 2月
• 行进
• 四月
• 可能
• 六月

If Jan is selected for example , I would like columns , e through h shown. If Jan is not selected then columns e through h would be hidden.

例如，如果选择 Jan ，我希望列从 e 到 h 显示。如果没有选择 Jan，则 e 到 h 列将被隐藏。

JAN - (COLUMNS E-H)
FEB - (COLUMNS I-M)
MARCH - (COLUMNS N-R)
APRIL - (COLUMNS S-W)
MAY - (COLUMNS X-AB)
JUNE - (COLUMNS AC-AG)

JAN - (COLUMNS EH)
FEB - (COLUMNS IM)
MARCH - (COLUMNS NR)
APRIL - (COLUMNS SW)
May - (COLUMNS X-AB)
JUNE - (COLUMNS AC-AG)

回答by JimmyPena

Welcome to Stack Overflow. Sometimes, the macro recorder is your friend.

欢迎使用堆栈溢出。有时，宏记录器是您的朋友。

I recorded a macro of myself hiding columns E through H. Here was the recorded code:

我录制了自己隐藏列 E 到 H 的宏。这是录制的代码：

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/17/2012 by Jimmy Pe?a
'

'
    Columns("E:H").Select
    Selection.EntireColumn.Hidden = True
End Sub

The Select/Selection pair is a clear indication that I can concatenate these two lines. I shorten the code like this:

Select/Selection 对清楚地表明我可以连接这两行。我像这样缩短代码：

Columns("E:H").EntireColumn.Hidden = True

Columns("E:H").EntireColumn.Hidden = True

Since the property takes a value of True, I know that I can hide columns by setting the value to Trueand show columns by setting the value to False.

由于该属性的值为True，我知道我可以通过将值设置为 来隐藏列，True并通过将值设置为 来显示列False。

Since you haven't shown the code you are using, I'll provide a generic approach which you will need to customize for your use.

由于您尚未展示您正在使用的代码，我将提供一种通用方法，您需要对其进行自定义以供使用。

I added a listbox to a sample userform. This code will selectively hide/unhide depending on which value is selected.

我在示例用户表单中添加了一个列表框。此代码将根据选择的值有选择地隐藏/取消隐藏。

Private Sub ListBox1_Click()

  Dim selectedValue As String

  ' get selected value from listbox
  selectedValue = ListBox1.value

  Application.ScreenUpdating = False

  ' unhide any columns
  Cells.EntireColumn.Hidden = False

  Select Case selectedValue
  Case "JAN"
    Columns("E:H").EntireColumn.Hidden = True
  Case "FEB"
    Columns("I:M").EntireColumn.Hidden = True
  Case "MARCH"
    Columns("N:R").EntireColumn.Hidden = True
  Case "APRIL"
    Columns("S:W").EntireColumn.Hidden = True
  Case "MAY"
    Columns("X:AB").EntireColumn.Hidden = True
  Case "JUNE"
    Columns("AC:AG").EntireColumn.Hidden = True
  End Select

  Application.ScreenUpdating = True

End Sub