Implement a macro (increase cell +1 by right click) for Merge Cells

[itommy14]

Hi, I'm new on this forum
I've got a problem with a macro used for increasing cells by 1 with the right click of the mouse

This is the macro:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A1:Z100")) Is Nothing Then
Cancel = True
Target = Target + 1
End If

End Sub

Unfortunately it doesn't work with merge cells. How can I do?
Thank you

 

[Rijnsent]

Hi itommy14,
it took some messing around, but I found some help on Stackoverflow and this seems to work. The code is commented so you know what's happening.
Cheers,
Koen

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

'http://stackoverflow.com/questions/14167481/check-if-merged-cells-are-blank
If Target.Cells.Count > 1 Then
    If IsNull(Target.MergeCells) Then
        'Changed Range contains one or more Merged cells and other cells
        Exit Sub
    Else
        If Target.MergeCells Then
            'Changed Range is a single Merged cell
        Else
            'Changed Range is Unmerged cells only
            Exit Sub
        End If
    End If
Else
    'Continue, one cell selected
End If

If Not Intersect(Target(1, 1), Range("A1:Z100")) Is Nothing Then
    Cancel = True
    Target(1, 1) = Target(1, 1) + 1
End If

End Sub

 

[itommy14]

It works very well! Thank you very much!

 

[itommy14]

Hello how can i do to increase cell value by one in another sheet?
For example if A1 in sheet1 is selected and I press a button I want that cell B2 in sheet 2 in increased by 1
Thank you

 

[Rijnsent]

Hi itommy,
squeezing into your previous code:

If Not Intersect(Target(1, 1), Range("A1:Z100")) Is Nothing Then
    Cancel = True
    Target(1, 1) = Target(1, 1) + 1
    rw = Target(1,1).Row
    col = Target(1,1).Column
    Worksheets("Sheet2").Cells(rw+1,col).value = Worksheets("Sheet2").Cells(rw+1,col).value + 1

End If

The last bit is the one finding the row and column of your selected cell and add one to the same cell one row down on the Sheet2 worksheet.

Cheers,

Koen