On Cell click, start macro

  • Thread starter Thread starter camcrazy08
  • Start date Start date
C

camcrazy08

Here is what I am looking to do. I need to make this macro where, when I
click on a cell in column B on sheet one, it would copy the contents of that
cell, and insert the value into sheet two in Range("B4"), then call the macro
Searchforstring. How can I do this? Here is what I have so far...

Sub SearchandFind()

On Cell Click

ActiveCell.Copy
Sheets("Sheet2").Activate
Range("B4").PasteSpecial xlPasteValues

Call SearchForString

End Sub

Obviously the "On Cell Click" is incorrect. Does anyone have any possible
idea as to how I can complete this? Thank you for any help that you can be!

In case anyone is wondering or want to see the SearchForString vba (for
incorporation into this macro, or to tell me how inefficient this macro is,
or suggestions on another way to accomplish this):

Sub SearchForString()

Profit = Range("B4")

Sheets("Sheet2").Select
Range("A2:K100").ClearContents

Sheets("$1k Detail").Select

Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

'Start search in row 2
LSearchRow = 2

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

While Len(Range("B" & CStr(LSearchRow)).Value) > 0

'If value in column B = Range "Profit", copy entire row to Sheet1
If Range("B" & CStr(LSearchRow)).Value = Profit Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet2 in next row
Sheets("Sheet2").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("$1k Detail").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Sheets("Sheet2").Select
Range("A2").Select

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub


THANK YOU!
 
How about a double click event? Right click sheet tab>view code>insert this.
Now when you DOUBLE click on cell b4 cell b4 in sheet 2 will be populated.
If you really want it to be automatic use a worksheet_change event instead
which will just do it automatically when you change the value in cell b4 or
the source sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Address <> "$B$4" Then Exit Sub
Sheets("sheet2").Range("b4") = Target
'call mymacroname
End Sub
For the rest of your code may I suggest you look in the vba help index for
FINDNEXT instead. Do NOT use selections where not necessary.
 
Don,

Thank you for your time. This is exactly what I wanted to do, so you have
been a great help.

Robert

Don said:
How about a double click event? Right click sheet tab>view code>insert this.
Now when you DOUBLE click on cell b4 cell b4 in sheet 2 will be populated.
If you really want it to be automatic use a worksheet_change event instead
which will just do it automatically when you change the value in cell b4 or
the source sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Address <> "$B$4" Then Exit Sub
Sheets("sheet2").Range("b4") = Target
'call mymacroname
End Sub
For the rest of your code may I suggest you look in the vba help index for
FINDNEXT instead. Do NOT use selections where not necessary.
Here is what I am looking to do. I need to make this macro where, when I
click on a cell in column B on sheet one, it would copy the contents of
Click to expand...
[quoted text clipped - 83 lines]
THANK YOU!
Click to expand...
Click to expand...
 
Glad to help. Post your improved 2nd macro using FINDNEXT

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
camcrazy08 said:
Don,

Thank you for your time. This is exactly what I wanted to do, so you have
been a great help.

Robert

Don said:
How about a double click event? Right click sheet tab>view code>insert
this.
Now when you DOUBLE click on cell b4 cell b4 in sheet 2 will be populated.
If you really want it to be automatic use a worksheet_change event instead
which will just do it automatically when you change the value in cell b4
or
the source sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Address <> "$B$4" Then Exit Sub
Sheets("sheet2").Range("b4") = Target
'call mymacroname
End Sub
For the rest of your code may I suggest you look in the vba help index for
FINDNEXT instead. Do NOT use selections where not necessary.
Here is what I am looking to do. I need to make this macro where, when I
click on a cell in column B on sheet one, it would copy the contents of
Click to expand...
[quoted text clipped - 83 lines]
THANK YOU!
Click to expand...
Click to expand...
Click to expand...
 
I am going to have to teach myself FINDNEXT, but as soon as I get that down
and change the macro I will change it here.

I am always wanting to build my vba base, so thank you for your input, as it
gives me a new direction to learn in vba.

Don said:
Glad to help. Post your improved 2nd macro using FINDNEXT
Don,
Click to expand...
[quoted text clipped - 25 lines]
Click to expand...
 
Back
Top