進度條範例(UserForm Lable)

by chijanzen on 七月 15th, 2008 | View: 5,371 views

Tags

Comments (5)

索引	H0004
主題	進度條範例(UserForm Lable)
版本	>= 8.0(Office 97)
說明	本範例提供用Label控件來製作進度條的範例
參考

Code

Sub UpdateProgress(Pct)
    With UserForm1
        .FrameProgress.Caption = Format(Pct, "0%")
        .LabelProgress.Width = Pct * (.FrameProgress.Width - 10)
        .Repaint
    End With
End Sub
　

Code

Private Declare Sub Sleep Lib "kernel32" ( _
        ByVal dwMilliseconds As Long)
    Dim strText1 As String
    Dim intI, i, j As Integer
　

Private Sub UserForm_activate()
    Me.Label1 = "程式執行中"
    With Application
        .EnableEvents = False

        .ScreenUpdating = False  '關閉復螢幕更新
    End With
    Dim strText1 As String
    Dim intI, i, j As Integer
    strText1 = Sheets("LL").ComboBox1.Text
        For i = 5 To 26
            rng(i).Copy Destination:=Sheets("LL").Cells(i, 2)
            K = K + 1
            DcPct = K / ((26 - 5) + 1)   '進度百分比
            Call UpdateProgress(DcPct)   '更新進度條百分比
        Next i
    Worksheets("SJ").Cells.AutoFilter   '關閉篩選
    With Application
        .EnableEvents = True
        .ScreenUpdating = True   '開啟螢幕更新
    End With
    Sleep 1000   '1秒

Unload Me
End Sub
　

Function rng(i) As Range
    With Worksheets("SJ")
        ctxt = Worksheets("LL").Cells(i, 1)
        '篩選公司
        .Cells.AutoFilter Field:=2, Criteria1:=ctxt
        strText1 = Sheets("LL").ComboBox1.Text
        '篩選月份
        .Cells.AutoFilter Field:=1, Criteria1:=strText1
        '指定範圍
        Set rng = .Range("C2:W" & .[A65536].End(xlUp).Row). _
                   SpecialCells(xlCellTypeVisible)
    End With
End Function
　

Help

Code

本範例的使用方法:請選取工作表中的月份"下拉式方塊"，程式碼就會自動執行

DownLoad

Code

Popularity: 20%

About the Author

網路化名: chijanzen、中國龍、邪兵衛經歷: 第二屆微軟社群之星第三屆微軟「最有價值專家」第五屆微軟「最有價值專家」

Leave a Comment »5 Comments

Reply » YVONNE 十二月 18, 2009

我想要在EXCEL裡使用網頁上KEYWORD的功能,是不是要用到巨集??..

我在EXCEL裡插入了一張圖片,然後在SHEET!A1的儲存格裡輸入關鍵字後,按那張圖片,可以搜尋整本活頁簿裡符合關鍵字的儲存格,以及自動跳到那個儲存格那一頁去...

請幫幫忙...謝謝
Reply » child 十一月 1, 2009

很謝謝老師的幫忙^^

非常謝謝您^^
Reply » chijanzen 十一月 1, 2009

你好:
將範例中Userform 程式碼全部刪除，如後再Module 中執行以下程式碼

Sub UpdateProgress(Pct)
With UserForm1
.FrameProgress.Caption = Format(Pct, "0%")
.LabelProgress.Width = Pct * (.FrameProgress.Width - 10)
.Repaint
End With
End Sub

Sub Data()
UserForm1.Show 0
Call RPPA
UpdateProgress (0.2)
Call Model
UpdateProgress (0.4)
Call Input1
UpdateProgress (0.6)
Call Macro2
UpdateProgress (0.8)
Call Macro5
UpdateProgress (1)
End Sub

Sub RPPA()
For i = 1 To 5000000
i = i + 1
Next
End Sub

Sub Model()
For i = 1 To 5000000
i = i + 1
Next
End Sub

Sub Input1()
For i = 1 To 5000000
i = i + 1
Next
End Sub

Sub Macro2()
For i = 1 To 50000
i = i + 1
Next
End Sub
Sub Macro5()
For i = 1 To 50000
i = i + 1
Next
End Sub
Reply » child 十月 31, 2009

不好意思!可以請教大大一個問題嗎?
如果想把範例修改成→ 在Module裡呼叫了數個寫好的程式，每執行完一個後顯示完成的比例，例:在下列Data執行完第一個RPPA的程式碼後，就顯示完成25%。
Sub Data()
Call RPPA
Call Model
Call Input1
Call Macro2
Call Macro5
End Sub

不知道如何下手?很謝謝您的幫忙，感激不盡!
Reply » Anikita 十一月 6, 2008

It has long been looking for this information, thank you.

Get a GravatarLeave a Reply

Blue Green Grey Orange Purple Red

chijanzen 雜貨舖

進度條範例(UserForm Lable)

Help

DownLoad