PDA

View Full Version : Running a macro when double clicking a cell in another sheet



MidoOnly
08-12-2014, 05:33 AM
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:






Sub CopyData()Dim myWord$
myWord = InputBox("Enter Site Code:", "Enter your word")
If myWord = "" Then Exit Sub




Application.ScreenUpdating = False
Dim xRow&, NextRow&, LastRow&
NextRow = 13
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For xRow = 1 To LastRow
If WorksheetFunction.CountIf(Rows(xRow), "*" & myWord & "*") > 0 Then
Rows(xRow).Copy Sheets("Sheet4").Rows(NextRow)
NextRow = NextRow + 1
End If
Next xRow
Application.ScreenUpdating = True




MsgBox "Site data is ready", 64, "Done"
End Sub





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.

westconn1
08-12-2014, 02:18 PM
1- When a user double clicks cell C8 in sheet 4,you only want this for sheet4 in specific workbook?
if so try putting the code into the before double click event for sheet 4
for all sheets in workbook look at the workbook sheetbefore doubleclick event