|
要求 二級(jí)菜單需要根據(jù)一級(jí)菜單的不同變換內(nèi)容 二級(jí)菜單為多選框，選擇后，以逗號(hào)分隔顯示在單元格內(nèi) 實(shí)現(xiàn) 先上效果圖，如下圖圖一所示，這里面是excel2013版本
圖一效果圖 數(shù)據(jù)源放在了sheet2里面,數(shù)據(jù)源如下圖二所示。這里，使用第一行為第一級(jí)即H列的數(shù)據(jù)源【H列加數(shù)據(jù)驗(yàn)證為序列，源為sheet2的第一列，度娘有很詳細(xì)的步驟】；I列根據(jù)H列的不同，加載對(duì)應(yīng)列為多選的選項(xiàng)。
圖二數(shù)據(jù)源 在編寫代碼的時(shí)候，一定要記得先加控件，步驟圖如下圖三所示，圖四是控件的屬性圖，另外，請(qǐng)先確定啟用了宏和開(kāi)發(fā)工具【度娘有詳細(xì)教導(dǎo)】?？丶譃長(zhǎng)istBox1，放在I列。右鍵sheet1--查看代碼---在編輯器里面針對(duì)它進(jìn)行了一系列編碼，這里也附上了編碼，代碼是我拼湊過(guò)來(lái)的，我知道不好看，但是好在實(shí)現(xiàn)了，，，，，祝好吧。
圖三添加控件
圖四控件屬性
小結(jié) 老大是想讓我一天實(shí)現(xiàn)，但是，臣無(wú)能啊~第一天都在看二級(jí)聯(lián)動(dòng)菜單，發(fā)現(xiàn)不需要vba啊，度娘說(shuō)數(shù)據(jù)驗(yàn)證就能實(shí)現(xiàn)了，第二天反應(yīng)過(guò)來(lái)了，需要的是多選框，期間調(diào)試代碼的時(shí)候一臉懵逼，就說(shuō)我控件未定義，后來(lái)，老大來(lái)了，一臉黑線的幫我在界面拖出個(gè)控件，，，，，我控件都沒(méi)有，編了一堆代碼有何用，，，，，， 
 1 Option Explicit 2 Dim t As String 3 Dim Reload As Boolean 4 Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) 5 ActiveCell.Value = ListBox1.Value 6 Me.ListBox1.Clear 7 Me.ListBox1.Visible = False 8 End Sub 9 Private Sub Worksheet_SelectionChange(ByVal Target As Range) 10 Dim i As Integer 11 Dim j As Integer 12 Dim Y As Integer 13 Dim Z As Integer 14 Dim arr1 As Variant, arr2 As Variant 15 Dim myStr As String 16 Dim columName As String 17 Dim X As String 18 Me.ListBox1.Clear 19 20 21 If Target.Count = 1 Then '單擊一個(gè)單元格有效，多選無(wú)效 22 23 With Me.ListBox1 24 If Target.Column = 11 And Target.Row > 2 Then 25 If Cells(Target.Row, Target.Column - 1) <> "" Then '上級(jí)沒(méi)有數(shù)據(jù)，不顯示多選框 26 columName = Cells(Target.Row, Target.Column - 1) 27 For Y = 1 To 100 28 If Sheet2.Cells(1, Y) = columName Then '根據(jù)列名得到列號(hào)A、B之類的 29 Z = Y 30 If Y > 26 Then 31 X = Mid(Cells(1, Y).Address, 2, 2) '這是處理AA、AB，即26列以后的情況 32 Else 33 X = Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Y, 1) 34 End If 35 End If 36 Next 37 [B5] = X '這是當(dāng)時(shí)用來(lái)查看結(jié)果的，然后忘記刪掉了，，，，，，bless 38 With Sheet2 '加載多選項(xiàng) 39 arr1 = .Range(X & "2:" & X & .Range(X & "65535").End(xlUp).Row) 40 If .Range(X & "65535").End(xlUp).Row <> 2 Then 41 For j = 1 To .Range(X & "65535").End(xlUp).Row - 1 42 43 Me.ListBox1.AddItem arr1(j, 1) 44 45 Next j 46 Else 47 Me.ListBox1.AddItem Sheet2.Cells(2, Z) 48 End If 49 End With 50 t = ActiveCell.Value 51 Reload = True 52 For i = 0 To .ListCount - 1 53 If InStr(t, .List(i)) Then 54 .Selected(i) = True 55 Else 56 .Selected(i) = False 57 End If 58 Next 59 Reload = False 60 .Top = ActiveCell.Top + ActiveCell.Height 61 .Left = ActiveCell.Left 62 .Width = ActiveCell.Width 63 .Visible = True 64 65 Else 66 .Visible = False '監(jiān)聽(tīng)到非此列時(shí)，隱藏復(fù)選框 67 End If 68 Else 69 .Visible = False 70 End If 71 t = "" 72 End With 73 74 End If 75 End Sub 76 Private Sub ListBox1_Change() 77 Dim i As Integer 78 Dim flag As Boolean 79 flag = False 80 If Reload Then Exit Sub 81 For i = 0 To Me.ListBox1.ListCount - 1 82 If Me.ListBox1.Selected(i) = True Then 83 t = t & "," & Me.ListBox1.List(i) 84 flag = True 85 End If 86 Next 87 If flag = False Then 88 t = "" 89 End If 90 ActiveCell.Value = "" 91 ActiveCell = Mid(t, 2) 92 t = "" 93 End Sub
|
|
|
