如何创建“嵌套"广告素材? VBA组合框中的下拉列表? [英] How to create "nested" dropdowns in Excel VBA combo boxes?

问题描述

例如，

1. 银行现金

1. Cash in Bank

• 帐户#1
• 帐户#2
• 帐户编号3

工资支出

• 常规
• 额外
• 代理商

我想创建一个组合框，该框将允许选择主要类别(例如薪资费用)，同时还允许选择子类别(例如代理商).

I want to create a combo box that will allow the selection of the main category (e.g. Payroll Expense) while it also allows the selection of the sub categories (e.g. Agency).

以下是此类下拉菜单的示例:

Below is an example of these kinds of dropdowns:

推荐答案

下面是一个粗略的示例:

Here's a rough example:

设置两个用户窗体UserForm1和UserForm2.

Set up two userforms UserForm1 and UserForm2.

UserForm1上有标签.

UserForm2上有一个TreeView(您必须将其添加到工具箱中->右键单击工具箱-> Additional Controls...-> Microsoft TreeView Control, version 6.0)

UserForm2 has a TreeView on it (You'll have to add this to your toolbox -> Right Click on the Tool Box -> Additional Controls... -> Microsoft TreeView Control, version 6.0)

然后在UserForm1后面添加以下代码:

Then behind UserForm1 add the following code:

Private Sub Label1_Click()
    UserForm2.Show
End Sub

在UserForm2之后添加:

Option Explicit
#If VBA7 Then
    Private Declare PtrSafe Function FindWindow Lib "User32" _
    Alias "FindWindowA" ( _
    ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long

    Private Declare PtrSafe Function GetWindowLong Lib "User32" _
    Alias "GetWindowLongA" ( _
    ByVal hwnd As Long, _
    ByVal nIndex As Long) As Long

    Private Declare PtrSafe Function SetWindowLong Lib "User32" _
    Alias "SetWindowLongA" (ByVal hwnd As Long, _
    ByVal nIndex As Long, _
    ByVal dwNewLong As Long) As Long

    Private Declare PtrSafe Function DrawMenuBar Lib "User32" ( _
    ByVal hwnd As Long) As Long
#Else
    Private Declare Function FindWindow Lib "User32" _
    Alias "FindWindowA" ( _
    ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long

    Private Declare Function GetWindowLong Lib "User32" _
    Alias "GetWindowLongA" ( _
    ByVal hwnd As Long, _
    ByVal nIndex As Long) As Long

    Private Declare Function SetWindowLong Lib "User32" _
    Alias "SetWindowLongA" (ByVal hwnd As Long, _
    ByVal nIndex As Long, _
    ByVal dwNewLong As Long) As Long

    Private Declare Function DrawMenuBar Lib "User32" ( _
    ByVal hwnd As Long) As Long
#End If

Sub RemoveTitleBar(frm As Object)
    Dim lStyle          As Long
    Dim hMenu           As Long
    Dim mhWndForm       As Long

    If Val(Application.Version) < 9 Then
        mhWndForm = FindWindow("ThunderXFrame", frm.Caption) 'for Office 97 version
    Else
        mhWndForm = FindWindow("ThunderDFrame", frm.Caption) 'for office 2000 or above
    End If
    lStyle = GetWindowLong(mhWndForm, -16)
    lStyle = lStyle And Not &HC00000
    SetWindowLong mhWndForm, -16, lStyle
    DrawMenuBar mhWndForm
End Sub

Private Sub TreeView1_Click()
    UserForm1.Label1 = TreeView1.SelectedItem
End Sub

Private Sub UserForm_Click()
    Unload Me
End Sub
Private Sub UserForm_Initialize()

    Call RemoveTitleBar(Me)

    With Me
        .StartUpPosition = 0
        .Top = UserForm1.Top + (UserForm1.Height - UserForm1.InsideHeight) + UserForm1.Label1.Height + UserForm1.Label1.Top
        .Left = UserForm1.Left + (UserForm1.Width - UserForm1.InsideWidth) + UserForm1.Label1.Left
    End With

    TreeView1.Nodes.Add Key:="Item1", Text:="Parent 1"
    TreeView1.Nodes.Add Key:="Item2", Text:="Parent 2"
    TreeView1.Nodes.Add Key:="Item3", Text:="Parent 3"

    TreeView1.Nodes.Add "Item1", tvwChild, "one", "Item 1, Child node 1"
    TreeView1.Nodes.Add "Item1", tvwChild, "two", "Item 1, Child node 2"

    TreeView1.Nodes.Add "Item2", tvwChild, "three", "Item 2, Child node 1"
    TreeView1.Nodes.Add "Item2", tvwChild, "four", "Item 2, Child node 2"

    TreeView1.Nodes.Add "Item3", tvwChild, "five", "Item 3, Child node 1"
    TreeView1.Nodes.Add "Item3", tvwChild, "six", "Item 3, Child node 2"
End Sub

结果是:

单击UserForm2底部的灰色栏以关闭

Click on the grey bar at the bottom of UserForm2 to dismiss

您可以自己更多地使用它-这只是我先前评论的一个简单示例.看看将DropDown图片添加到Label

You can play around with this a lot more yourself - This is just a quick example of my previous comment. Have a look at adding a DropDown picture to the end of the Label

这篇关于如何创建“嵌套"广告素材? VBA组合框中的下拉列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！