跳到主要內容

單擊Excel中的特定單元格時如何彈出日曆?

假設工作表中有一個列範圍,您需要經常在其中輸入和更改日期,但是每次手動輸入或更改日期很無聊。 如何快速輸入日期而無需手動輸入列範圍? 本文討論的是單擊某個範圍內的單元格時彈出日曆,然後在日曆中選擇日期後將日期自動插入到選定的單元格中。


單擊帶有VBA代碼的特定單元格時彈出日曆

請按以下步驟解決此問題。

備註:此方法僅適用於32位Microsoft Excel。

步驟1:創建帶有日曆的用戶窗體

請創建一個用戶窗體,其中包含您將通過單擊一個單元格而彈出的日曆。

1。 按 其他 + F11 同時打開 Microsoft Visual Basic for Applications 窗口。

2。 在裡面 Microsoft Visual Basic for Applications 窗口中,單擊 插入 > 用戶窗體.

3.然後 用戶窗體工具箱 窗口彈出 工具箱,單擊任意一個控件,然後右鍵單擊,然後選擇 附加控制 從右鍵單擊菜單中。 看截圖:

4。 在裡面 附加控制 對話框中,向下滾動以檢查 Microsoft MonthView控件 在選項 可用控件 框,然後單擊 OK 按鈕。

5.然後您可以看到 月視圖 按鈕添加到 工具箱 窗口。 請單擊此MonthView按鈕,然後在UserForm1窗口上單擊以在用戶窗體中創建日曆。

備註:您可以通過拖動用戶窗體的邊框來調整用戶窗體窗口的大小以適合插入的日曆。

6.雙擊UserForm1中的插入日曆,然後在 推薦碼 窗口,請用以下VBA腳本替換原始代碼。

VBA code: create a user form with calendar

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
 On Error Resume Next
   Dim xRg As Object
   For Each xRg In Selection.Cells
      xRg.Value = DateClicked
   Next xRg 
   Unload Me
End Sub

備註:此代碼可以幫助在從日曆中選擇日期後將日期插入到選定的單元格中。

步驟2:在單元格上單擊時激活日曆

現在,您需要指定某些單元格才能在單擊時彈出日曆。 請執行以下操作。

7.雙擊包含要單擊的單元格的工作表名稱,以在左側彈出日曆 專案 窗格,然後將下面的VBA代碼複製並粘貼到“代碼”窗口中。 看截圖:

VBA code: Click cell to pop up calendar

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If (Target.Count = 1) Then
    If Not Intersect(Target, Range("A2:A10")) Is Nothing Then UserForm1.Show
    End If
End Sub

備註:在代碼中,A2:A10是您要單擊以彈出日曆的單元格。 請根據需要更改單元格範圍。

8。 按 其他 + Q 同時關閉按鍵 Microsoft Visual Basic for Applications 窗口。

從現在開始,當單擊當前工作表中指定範圍內的任何單元格時,將彈出一個日曆,如下圖所示。 從日曆中選擇日期後,日期會自動插入到選定的單元格中。


單擊帶有VBA代碼的特定單元格時彈出日曆

本節介紹 日期選擇器 的效用 Excel的Kutools。 啟用此功能後,單擊日期單元格將彈出一個日曆,您可以輕鬆地用新日期替換現有日期。 請按照以下步驟應用此功能。

1。 點擊 庫工具 > 內容 > 啟用日期選擇器.

2.啟用此功能後,單擊日期單元格,日曆圖標將在該單元格的右側彈出。

3.單擊日曆圖標以打開 日期選擇器 對話框,然後單擊一個新日期以替換所選單元格中的日期。

筆記:

  • 撤消按鈕:單擊此按鈕可以撤消替換日期;
  • 關閉按鈕:單擊此按鈕可關閉“日期選擇器”對話框;
  • 此功能僅適用於包含日期的單元格。

  如果您想免費試用(30天)此實用程序, 請點擊下載,然後按照上述步驟進行操作。


相關文章:

最佳辦公生產力工具

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

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

kte選項卡201905


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

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (18)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I am not seeing the Microsoft MonthView Control in the listing, but it may be as Pete (#32847) mentioned that this will not work in Office 365 or 64-bit Microsoft Office.
This comment was minimized by the moderator on the site
Hi Teagan Caudle,
I searched and tried the methods suggested in google, but ultimately could not register the MonthView control in Microsoft 365.
Sorry for the inconvenience. Maybe you can check this out.
https://social.technet.microsoft.com/Forums/Azure/en-US/db3b4dff-aad7-4d88-87cc-8f3f117be550/microsoft-windows-common-controls-60-for-office-2016
This comment was minimized by the moderator on the site
There is no Microsoft MonthView Control listed (Office 365), and the directions here doesn't explain how one would get that control, so this is pretty much useless unless you're using an older version of Excel.
This comment was minimized by the moderator on the site
The calendar will show up but when I click on the date, the cell doesn't populate
This comment was minimized by the moderator on the site
Hi,
The code works well in my case. Which Excel version are you using?
This comment was minimized by the moderator on the site
Thank you so much! These directions were super useful :)
This comment was minimized by the moderator on the site
Hi, Is it possible to put the date picker pop-up for multiple column, as in my sheet I have "start date", "end date" and "agreement date". if yes then how?
This comment was minimized by the moderator on the site
Hi jeet,
Follow the steps and replace the range "A2:A10" in the second VBA code with your column range (such as C2:E2).
This comment was minimized by the moderator on the site
Salve il codice funziona benissimo, ma se volessi farlo funzionare anche su un altro foglio
This comment was minimized by the moderator on the site
if i try to select a row, the pop up will activate and the date appears in each cell in that row


how can i avoid this
This comment was minimized by the moderator on the site
Hi Sam,
The code has been updated in the article with the problem solving. Please have a try and thank you for your comment.
This comment was minimized by the moderator on the site
tarihi seçebiliyorum ama a1:a10 hücrelerine seçtiğim tarih eklenmiyor. teşekkür ederim
This comment was minimized by the moderator on the site
Hello everyone,

Can anyone tell me how to popup a calendar in a range of cells, but starting only from the cell right bellow a table header and down bellow in an excel column.


Thank you in advance.
This comment was minimized by the moderator on the site
Use i.e: Range("B6:C30")
This comment was minimized by the moderator on the site
Good Day,
Sorry I didn't got your question. Would be nice if you could provide screenshot of what you are trying to do.
This comment was minimized by the moderator on the site
Use i.e: Range("B6:C30")
This comment was minimized by the moderator on the site
i used these VBA codes and everthing's fine so far. The range is A2:A10 and calendar pops up when you select a cell into it. But if you mark row from 2 to 10 again the calendar pops again. It's the same with column "A" if you mark it, again the calendar pops. How should i proceed, in order to get the calendar only in the range i've defined?
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