跳到主要內容

如何在Excel工作表中創建秒錶?

如果工作表中有秒錶,您將知道完成工作的時間。 如何在帶有“開始”,“停止”和“重置”按鈕的工作表中創建秒錶,如以下屏幕截圖所示? 如果單擊“開始”按鈕,它將開始計時,如果您單擊“停止”按鈕,它將停止計時,而“重置”按鈕可以幫助您重置時間。 本文,我將討論如何在Excel中創建簡單方便的秒錶。

doc創建秒錶1

使用VBA代碼創建具有三個按鈕的秒錶


使用VBA代碼創建具有三個按鈕的秒錶

要創建帶有“開始”,“停止”和“重置”三個按鈕的秒錶,請一一進行以下步驟。

首先,插入三個命令按鈕。

1。 點擊 開發者 > 插入 > 命令按鈕,請參見屏幕截圖:

doc創建秒錶2

2。 然後拖動鼠標繪製一個按鈕,插入按鈕後,可以更改按鈕標題,請單擊 開發者 > 氟化鈉性能 氟化鈉性能 對話框中,輸入新的標題“開始”按鈕,位於旁邊的文本框中 標題,請參見屏幕截圖:

doc創建秒錶3 2 doc創建秒錶4

3。 重複上述兩個步驟,以插入其他兩個按鈕並將其標題為“停止“和”重設”,請參見屏幕截圖:

doc創建秒錶5

4。 插入按鈕後,應通過單擊退出設計模式 開發者 > 設計模式.

其次,創建一個VBA代碼。

5。 然後,右鍵單擊當前工作表選項卡,然後選擇 查看代碼,在彈出 Microsoft Visual Basic for Applications 窗口,請複制以下代碼並將其粘貼到 表碼:

VBA代碼:創建秒錶:

Public StopIt As Boolean
Public ResetIt As Boolean
Public LastTime
Private Sub CommandButton1_Click()
Dim StartTime, FinishTime, TotalTime, PauseTime
StopIt = False
ResetIt = False
If Range("C2") = 0 Then
  StartTime = Timer
  PauseTime = 0
  LastTime = 0
Else
  StartTime = 0
  PauseTime = Timer
End If
StartIt:
  DoEvents
  If StopIt = True Then
    LastTime = TotalTime
    Exit Sub
  Else
    FinishTime = Timer
    TotalTime = FinishTime - StartTime + LastTime - PauseTime
    TTime = TotalTime * 100
    HM = TTime Mod 100
    TTime = TTime \ 100
    hh = TTime \ 3600
    TTime = TTime Mod 3600
    MM = TTime \ 60
    SS = TTime Mod 60
    Range("C2").Value = Format(hh, "00") & ":" & Format(MM, "00") & ":" & Format(SS, "00") & "." & Format(HM, "00")
    If ResetIt = True Then
      Range("C2") = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
      LastTime = 0
      PauseTime = 0
      End
    End If
    GoTo StartIt
  End If
End Sub
Private Sub CommandButton2_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  StopIt = True
End Sub
Private Sub CommandButton3_Click()
  Range("C2").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
  LastTime = 0
  ResetIt = True
End Sub

doc創建秒錶6

備註:在上面的代碼中, C2 是要插入秒錶時間的單元格,並且 命令按鈕 1, 命令按鈕 2, 命令按鈕 3 是按鈕名稱,您可以從 名稱框,請參見屏幕截圖:

doc創建秒錶7

第三,格式化秒錶時間單元。

6。 然後,您應該將時間單元格格式設置為 文本 格式,您可以根據需要更改單元格大小,字體,字體顏色,背景等,請參見屏幕截圖:

doc創建秒錶8

7。 完成上述步驟後,從現在開始,當您單擊 開始 按鈕,時間將從現在開始,然後單擊 停止 按鈕,它將停止時間, 重設 按鈕將重置時間,請參見屏幕截圖:

doc創建秒錶9


演示:使用VBA代碼創建帶有三個按鈕的秒錶

最佳辦公生產力工具

🤖 Kutools 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (32)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
One has to paste the VBA code to the Sheet's VBA space, not to the Module!

=> I suggest correcting, in the article, the "copy and paste the following code into the Module" -> replace Module with Sheet's VBA field, or sth

Otherwise, perfect, thanks!
This comment was minimized by the moderator on the site
Hello, Florian,

Thanks for your comment, we have updated the content for this article.
Thanks again! 😀
This comment was minimized by the moderator on the site
i started. it works. but it doesn't work when i close and open the excel file again. what must i do?
This comment was minimized by the moderator on the site
Hello, mustafa zirek
After inputing the code, you should save the workbook as Excel Macro-Enabled Workbook format, and when you open the workbook next time, please click the Enable Content above the formula bar to activate the code.
Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Hi! Thank you for this, how do I make it so it populates time in subsequent cells, ex. start/stop time for cell C2, then a different start/stop time for cell c3, c4, c5 etc 
This comment was minimized by the moderator on the site
Bonjour, j'ai voulu intégrer ce chronomètre à une autre macro, déterminer le temps d'utilisation du fichier. La macro se lance mais bloque l'utilisation du fichier.Comment faire pour utiliser votre chronomètre en même temps que d'autres codes VBA?MerciHenry.
This comment was minimized by the moderator on the site
Is it possible to use a combobox or a dropdown selection to start and stop the clock
This comment was minimized by the moderator on the site
This is brilliant! Thanks, Chris H
This comment was minimized by the moderator on the site
Hello
Have followed the steps but cannot get a time to appear in the timer cell. I have copied the code but can i check1. That the line numbers are not needed or are they2. That I put the code into module 1 not sheet1 or This workbook3. Should there be a macro listed in the macro list after doing all this - mine doesn't.
Thanks
This comment was minimized by the moderator on the site
Hi, Janice,You should put the code into your active sheet module as step 5.
5. And then, right click the current worksheet tab, and choose View Code, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the Module.

And you should change the button name to your own.
Note: In the above code, C2 is the cell where the stopwatch time will be inserted, and the CommandButton1, CommandButton2, CommandButton3 are the button names, you can view the exact button name from the Name Box.

Please check them, thank you!
This comment was minimized by the moderator on the site
Is it possible to get the counter to count only seconds?
So when it reaches 59 seconds, it continues with 60, 61, 62, 63 ....
This comment was minimized by the moderator on the site
is 'timer' a variable here? dont see its relevance in the code.
This comment was minimized by the moderator on the site
How about adding "+10s" & "2x Speed" feature in this code?
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