Run worksheet module from standard module

YasserKhalil
PlatinumLounger
Posts: 4582
Joined: 31 Aug 2016, 09:02

Run worksheet module from standard module

Post by YasserKhalil »

Hello everyone

I have a worksheet module in Sheet1
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

How can I run this worksheet module from a standard module?
I tried

Code: Select all

Application.Run "Sheet1.Worksheet_BeforeDoubleClick"
But this throws an error that the argument not optional. I tried to add the range and boolean value True as arguments but it displayed as red which refers to an error in syntax

YasserKhalil
PlatinumLounger
Posts: 4582
Joined: 31 Aug 2016, 09:02

Re: Run worksheet module from standard module

Post by YasserKhalil »

I even tried this
CallByName Sheet1, "Worksheet_BeforeDoubleClick", VbMethod, Range("K1"), True

Peter T
NewLounger
Posts: 8
Joined: 27 Dec 2021, 12:17

Re: Run worksheet module from standard module

Post by Peter T »

Change Private to Public and call in the normal way, eg
Dim bCancel As Boolean
Sheet1.Worksheet_BeforeDoubleClick ActiveCell, bCancel
Worksheets("Sheet1").Worksheet_BeforeDoubleClick ActiveCell, bCancel

Consider moving whatever you have in the BeforeDoubleClick stub to its own routine. and call it from the event as well as your normal module. If you move it to a normal module you'll need it qualify any cell references to Sheet1, otherwise they'll refer to the ActiveSheet which might not be Sheet1.

Peter T

YasserKhalil
PlatinumLounger
Posts: 4582
Joined: 31 Aug 2016, 09:02

Re: Run worksheet module from standard module

Post by YasserKhalil »

Thanks a lot for this working solution.

User avatar
Doc.AElstein
BronzeLounger
Posts: 1497
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Run worksheet module from standard module

Post by Doc.AElstein »

Hi Yasser,
YasserKhalil wrote:
15 Jan 2022, 09:24
... throws an error that the argument not optional. I tried to add the range and boolean value True as arguments but it displayed as red which refers to an error in syntax
I think the syntax on things like Application.Run can be a bit tricky, but it should work

Example:
This in your worksheet code module

Code: Select all

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 Let Target.Value2 = "This cell ya double clicked yaz"
End Sub
Then these sort of things should work in a normal code module to set that macro above off

Code: Select all

' https://eileenslounge.com/viewtopic.php?f=30&t=37685
Sub CallMeBeautifulPrivates()
Dim bCancel As Boolean
 Application.Run Macro:="'Sheet1.Worksheet_BeforeDoubleClick'", Arg1:=ActiveCell, Arg2:=bCancel
 Application.Run Macro:="      Sheet1.Worksheet_BeforeDoubleClick   ", Arg1:=ActiveCell, Arg2:=bCancel
 Application.Run Macro:="Sheet1.Worksheet_BeforeDoubleClick", Arg1:=ActiveCell, Arg2:=bCancel
 Application.Run Macro:="Sheet1.Worksheet_BeforeDoubleClick   ", Arg1:=ActiveCell, Arg2:=bCancel
 Application.Run "Sheet1.Worksheet_BeforeDoubleClick   ", ActiveCell, bCancel
 Application.Run Macro:="'Book1.xls'!Sheet1.Worksheet_BeforeDoubleClick   ", Arg1:=ActiveCell, Arg2:=bCancel
 Application.Run Macro:="'" & ThisWorkbook.Path & "\Book1.xls'!Sheet1.Worksheet_BeforeDoubleClick   ", Arg1:=ActiveCell, Arg2:=bCancel
 Application.Run Macro:="'" & ThisWorkbook.Path & "\Book1.xls'!          Sheet1.Worksheet_BeforeDoubleClick   ", Arg1:=ActiveCell, Arg2:=bCancel
 Application.Run Macro:="'" & ThisWorkbook.Path & "\Book1.xls'!'Sheet1.Worksheet_BeforeDoubleClick'", Arg1:=ActiveCell, Arg2:=bCancel
 Application.Run Macro:="'" & ThisWorkbook.Path & "\Book1.xls'!          'Sheet1.Worksheet_BeforeDoubleClick'", Arg1:=ActiveCell, Arg2:=bCancel

End Sub


' Ref
' https://excelfox.com/forum/showthread.php/2404-Notes-tests-Application-Run-OnTime-Multiple-Variable-Arguments-ByRef-ByVal?p=11870&viewfull=1#post11870
' https://stackoverflow.com/questions/31439866/multiple-variable-arguments-to-application-ontime
' https://web.archive.org/web/20180101022746/http://excelmatters.com/2017/04/07/passing-arguments-byref-using-run/#comment-205985
Alan
You do not have the required permissions to view the files attached to this post.
_ :cop: :moon: :cop:

YasserKhalil
PlatinumLounger
Posts: 4582
Joined: 31 Aug 2016, 09:02

Re: Run worksheet module from standard module

Post by YasserKhalil »

Amazing Mr. Alan. This is what I was searching for exactly as this keeps the worksheet module Private not Public.

User avatar
rory
5StarLounger
Posts: 749
Joined: 24 Jan 2010, 15:56

Re: Run worksheet module from standard module

Post by rory »

Why do you want to keep it private when you need to call it from elsewhere? It makes no sense to me. IMO, Peter's approach is the better one.
Regards,
Rory