在VBA Excel中加载用户窗体时如何显示进度栏

问题描述

我已经使用Userform创建了Macro，它具有许多本质上是静态的控件，并在用户窗体初始化时显示.但是它也具有(用户窗体初始化代码)代码，该代码使用sheet1中的数据动态地在其中一个框架中添加了复选框.这需要一些时间(例如30秒至1分钟)，具体取决于工作表中的数据.

I have created Macro using Userform and it has many controls which are Static in nature and displays upon userform intialization. But it also has (Userform initialize code) code written add checkboxes in one of the frame dynamically using the data in the sheet1. which is taking a bit of time(say 30 sec-1 min) depending on the data present in the sheet.

在此期间，我希望向用户显示进度完成百分比.

during this period i want to user to be shown a progress bar of % completion.

我尝试了Application.Statusbar功能，但是没有锻炼.所以想去Progressbar.有人可以在这方面提供帮助吗?

I tried Application.Statusbar functionality but it didnt workout. So thought to go for Progressbar. Can anyone please help in this regard?

推荐答案

这是我最近五年或六年来使用的进度条(最初发布于 http://www.mrexcel.com/forum/excel-questions/527468-progress-bar.html ).

This is the progress bar I've used for the last five or six years (originally posted in http://www.mrexcel.com/forum/excel-questions/527468-progress-bar.html).

不过，如果您要创建数百个控件，我会遵循Rorys的建议，并使用一个列表框.

I'd follow Rorys advice though and use a listbox if you're creating potentially hundreds of controls.

创建一个名为"进度栏"的表单

Create a form called 'Progress Bar'

赋予这些尺寸:
名称:ProgressBar
高度:49.5
宽度:483.75
ShowModal:错误< ----此位重要，否则将无法正确更新.

Give it these dimensions:
Name: ProgressBar
Height: 49.5
Width: 483.75
ShowModal: False <---- Important this bit or it won't update properly.

使用以下尺寸向表单添加标签:
名称:BoxProgress
标题:BoxProgress
高度:18
左:6
顶部:6
宽度:468
BackColour:& H008080FF&

Add a label to the form with these dimensions:
Name: BoxProgress
Caption: BoxProgress
Height: 18
Left: 6
Top: 6
Width: 468
BackColour: &H008080FF&

在常规模块中添加以下过程:

Sub UpdateProgressBar(n As Long, m As Long, Optional DisplayText As String)
'// DarkSprout April08
'// Omit DisplayText to display progress percentage
On Error GoTo ERR_HANDLE

If n >= m Then
    ProgressBar.Hide
Else
    If ProgressBar.Visible = False Then ProgressBar.Show
    ProgressBar![BoxProgress].Caption = IIf(DisplayText = "", Round(((n / m) * 10000) / 100) & "%", DisplayText)
    ProgressBar![BoxProgress].Width = (n / m) * 468
    DoEvents
End If
Exit Sub

ERR_HANDLE:
    Err.Clear
    ProgressBar.Hide
End Sub

像下面这样在您的代码中使用它:

Sub test()

    Dim x As Long

    For x = 1 To 100
        UpdateProgressBar x, 100
    Next x

End Sub

每次要更新进度条时，都需要调用该过程.

You'll need to call the procedure every time you want the progress bar to update.

变量:
m 表示条形将达到的最大数量， n 表示要显示的当前值.

The variables:
m represents the maximum number the bar will reach and n represents the current value to display.

这篇关于在VBA Excel中加载用户窗体时如何显示进度栏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！