跳到主要內容

如何通過單擊單元格來更改單元格值？

我們可以通過在Excel中單擊一個單元格來更改單元格值嗎？ 例如，當您第一次單擊特定的單元格A1時，它會顯示一個文本“ Excel”，如果再次單擊該A1，則會顯示一個文本“ Word”，並且在您單擊該對話框時會顯示一個文本“ Outlook”電池在第三次。 在這種情況下，單元格A1中的值將從“ Excel”到“ Word”再到“ Outlook”再到“ Excel”……依次出現，如下所示：

通過單擊1更改文檔值

通過單擊帶有VBA代碼的單元格來更改單元格值

箭頭藍色右氣泡 通過單擊帶有VBA代碼的單元格來更改單元格值

要在Excel中完成此任務，以下VBA代碼可能會對您有所幫助，請執行以下操作：

1。 右鍵單擊要更改單元格值的工作表選項卡，然後選擇 查看代碼 從上下文菜單中，然後在打開的 Microsoft Visual Basic for Applications 窗口，將以下代碼複製並粘貼到空白模塊中：

VBA代碼：通過單擊一個單元格來更改單元格值：

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Updateby Extendoffice
  Application.EnableEvents = False
  With Target
  If .Address = Range("A1").Address Then
    Select Case .Value
      Case "Excel"
        .Value = "Word"
      Case "Word"
        .Value = "Outlook"
      Case "Outlook"
        .Value = "Excel"
      Case Else
        .Value = "Word"
    End Select
  End If
  End With
  Range("A2").Select
  Application.EnableEvents = True
End Sub

通過單擊2更改文檔值

備註：在上面的代碼中， A1 是您要通過單擊更改值的單元格，“Excel“”Word“”Outlook”是您要反复顯示的單元格值，您可以根據需要進行更改。

2。 粘貼代碼後，然後保存代碼並關閉窗口。 現在，當您第一次單擊單元格A1時，將立即顯示文本“ Excel”，再次單擊它，將出現文本“ Word”，如果您第三次單擊它，則會顯示文本“ Outlook”，看截圖：

通過單擊1更改文檔值

更多相關文章：

如何僅通過單擊Excel中的單元格內容來過濾數據？

如何通過單擊Excel中的標題對列數據進行排序？

最佳辦公生產力工具

🤖 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 (7)
No ratings yet. Be the first to rate!
Tanner
Tanner
This comment was minimized by the moderator on the site
This is great information, thanks for everyone's input! Is there away to apply this same type of approach with a lookup or index\match value?

For example, if I click on a value in A1, can the VBA be configured to index that value in another worksheet column, and display a matched value in B1? Thank you again!
Reply
0
0
JGoode
JGoode
This comment was minimized by the moderator on the site
https://www.extendoffice.com/documents/excel/3573-excel-type-in-cell-without-double-clicking.html
Reply
0
0
Zack
Zack
This comment was minimized by the moderator on the site
I am trying to apply this to an entire column of individual cells, not just one cell. Is this possible? When I change the range from "A1" to "A1:A100" nothing happens when I click the cells in that range.
Reply
0
0
kfairhurst
kfairhurst     Zack
This comment was minimized by the moderator on the site
This is the final code that worked for me.  I use it to check a cell when an item is completed.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.EnableEvents = False
    With Target
      Dim KeyCells As Range
      Set KeyCells = Range("D6:D8000")
      If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        Select Case .Value
          Case "ü"
            .Value = ""
          Case ""
            .Value = "ü"
        End Select
      End If
    End With
  Application.EnableEvents = True
End Sub
Reply
Report
0
0
Kalle
Kalle     Zack
This comment was minimized by the moderator on the site
You can do that by declaring the range you want to have as KeyCells and than put that into the Application.Intersection Method. This will let you change the Value of your choice from all the cells. Worked for me but i am bad at explaining ^^


Application.EnableEvents = False
With Target


Dim KeyCells as Range

Set KeyCells = Range("A1:A100")

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

Select Case .Value

...
Reply
0
0
Kalle
Kalle     Zack
This comment was minimized by the moderator on the site
You can do that by declaring the range you want to have as KeyCells and than put that into the Application.Intersection Method. This will let you change the Value of your choice from all the cells. Worked for me but i am bad at explaining ^^

Dim KeyCells as Range

Set KeyCells = Range("A1:A100")

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

Select Case .Value

...
Reply
0
0
Paji Shoitan
Paji Shoitan     Zack
This comment was minimized by the moderator on the site
I did it but i will not tell anyone MUWAHAHAHAHAHAHAHAHAHA
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations