当前位置:网站首页>VBA: 采用Combox控件实现二级下拉菜单功能
VBA: 采用Combox控件实现二级下拉菜单功能
2022-08-10 09:40:00 【用户9949496】
文章背景:在使用VBA的用户窗体(userform)时,有时会用到二级下拉菜单
。比如选择院系(一级下拉菜单)后,班级(二级下拉菜单)的内容自动更新;选择省份后,该省份下面所属的市相应更新。接下来以省市为例,进行二级下拉菜单的功能实现。
表1的数据如下:
用户窗体的内容如下:
省份combox的名称
取为provinceselect
;城市combox的名称
取为cityceselect
。
用户窗体内的VBA代码如下:
Option Explicit
Private Sub provinceselect_Change()
'Populate city data
Dim i As Integer, j As Integer, nrow As Integer
Sheets("Sheet1").Select
'Clear old data, if exists
UserForm1.cityselect.Clear
nrow = Range("A65535").End(xlUp).Row
For i = 1 To nrow
j = 1
If Range("A1:A" & nrow).Cells(i, 1) = UserForm1.provinceselect.Text Then
Range("A" & i).Select
Do While Not IsEmpty(ActiveCell.Offset(j, 0))
UserForm1.cityselect.AddItem ActiveCell.Offset(j, 0)
j = j + 1
Loop
UserForm1.cityselect.Text = ActiveCell.Offset(1, 0)
Exit For
End If
Next i
End Sub
Private Sub UserForm_Initialize()
'Populate Provinces
Dim ncategories As Integer, i As Integer
Sheets("Sheet1").Select
ncategories = WorksheetFunction.CountA(Columns("C:C"))
For i = 1 To ncategories
UserForm1.provinceselect.AddItem Range("C1:C" & ncategories).Cells(i, 1)
Next i
UserForm1.provinceselect.Text = Range("C1").Value
End Sub
窗体初始化时,将省份的数据填入;当省份的选项发生变化时,城市的信息也做相应的修改。
代码运行过程如下:
http://mpvideo.qpic.cn/0bf2pmcokaaejuadhv3sv5pvi66d4v5qjzia.f10002.mp4?
参考资料:
[1] Coursera课程(Excel/VBA for Creative Problem Solving, Part 3)
边栏推荐
猜你喜欢
Message Queuing Overview
Basic concepts, structures, and classes of thread pools
【API 管理】什么是 API 管理,为什么它很重要?
用高质量图像标注数据加速AI商业化落地
腾讯发布四足机器人 Max 二代版本,梅花桩上完成跳跃、空翻
多线程浅谈
英伟达游戏显卡营收暴跌/ 谷歌数据中心爆炸致3人受伤/ iPhone电量百分比回归…今日更多新鲜事在此...
2022 首期线下 Workshop!面向应用开发者们的数据应用体验日来了 | TiDB Workshop Day
关于判断单峰数组的几种方法
CatchAdmin实战教程(四)Table组件之自定义基础页面
随机推荐
JWT: To own me is to have power
【API架构】使用 JSON API 的好处
UE4 粒子特效基础学习 (01-将粒子效果挂载到角色身上)
IDEA中xml文件头报错:URI is not registered (Settings | Languages & Frameworks | Schemas and DTDs)
keepalived:主备配置
DataStream API(基础篇) 完整使用 (第五章)
Plot temperature curves; QChart,
[Metaverse Omi Says] Listen to how Rabbit Fan Rabbit creates a new era of trendy play from virtual to reality
Defending risks with technology and escorting cloud native | Tongchuang Yongyi X Boyun held a joint product launch conference
"Data Architecture": How can master data management (MDM) help my industry?
腾讯云校园大使开始招募啦,内推名额和奖金等你来拿
CentOS和Ubantu的Mysql主从配置
JVM探究
UE4 Sequence添加基础动画效果 (04-在序列中使用粒子效果)
DeepFake换脸诈骗怎么破?让他侧个身
日期类(暑假每日一题 19)
Which is the strongest workflow engine for "Technology Selection"?Chief Architecture Helps You Pick
FPGA的虚拟时钟如何使用?
keepalived:双主配置
Nvidia's gaming graphics card revenue plummets / Google data center explosion injures 3 people / iPhone battery percentage returns... More news today is here...