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

[zhead]

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

>>> use code - tags <<<

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]

 

[vletm]

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]

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]

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]

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.

 

[vletm]

zhead
You can explain ...
Macro1 needs two parameters as well as Macro2,
without those - Macro1 and Macro2 do not know - what to do
- with or without call!

Calling Sub and Function procedures (VBA)

Office VBA reference topic

docs.microsoft.com

To call a Sub procedure from another procedure, type the name of the procedure and include values for any required arguments. The Call statement is not required, but if you use it, you must enclose any arguments in parentheses.