当前位置:网站首页>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)
边栏推荐
- 【Software Exam System Architect】Case Analysis ⑥ Web Application System Architecture Design
- keepalived:常见问题
- win下的开发环境变量记录
- 支付 x 聚合 x 分账 - 回流平台“二清”风险规避之路
- 「数据架构」:主数据管理(MDM)对我的行业有什么帮助?
- Which is the strongest workflow engine for "Technology Selection"?Chief Architecture Helps You Pick
- DataStream API(基础篇) 完整使用 (第五章)
- 中国驻越南使馆提醒在越北部、中部地区中国公民做好台风“木兰”安全防范
- 【Software Exam System Architect】System Reliability Analysis and Design ① System Reliability Analysis
- 【微服务架构】为故障设计微服务架构
猜你喜欢
Guo Jingjing's personal chess teaching, the good guy is a robot
Numpy学习
FPGA中BEL Site Tile FSR SLR分别指什么?
shell iterates over folders and outputs
[Metaverse Omi Says] See how UCOUCO integrates performance art into the Metaverse
CAD to WPF: Tips on converting CAD drawing files to WPF vector code files (xaml files)
Tencent releases the second-generation version of the quadruped robot Max, which completes jumps and somersaults on the plum blossom pile
CatchAdmin实战教程(四)Table组件之自定义基础页面
裸辞→自我放松→闭关→复习→斩获Offer
Development environment variable record under win
随机推荐
FPGA时钟篇(一) 7系列的时钟结构
[System Design] S3 Object Storage
shell iterates over folders and outputs
CentOS和Ubantu的Mysql主从配置
Property animation QPropertyAnimation
【Software Exam System Architect】System Reliability Analysis and Design ① System Reliability Analysis
俄罗斯宣布临时禁止进口摩尔多瓦植物产品
"Guangzhou highway engineering measures for the supervision and administration of production safety, and revised from six aspects
武功修炼:招式
Fourier series and Fourier transform
shell------ commonly used gadgets, sort, uniq, tr, cut
【软考 系统架构设计师】案例分析⑥ Web应用系统架构设计
Plot temperature curves; QChart,
10 【异步组件 组合式函数(hooks)】
Optimistic and pessimistic locking
初识Flink 完整使用 (第一章)
CSDN 21 Days Learning Challenge - Polymorphism (05)
How to use [jmeter regular expression extractor] to solve the problem of returning the value as a parameter
FPGA的虚拟时钟如何使用?
Tencent releases the second-generation version of the quadruped robot Max, which completes jumps and somersaults on the plum blossom pile