跳到主要内容

如何在Excel中增加下拉列表字体大小?

doc增加字体大小下拉列表1

下拉列表是Excel中的一项非常有用的功能,当您在工作表中创建下拉列表时,您是否曾尝试增加下拉列表的字体大小以使所选记录更大,更易读,如左图所示? 本文将讨论一种解决方法。

使用VBA代码增加下拉列表字体大小以扩大缩放比例


使用VBA代码增加下拉列表字体大小以扩大缩放比例

实际上,我们没有直接方法来增加下拉列表的字体大小,但是,您可以应用以下VBA代码来更改下拉列表单元格的缩放设置,以使文本显得更大。 请执行以下操作:

1. 转到包含您要增加其字体大小的下拉列表的工作表。

2。 右键单击工作表标签,然后选择 查看代码 从上下文菜单中,弹出 Microsoft Visual Basic应用程序 窗口,请复制以下代码并将其粘贴到空白处 模块 窗口:

VBA代码:增加下拉列表字体大小以放大单元格缩放

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'updateby Extendoffice
    On Error GoTo LZoom
    Dim xZoom As Long
    xZoom = 100
    If Target.Validation.Type = xlValidateList Then xZoom = 130
LZoom:
    ActiveWindow.Zoom = xZoom
End Sub

3. 然后保存并关闭此代码,现在,当您单击下拉列表单元格时,该单元格的缩放将被放大,并且下拉列表将显示为较大,但是,当您单击不包含该下拉列表的其他单元格时,单元格缩放将是正常的。

备注:在上面的代码中, x变焦= 130 会将下拉列表单元格的缩放比例更改为 130,您可以根据需要更改此缩放倍数。


在Excel中使用复选框创建下拉列表

如果您需要创建带有多个复选框的下拉列表,以便将列表中的多个项目选择到一个单元格中,则可能没有直接的方法来解决此任务。 但, Kutools for Excel 支持惊人的功能- 带有复选框的下拉列表,使用此功能,您可以根据需要快速创建带有指定范围,当前工作表,当前工作簿或所有打开的工作簿中的复选框的下拉列表。  点击下载Kutools for Excel!


演示:使用VBA代码增加下拉列表字体大小以放大缩放

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (29)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Thanks so much!
This comment was minimized by the moderator on the site
I have multiple drop down buttons. The VBA code was working fine at the first attempt. But i had to change the layout of the form requiring movement of all the cells. Now, all the drop down cells works except for one. I tried deleting and repasting the code. The problem persist, all cells works except for that one. Please help.
This comment was minimized by the moderator on the site
Ale po zamknięciu pliku i ponownym go otwarciu to rozwiązanie już nie działa.
This comment was minimized by the moderator on the site
OKI. Trzeba zapisać plik z obsługą makra.
This comment was minimized by the moderator on the site
Hello friend,
You are right. We need to save the macro-enabled file so the solution can work. Have a great day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Can we do this for multiple drop box? If not, then what is procedure for multiple drop box.
This comment was minimized by the moderator on the site
Ocurre al revés. Al dar clic en cualquiera celda se agranda, pero no en la lista desplegable y no vuelve al tamaño original. Uso Excel 2010. ¿Solución?It happens the other way around. Clicking on any cell enlarges it, but not the dropdown list and it does not return to the original size. I use Excel 2010. Solution?
This comment was minimized by the moderator on the site
Thank You for the tip to zoom the size of the font in the drop down box. It did the trick.
This comment was minimized by the moderator on the site
Hi, Just question i have pasted the code onto blank Module window (Right click in view code). Once i save, is function well that was good. But when i close the file and re-open it, the code was gone and no more enlarge for the drop down list. Can i know how to set for permanent use?
This comment was minimized by the moderator on the site
Hello, Ace,
To save the code, you should save the Excel file as Excel Macro-Enabled Workbook format. Please try it.
This comment was minimized by the moderator on the site
I have multiple dropdowns and only need one bigger as the rest are yes/no. But the code you shared is doing this for all of them. How do I only this to be used for one dropdown only?
This comment was minimized by the moderator on the site
Hello, Yvonne
To apply the code only to one cell, the following code may help you: (Note: in the code, change the cell I2 to your own cell reference.)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'updateby Extendoffice
    On Error GoTo LZoom
    Dim xZoom As Long
    Dim xRg As Range
    xZoom = 100
    Set xRg = Range("I2")
    If Not Intersect(Target, xRg) Is Nothing Then
        If Target.Validation.Type = xlValidateList Then xZoom = 130
    End If
LZoom:
    ActiveWindow.Zoom = xZoom
End Sub

Please try, hope it can help you!
This comment was minimized by the moderator on the site
How can I apply this font change size to only one column? I have several columns with drop-down boxes and I only want one specific column to change font size?
This comment was minimized by the moderator on the site
This zooms the whole sheet to 130%
How do I increase only the drop menu ?
This comment was minimized by the moderator on the site
Hello, Dave,
To only increase the drop down menu, here is a workaround to solve it.

First, after inserting the drop down list in your worksheet, then you should insert an Combo Box (ActiveX Controls),and then apply the following VBA code into the sheet module code:

Dim xRg As Range
Private Sub ComboBox1_Change()
On Error Resume Next
xRg.Value = Me.ComboBox1.Text
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo LblExit
With Me.ComboBox1
.Visible = False
If Target.Validation.Type = xlValidateList Then
Target.Validation.InCellDropdown = False
.ListWidth = 120
.ListFillRange = ""
.ListFillRange = Target.Validation.Formula1
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width
.Height = Target.Height
.Font.Size = 16
.Visible = True
Set xRg = Target
End If
End With
LblExit:
End Sub

After inserting the code, now when you click the drop down list cell, a combo box will be displayed, and font size in the drop down menu is increased as you need. And the inserted combo box will be hidden automatically.

Please try it, hope it can help you!
This comment was minimized by the moderator on the site
Can you explain it step by step for dummies pls?
This comment was minimized by the moderator on the site
Hello, ITALO,
1. Insert the drop down you need in your worksheet;
2. Then insert a Combo Box (ActiveX Controls) by clicking Developer > Insert > Combo Box (ActiveX Controls), and then drag the mouse to draw a Combo Box object;
3. And then, right click your current used worksheet, choose View Code, and paste the above code into the module;
4. At last, click Design Mode under the Developer tab to disable it.
5. Now when you click the drop down list cell, a combo box will be displayed, and font size in the drop down menu is increased as you need.
Please try, hope it can help you!
This comment was minimized by the moderator on the site
GENIUS! Thank you Skyyang! So grateful for your method! Works like a charm!
This comment was minimized by the moderator on the site
After using this code, I can't use return key on Excel. How can I code (install) return key?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations