How to double click a cell and add 1 to that cell value in Excel?

This article will show you method of add 1 to a cell value automatically with double clicking it in Excel.

Double click a cell and add 1 with VBA code

Double click a cell and add 1 with VBA code

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!

Save 50% of your time, and reduce thousands of mouse clicks for you every day!

The following VBA code can help you add 1 to a cell value after double clicking. Please do as follows.

1. Right click the sheet tab which the cell values you need to add 1 with double clicking. Then click View Code from the right-clicking menu.

2. In the popping up Microsoft Visual Basic for Applications window, copy and paste the below VBA code into the Code window.

VBA code: Add 1 to a cell value with double clicking

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    On Error Resume Next
    If Not Intersect(Target, Range("A1")) Is Nothing Then
      Range("A1").Value = Range("A1").Value + 1
      Cancel = True
    End If
End Sub

Note: In the code, A1 is the cell you will add number 1 to with double clicking.

3. Press the Alt + Q keys simultaneously to close the Microsoft Visual Basic for Applications window.

Now, double click on cell A1 will add 1 to the existing value as below screenshot shown.

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download... Purchase...

16 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

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.
OW · 16 days ago
What code would I you if I want to add 1 to multiple cells lets say E5:E15 but then add .5 or a different number to another group of cells like G5:515?
- Reply
To post as a guest, your comment is unpublished.
Stella · 8 months ago
I cannot get this to work in multiple cells
Tried using, but it did not work

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
If Not Intersect(Target, Range("B2:L14")) Is Nothing Then
Range("B2:L14").Value = Range("B2:L14").Value + 1
Cancel = True
End If
End Sub
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 7 months ago
  
  Hi Stella,
  Please apply the below VBA to solve the problem.
  
  Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  On Error Resume Next
  If Not Intersect(Target, Range("B2:L14")) Is Nothing Then
  Target.Value = Target.Value + 1
  Cancel = True
  End If
  End Sub
  
  Reply
To post as a guest, your comment is unpublished.
Bill · 2 years ago
If you want to increment any cell in a workbook cell by double clicking on that cell but only that cell, this code works:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
If Not Intersect(Target, Range(ActiveCell.Address)) Is Nothing Then
ActiveCell.Value = ActiveCell.Value + 1
Cancel = True
End If
End Sub
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Dear Bill,
  Thank you for sharing.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  BB · 2 years ago
  
  Thank You! that helped me a lot!
  
  Reply
To post as a guest, your comment is unpublished.
Steve · 2 years ago
This is great! How do I do this for multiple cells? I type in "A1,A2" instead "A1", it makes both cells equal and adds 1 to both cells simultaneously. I'd like the cells to act independently.
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Sorry I didn’t get your point.
  
  Reply
  
  To post as a guest, your comment is unpublished.
  Adam · 2 years ago
  
  Hello - agree with Steve this is great. I think what he would like (and me too) is to be able to add to the value of multiple different cells in the same tab by double clicking on them. He has amended the VBA formula so it says "A1,A2" instead of just "A1" however double clicking on "A1" adds 1 to both cells. Is it possible to change the formula so that multiple cells are included with the value of each only being increased when you click on it directly?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  laura · 2 years ago
  
  maybe by making a table with a range name. and updating the formula to include that?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  Steve · 2 years ago
  
  Basically, with the code above, if you double clicked on A1, it would add 1 to A1. If I changed A1 to A1:A2 in the Target Range, then if I double clicked on A1 or A2, it would add 1 to A1. What I wanted was to add 1 to A1 only if I double clicked on A1, and add 1 to A2 only if I double clicked on A2. I also did not want to add 1 to both cells.
  
  I think I have found what I was looking for:
  
  Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Range("D5:BC56")) Is Nothing Then
  Cancel = True
  Range(Target.Address).Value = Val(Range(Target.Address).Value) + 1
  End If
  End Sub
  
  Reply
  
  To post as a guest, your comment is unpublished.
  Sophie · 2 years ago
  
  This code does not seem to work for me. Don't I have to specify which is the Target.Adress ?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  crystal · 2 years ago
  
  Dear Steve,
  I got your point finally, and thank you for sharing.
  
  Reply
To post as a guest, your comment is unpublished.
Matt · 3 years ago
Can we add a two digit number to the end of the date? for example i need to double click and add the current date and the next two digit number (20171030-01 then the next cell will show 20171030-02 upon double click)
- Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Dear Matt,
  The code only supports the number format cell. It can't work as your data 20171030-01 is text format. Sorry about that.
  
  Reply
- To post as a guest, your comment is unpublished.
  crystal · 3 years ago
  
  Dear Matt,
  The code only supports the number format cell. It can work as your data 20171030-01 is a text format. Sorry about that.
  
  Reply