VBA implementation of Excel two-level linkage multi-select function

Requirements

The second-level menu needs to be transformed according to the different level menu

The second-level menu is a multi-box, and after selection, comma-separated display in the cell then cell

Realize

First, as shown in, this is the excel2013 version.

Figure A

The data source is placed inside the Sheet2, and the data source is shown in two. Here, the first behavior of the first level is the H column of the data source "H column plus data validation as a sequence, the source is the first column of Sheet2, the degree Niang has a very detailed step"; The I column loads the option that corresponds to the multiple selection according to the H column.

Figure Two data sources

When writing code, be sure to add control first, step diagram as shown in Figure four is the control's property map, in addition, please first determine the macro and development tools enabled "degree Niang have detailed guidance." The control name is ListBox1 and placed in the I column. Right-sheet1--View Code---in the editor for it a series of code, here also attached to the code, I pieced together, I know not good-looking, but fortunately realized,,,, good luck.

Figure three Adding controls

Figure Four Control properties

Summary

Boss is want to let me realize, but, Minister incompetent Ah ~ The first day are looking at two level linkage menu, found that do not need VBA Ah, degree Niang said data validation can be realized, the next day reaction came over, need is multi-box, during debugging code when a face confused force, said I control undefined, later, the boss came, A face black line to help me drag out a control in the interface,,,, I control none, compiled a bunch of code what is the use,,,,,

1 Option Explicit2     DimT as String3     DimReload as Boolean4 Private SubListbox1_dblclick (ByValCancel asMsforms.returnboolean)5ActiveCell.Value =Listbox1.Value6     Me. Listbox1.clear7     Me. Listbox1.visible =False8 End Sub9 Private SubWorksheet_selectionchange (ByValTarget asRange)Ten     DimI as Integer One     DimJ as Integer A     DimY as Integer -     DimZ as Integer -     DimArr1 asVariant, ARR2 asVariant the     DimMyStr as String -     DimColumname as String -     DimX as String -     Me. Listbox1.clear +  -      +     IfTarget.count =1  Then 'Click a cell is valid, multiple selection is not valid A  at          with Me. ListBox1 -              IfTarget.Column = One  andTarget.Row >2  Then -                 IfCells (Target.Row, Target.Column-1) <>""  Then 'Superior No data, no multi-marquee -Columname = Cells (Target.Row, Target.Column-1) -                forV R1  to  - -               IfSheet2.cells (1, Y) = Columname Then 'get column numbers A, B, etc. according to the column name inZ =Y -                  IfY > -  Then toX =Mid(Cells (1, Y). Address,2,2)'This is the case after handling AA, AB, or 26 columns +                  Else -X =Mid("abcdefghijklmnopqrstuvwxyz"Y1) the                  End If *               End If $               NextPanax Notoginseng[B5] = X'This was used to view the results, and then forgot to delete, ,,,,, bless -                withSheet2'Load multiple options theARR1 =. Range (X &"2:"& X &. Range (X &"65535"). End (Xlup). Row) +                 If. Range (X &"65535"). End (Xlup). Row <>2  Then A                  forj =1  to. Range (X &"65535"). End (Xlup). Row-1 the                    +                     Me. ListBox1.AddItem Arr1 (J,1) -                     $                 NextJ $                 Else -                     Me. ListBox1.AddItem Sheet2.cells (2, Z) -                 End If the               End  with -t =ActiveCell.ValueWuyiReload =True the                  fori =0  to. ListCount-1 -                      If InStr(T,. List (i)) Then Wu. Selected (i) =True -                      Else About. Selected (i) =False $                      End If -                 Next -Reload =False -. Top = Activecell.top +Activecell.height A. left =Activecell.left +. Width =Activecell.width the. Visible =True -                  $                 Else the. Visible =False 'when listening to a column other than this, hide the check box the                 End If the             Else the. Visible =False -             End If int ="" the         End  with the          About     End If the End Sub the Private SubListbox1_change () the     DimI as Integer +     DimFlag as Boolean -Flag =False the     IfReload Then Exit SubBayi      fori =0  to Me. ListBox1.ListCount-1 the         If Me. listbox1.selected (i) =True  Then thet = t &","&Me. Listbox1.List (i) -Flag =True -         End If the     Next the     IfFlag =False  Then thet ="" the     End If -ActiveCell.Value ="" theActiveCell =Mid(T,2) thet ="" the End Sub

Code

Original address: http://www.cnblogs.com/chanmao--/p/6294548.html

Original address: http://www.cnblogs.com/chanmao--/p/6294548.html

VBA implementation of Excel two-level linkage multi-select function