I received a demand now, so, first, for example:
A page has 4 Sheet in Excel file, respectively Sheet1/Sheet2/Sheet3/Sheet4;
In the Sheet1 for options page allows the user to select (can choose more); Sheet2 Sheet3/Sheet4 (column name) page corresponding to three areas of sales data,
Now demand is according to Sheet1 selected month shows that the regional Sheet2 Sheet3/Sheet4 in sales data, not hidden in the selected;
I now idea is inserted into the list box in the sheet1 (pops), add a button to call the macro;
Before but have not used VBA, think how great god please help to think about the specific implementation?
Attach the screenshot Sheet1:
Sheet2/Sheet3/Sheet4 screenshots:

CodePudding user response:

Put the things you want to hide in a separate column, or on a separate sheet, sheet or hide columns, during the recording macros, then can see the specific code should be how to write

CodePudding user response:

From the point of your screenshot, is inserted "form controls,"
This seems can not "data", determine whether to choose a,

Need to use ActiveX controls, can get status information in VBA,

CodePudding user response:

Thank you reply, if there are alternative approach, under the trouble that specific how to implement? If is to use ActiveX controls

CodePudding user response:

Refer to the following code,
Pay attention to your ListBox control name! If different modified by yourself,

 Option Explicit 

Private Sub CommandButton1_Click () 
'according to the selected in the show/hide the corresponding column 
Dim As Long, I vs As Boolean 

For I=0 & amp; To 11 
V=Not ListBox1. Selected (I) 
Sheet2. Columns (1 & amp; + I). EntireColumn. Hidden=v 
Sheet3. Columns (1 & amp; + I). EntireColumn. Hidden=v 
Sheet4. Columns (1 & amp; + I). EntireColumn. Hidden=v 
Next 
End Sub 

Private Sub CommandButton2_Click () 
'add a list of data, can be written in the other, "open the document" execution time, 
Dim As Long I 

Call ListBox1. Clear 
ListBox1. The MultiSelect=1 
For I=1 To 12 
The ListBox1. AddItem I & amp; "In" 
Next 
End Sub 

CodePudding user response:

Oh, by the way, is your "in" column to B M column, the code above 9 to 11 in line 1 & amp; + I changed to 2 & amp; + I,

CodePudding user response:

Code can be added in 4th floor code line 6:
If (0=ListBox1. ListCount) Then the Exit Sub

This can avoid "empty list" caused by abnormal operation,