Running a macro when double clicking a cell in another sheet

  • Dears,
    Good morning or good evening wherever you are.
    I am new with VBA. So, I had to search online on how to do what I wanted to do with my workbook. But now I am stuck and I hope you could help me.





    In sheet 1, there is a table of data.
    Each row has a site code and some data related to this site.
    I want to copy certain rows to sheet 4 based on a cell value containing the site code. So, I used the below code:







    In Sheet 4, the user enters a site code in cell B8, the number of occurrences for that site is generated in cell C8 using a COUNTIF function.



    What I need in my code is:
    1- When a user double clicks cell C8 in sheet 4, the macro CopyData starts copying the data from sheet 1 based on the site code entered in cell B8 in sheet4 without the need for an InputBox. I need the rows pasted in sheet 4 starting from row 13.
    2- If possible, when a row is copied from sheet 1, the first column of any row is not copied, which is column B as column A is already empty for the whole sheet.
    3- The code automatically clears previously copied data available in sheet 4 before copying new rows each time a new site code is entered.



    Please feel free to do modifications to my code or suggest a new one if my code is not good enough for the above mentioned tasks.





    I hope I was clear describing my problem.





    Thank you so much.


    Another link for my question: http://www.mrexcel.com/forum/e…er-sheet.html#post3902946

  • Re: Running a macro when double clicking a cell in another sheet


    To run a Macro when a cell is double clicked you can do the following....

    Code
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    
     If Not Intersect(Target, Range("C8")) Is Nothing Then 'Change The Range Here As Needed
      Call SomeSub 'Your Sub Name Here
     End If
     
    End Sub

    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Running a macro when double clicking a cell in another sheet


    Right-click on Sheet4's tab, View code, paste this in.
    PS. Thank's for stating the problem clearly, posting your code, AND providing a cross-link. A very well written post. :)

    Best Regards,
    Luke M
    =======
    "A little knowledge is a dangerous thing."

  • Re: Running a macro when double clicking a cell in another sheet


    Quote from Luke M;722867




    This is working perfectly :) Thank you so much.


    Thanks every one :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!