Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to change cell value by clicking on the cell?

Is it possible for us to change cell value by clicking on a cell in Excel? For example, when you click a specific cell A1, the first time, it shows a text “Excel”, if you click the A1 again, a text “Word” is displayed, and a text “Outlook” is displayed when you click this cell in the third time. In this case, the value in cell A1 will be recurrent from “Excel” to “Word” to “Outlook” to “Excel” …… as following screenshot shown:

doc change value by click 1

Change cell value by clicking on a cell with VBA code


arrow blue right bubble Change cell value by clicking on a cell with VBA code


To finish this task in Excel, the following VBA code may help you, please do as follows:

1. Right click the sheet tab that you want to change cell value by clicking on, and choose View Code from the context menu, and in the opened Microsoft Visual Basic for Applications window, copy and paste the following code into the blank Module:

VBA code: Change cell value by clicking on a cell:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Updateby Extendoffice 20161123
  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

doc change value by click 2

Note: In the above code, A1 is the cell where you want to change value by clicking, “Excel”, “Word”, “Outlook” are the cell values you want to displayed recurrently, you can change them to your need.

2. After pasting the code, then save the code and close the window. Now, when you click the cell A1 at first time, the text “Excel” is displayed at once, click it again, the text “Word” is appeared, the text “Outlook” is shown if you click it at the third time, see screenshot:

doc change value by click 1


More related articles:

How to filter data just by clicking cell content in Excel?

How to sort column data by clicking on header in Excel?


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Zack · 1 years ago
    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.
    • To post as a guest, your comment is unpublished.
      Kalle · 14 days ago
      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

      ...
    • To post as a guest, your comment is unpublished.
      Kalle · 14 days ago
      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

      ...
    • To post as a guest, your comment is unpublished.
      Paji Shoitan · 3 months ago
      I did it but i will not tell anyone MUWAHAHAHAHAHAHAHAHAHA