动态调整Excel VBA中组合框的宽度 [英] Dynamically adjusting the width of a combobox in Excel VBA

问题描述

我在努力设置一个组合框（在Excel VBA中），其宽度将根据其包含的最长字符串的长度自动调整。

I'm struggling to set up a combobox (in Excel VBA) in such a way that its width is automatically adjusted according to the length of the longest string that it contains.

我试图创建一个下拉列表（使用名为WorksheetSelectionForm的窗体中的组合框），一旦打开特定的工作簿，它就出现在屏幕上，并允许用户选择工作簿的工作表他们希望打开。

I'm trying to create a drop-down list (using a combobox in a form named "WorksheetSelectionForm") that, once a particular workbook is opened, appears on screen and allows the user to select which of the workbook's worksheets they wish to open.

我想要组合框的宽度调整到下拉列表中最长的文本字符串的长度。目前，我的下拉列表包含三个项目（工作簿中当前存在的工作表的名称）。他们是以下：

I want the width of the combobox to adjust to the length of the longest text string in the drop-down list. Currently my drop-down list contains three items (the names of the worksheets that currently exist in the workbook). They are the following:

• 损益帐户（23个字符）


• （13个字符）


• 现金流量报告（15个字符）

可以添加到工作簿，因此更多的项目添加到下拉列表，因此我不想简单地修复组合框的宽度在23点（当前是下拉列表中最长的字符串的长度） 。

More worksheets could be added to the workbook and hence more items added to the drop-down list, hence why I don't want to simply fix the combobox's width at 23 points (the length of what's currently the longest string in the drop-down list).

我一直在引用OzGrid的以下主题的想法（参见条目＃3）： http://www.ozgrid.com/forum/showthread.php?t=55098 。他们提出的解决方案如下：

I've been referring to the following thread from OzGrid for ideas (see entry #3): http://www.ozgrid.com/forum/showthread.php?t=55098. Their proposed solution is given below:

Dim iWidth As Double 
ComboBox1.AutoSize = True 
iWidth = 0 

For i = 0 To ComboBox1.ListCount - 1 
    ComboBox1.ListIndex = i 
    If iWidth < ComboBox1.Width Then 
        iWidth = ComboBox1.Width 
    End If 
Next 

ComboBox1.Width =  iWidth 
ComboBOx1.AutoSize = False 
ComboBox1.ListCount = 0 

此解决方案的问题是代码 ComboBox1 .Width 在if-then语句中实际上似乎没有解决在下一个循环中当前处于焦点的组合框项目的长度。

The problem with this solution is that the code ComboBox1.Width in the if-then statement doesn't actually seem to work out the length of the combobox item that's currently in focus in the for-next loop.

下面是我写的代码：

Private Sub Workbook_Open()

Dim Sheet As Worksheet, CmBox As MSForms.ComboBox, LWidth As Double, i As Integer
Set CmBox = WorksheetSelectionForm.ComboBox_Worksheets
LWidth = 0

'Populate the drop-down list with the names of the worksheets
For Each Sheet In Worksheets
    CmBox.AddItem Sheet.Name
Next Sheet

'Find out the length of the longest string in the combobox
For i = 0 To CmBox.ListCount - 1
    CmBox.ListIndex = i
    If Len(CmBox.Value) > LWidth Then
        LWidth = Len(CmBox.Value)
    End If
Next i

'Set the combobox's width to the length of the longest string in the combobox
CmBox.ListWidth = LWidth

'Show the form on screen
WorksheetSelectionForm.Show

End Sub

此代码在运行时似乎并未根据需要设置组合框的宽度。它还生成缺少其所有项目（工作表的名称）的组合框。

This code, when run, doesn't seem to be setting the combobox's width as desired. It also generates a combobox that's missing all of its items (the names of the worksheets). Where have I gone wrong?

以下是用户选择组合框中的项目时的代码（以防万一对您有用）：

Below is the code for when an item in the combobox is selected by the user (just in case it's of use to you):

Private Sub ComboBox_Worksheets_Change()

'Activate the worksheet whose name has been selected in the combobox
Sheets(ComboBox_Worksheets.Value).Activate

'Close the form
Unload WorksheetSelectionForm

End Sub

推荐答案

我使用你的代码作为开始基础，这是结果：

i used your code for a start base, and this is the outcome:

Private Sub Workbook_Open()

Dim Sheet As Worksheet, CmBox As MSForms.ComboBox, LWidth As Double, i As Integer
dim Wb as workbook
load WorksheetSelectionForm
with WorksheetSelectionForm
    Set CmBox = .ComboBox_Worksheets
    'LWidth = 0

    'Populate the drop-down list with the names of the worksheets
    with cmBox
        .clear
        for each Wb in workbooks
            For Each Sheet In WB.Worksheets 'i wasn't sure your way works for filling the list, did you verify it ?, so i do it my way
                h = Sheet.Name
                .AddItem h
                if len(h)>Lwidth then LWidth = Len(h) 'no need to loop again when list is full
            Next Sheet
        next Wb
    end with

    'Find out the length of the longest string in the combobox
    'For i = 0 To CmBox.ListCount - 1
    '    tmp_Length = len(CmBox.List(i))    'this is an other way of doing it, without changing the cmBox value (could trigger events)
    '    If tmp_Length > LWidth Then
    '        LWidth = tmp_Length
    '    End If
    'Next i

    'Set the combobox's List's width to the length of the longest string in the combobox
    CmBox.ListWidth = LWidth*8 'according to the list's Text Font size , you will need to adjust the *8

    'Show the form on screen
    .Show
end with

结束子

这篇关于动态调整Excel VBA中组合框的宽度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！