从Userform添加数据时显示进度条的实现需求

步骤1：创建进度条用户窗体 #

新建一个UserForm，命名为frmProgress，添加以下控件并设置属性：

• Frame控件（命名为fraProgress）：
  • BorderStyle = fmBorderStyleSingle
  • Width = 300，Height = 20
• Label控件（命名为lblStatus）：
  • BackColor = &H0000FF00&（可自行替换为喜欢的进度条颜色）
  • Width = 0，Height = 18
  • Caption = ""
  • Left = 1，Top = 1

在frmProgress的代码窗口中添加进度更新函数：

Public Sub UpdateProgress(currentVal As Integer, maxVal As Integer, statusText As String)
    Dim progressWidth As Integer
    ' 计算进度条宽度
    progressWidth = (currentVal / maxVal) * Me.fraProgress.Width
    ' 更新进度条显示
    Me.lblStatus.Width = progressWidth
    Me.Caption = statusText
    ' 强制刷新界面，确保进度实时同步
    DoEvents
End Sub

步骤2：修改原添加按钮代码 #

更新cmbAddItem_Click过程，嵌入进度条调用逻辑，将操作拆分为多个进度节点：

Private Sub cmbAddItem_Click()
    Dim sht As Worksheet
    Dim LastRow As Long
    Dim totalSteps As Integer
    Dim currentStep As Integer
    
    ' 初始化进度参数：总步骤数根据操作节点设定
    totalSteps = 7
    currentStep = 0
    
    ' 显示进度条窗体（非模态，不阻塞后续操作）
    Load frmProgress
    frmProgress.Show vbModeless
    frmProgress.UpdateProgress currentStep, totalSteps, "初始化工作表..."
    
    ' 步骤1：初始化工作表
    currentStep = currentStep + 1
    Set sht = ThisWorkbook.Sheets("ITEMS")
    frmProgress.UpdateProgress currentStep, totalSteps, "获取数据区域..."
    
    ' 步骤2：获取数据区域
    currentStep = currentStep + 1
    Dim rng As Range
    Set rng = sht.Range("ITEM_LIST").CurrentRegion
    LastRow = rng.End(xlDown).Row + 1
    frmProgress.UpdateProgress currentStep, totalSteps, "写入数据..."
    
    ' 步骤3：写入数据到工作表
    currentStep = currentStep + 1
    With sht
        .Cells(LastRow, 3) = lblItemId
        .Cells(LastRow, 4) = txtItemname
        .Cells(LastRow, 5) = lblDate
        .Cells(LastRow, 6) = txtItemDes
    End With
    frmProgress.UpdateProgress currentStep, totalSteps, "设置单元格格式..."
    
    ' 步骤4：设置单元格格式
    currentStep = currentStep + 1
    With sht
        .Range("ITEM_LIST").Rows(rng.Rows.Count).Font.Name = "Franklin Gothic Book"
        .Range("ITEM_LIST").Rows(rng.Rows.Count).HorizontalAlignment = xlCenter
    End With
    frmProgress.UpdateProgress currentStep, totalSteps, "刷新列表..."
    
    ' 步骤5：刷新列表框
    currentStep = currentStep + 1
    loaditemlist
    frmProgress.UpdateProgress currentStep, totalSteps, "更新项目编码..."
    
    ' 步骤6：更新项目编码与统计总数
    currentStep = currentStep + 1
    Call ItemCode ' 更新下一个项目的编码标签
    lblTotItem = Application.CountA(Range("ITEM_CODE")) ' 刷新总数显示
    frmProgress.UpdateProgress currentStep, totalSteps, "保存工作簿..."
    
    ' 步骤7：保存工作簿
    currentStep = currentStep + 1
    ThisWorkbook.Save
    frmProgress.UpdateProgress currentStep, totalSteps, "操作完成!"
    
    ' 关闭进度条并弹出成功提示
    Unload frmProgress
    MsgBox "数据添加成功!", vbInformation, "操作完成"
End Sub

关键说明 #

• 用vbModeless模式显示进度条，确保主操作执行时进度条能实时更新
• 每个操作节点后调用UpdateProgress，传入当前进度、总进度和状态文本
• DoEvents语句强制Excel刷新界面，避免进度条卡顿不显示
• 总步骤数可根据实际操作复杂度调整，让进度显示更精准

内容的提问来源于stack exchange，提问作者charly1988