• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

zhead

New Member
I am trying to run this sheet code and keep getting the error below. Any idea what I did wrong?

>>> use code - tags <<<
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     Call Macro1     'event runs when cell A9 is double click
     Call Macro2    'event runs when cell A10 is double click
End Sub
Private Sub Macro1(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$A$9" Then
Dim strCellName As String
Dim strReplace As String
strCellName = Target.Value

If InStr(1, strCellName, "@Para1") > 0 Then
strReplace = InputBox("Enter Floc like % OR % (Wildcard)")
If strReplace = "" Then
Cancel = True
Exit Sub
End If

strCellName = Replace(strCellName, "@Para1", strReplace)
End If
If InStr(1, strCellName, "@Para2") > 0 Then
strReplace = InputBox("Job Template like % OR % (Wildcard)")
If strReplace = "" Then
Cancel = True
Exit Sub
End If

strCellName = Replace(strCellName, "@Para2", strReplace)
End If


Call ExecSql(strCellName)
Cancel = True

End Sub
Private Sub Macro2(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$A$10" Then
Dim strCellName As String
Dim strReplace As String
strCellName = Target.Value

If InStr(1, strCellName, "@Para1") > 0 Then
strReplace = InputBox("Enter TTO like % OR % (Wildcard)")
If strReplace = "" Then
Cancel = True
Exit Sub
End If

strCellName = Replace(strCellName, "@Para1", strReplace)
End If
If InStr(1, strCellName, "@Para2") > 0 Then
strReplace = InputBox("Job Template like % OR % (Wildcard)")
If strReplace = "" Then
Cancel = True
Exit Sub
End If

strCellName = Replace(strCellName, "@Para2", strReplace)
End If


Call ExecSql(strCellName)
Cancel = True

End Sub
[code]
[ATTACH type="full" alt="71224"]71224[/ATTACH]
 

Attachments

Last edited by a moderator:

vletm

Excel Ninja
zhead
You Macro1 seems to need three parameters, which are missing ... for some reason?
... same feature with Macro2.

... Why do You have there Call before Macro1 and Macro2?
 

zhead

New Member
Vletm thanks for the quick reply.
I am not understanding when you say "You Macro1 seems to need three parameters, which are missing ... for some reason?"

I use call in other macros and figured I would try it here. But it didn't help
 

vletm

Excel Ninja
zhead
You Marco1 ... seems to need three OOOOH! sorry! two parameters as You could see below ...
Private Sub Macro1(ByVal Target As Range, Cancel As Boolean)

Call - okay You use to call macros ... but why?
 

zhead

New Member
vletm
Let me explain what I am trying to do. I have hundreds of excel sheets that connects to databases around the world and brings back the requested data via an SQL query. I am trying to combine two or more reports on one sheet. The private subs ask for prompts to narrow the data brought back. Each report has different prompts therefore I need to be able to call the correct prompts for the correct report.
 
Top