Multiple events for double click action

[ilker11]

Hello everyone,

First post here.

I have a workbook consisting of two sheets, I want to be able to double click on a cell in column C to copy the data of that cell and also the data in column H and paste them in next empty row in sheet 2.

I managed to do just one with the following code but can't get it to work with both functions.

Any help is much appreciated.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("C2:C10")) Is Nothing Then
Cancel = True
Target.Copy
Sheets("INV").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If

Sheets("INV").Select

End Sub


I want it to also copy "H2:H10" to next empty row (B) in "INV"

[zzzeddy]

Hi

Welcome to the Forum.

Try this

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

If Not Intersect(Target, Range("C2:C10")) Is Nothing Then
Cancel = True
r = Target.Row
zValue1 = Target.Value
zValue2 = Cells(r, "H")

k = Sheets("INV").Range("A" & Rows.Count).End(xlUp).Row
Sheets("INV").Cells(k + 1, "A") = zValue1
Sheets("INV").Cells(k + 1, "B") = zValue2
End If

Sheets("INV").Select

End Sub

zeddy
P.S. better wrap your posted code with tags before you get caught and admonished

[ilker11]

zeddy
P.S. better wrap your posted code with tags before you get caught and admonished

I don't quite follow what you mean by this.

Thanks for the answer, it works perfect. Is it also possible to deduce the stock level by "1" which is in column "I"?

Many thanks for your help.

[Greg M]

Hi there,

See if the following code does what you need:

Option Explicit


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

    Const sOTHER_COLUMN As String = "H"

    Dim vDataValue_1    As Variant
    Dim vDataValue_2    As Variant

    If Not Intersect(Target, Range("C2:C10")) Is Nothing Then

        vDataValue_1 = Target.Value

        vDataValue_2 = Intersect(Target.EntireRow, _
                                 Me.Columns(sOTHER_COLUMN)).Value

        With Sheets("INV").Range("A" & Rows.Count).End(xlUp).Offset(1)

            .Cells(1, 1).Value = vDataValue_1
            .Cells(1, 2).Value = vDataValue_2

            .Parent.Select

        End With

        Cancel = True

    End If

End Sub

The highlighted value may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[ilker11]

This also works as intended.

Many thanks

[ilker11]

Hi there,

See if the following code does what you need:

Option Explicit


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

    Const sOTHER_COLUMN As String = "H"

    Dim vDataValue_1    As Variant
    Dim vDataValue_2    As Variant

    If Not Intersect(Target, Range("C2:C10")) Is Nothing Then

        vDataValue_1 = Target.Value

        vDataValue_2 = Intersect(Target.EntireRow, _
                                 Me.Columns(sOTHER_COLUMN)).Value

        With Sheets("INV").Range("A" & Rows.Count).End(xlUp).Offset(1)

            .Cells(1, 1).Value = vDataValue_1
            .Cells(1, 2).Value = vDataValue_2

            .Parent.Select

        End With

        Cancel = True

    End If

End Sub

The highlighted value may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

This also works as intended.

Many thanks

[Greg M]

Hi again,

Many thanks for your very prompt feedback.

You're welcome - glad I was able to help.

Regards,

Greg M

[ilker11]

Hi again,

Many thanks for your very prompt feedback.

You're welcome - glad I was able to help.

Regards,

Greg M

Dear Greg,

Is it possible to add another function?
I'd like to deduct stock quantity by 1 in column "I"

many thanks

[Greg M]

Hi there,

I think the following code should do what you need:

Option Explicit


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

    Const sSTOCK_COLUMN As String = "I"
    Const sOTHER_COLUMN As String = "H"

    Dim vDataValue_1    As Variant
    Dim vDataValue_2    As Variant
    Dim rStockCell      As Range

    If Not Intersect(Target, Range("C2:C10")) Is Nothing Then

        vDataValue_1 = Target.Value

        vDataValue_2 = Intersect(Target.EntireRow, _
                                 Me.Columns(sOTHER_COLUMN)).Value

        Set rStockCell = Intersect(Target.EntireRow, _
                                   Me.Columns(sSTOCK_COLUMN))

        rStockCell.Value = rStockCell.Value - 1

        With Sheets("INV").Range("A" & Rows.Count).End(xlUp).Offset(1)

            .Cells(1, 1).Value = vDataValue_1
            .Cells(1, 2).Value = vDataValue_2

            .Parent.Select

        End With

        Cancel = True

    End If

End Sub

The highlighted values may be altered to suit your requirements.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

[ilker11]

Hi there,

I think the following code should do what you need:

Option Explicit


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

    Const sSTOCK_COLUMN As String = "I"
    Const sOTHER_COLUMN As String = "H"

    Dim vDataValue_1    As Variant
    Dim vDataValue_2    As Variant
    Dim rStockCell      As Range

    If Not Intersect(Target, Range("C2:C10")) Is Nothing Then

        vDataValue_1 = Target.Value

        vDataValue_2 = Intersect(Target.EntireRow, _
                                 Me.Columns(sOTHER_COLUMN)).Value

        Set rStockCell = Intersect(Target.EntireRow, _
                                   Me.Columns(sSTOCK_COLUMN))

        rStockCell.Value = rStockCell.Value - 1

        With Sheets("INV").Range("A" & Rows.Count).End(xlUp).Offset(1)

            .Cells(1, 1).Value = vDataValue_1
            .Cells(1, 2).Value = vDataValue_2

            .Parent.Select

        End With

        Cancel = True

    End If

End Sub

The highlighted values may be altered to suit your requirements.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

This works perfect, thank you.

One last question if you don't mind.

Instead of integrating this code to change quantity with the double click action, is it possible to link qty column on the invoice to the inventory sheet?

For example, I double click on the product which transfers it to the invoice and then when update the qty on the invoice it automatically updates the qty on the stock list for that specific product.

Many thanks