Error in my Dbuble-Click Event Macro

[jim may]

I've set up a Sheet as follows: In a Standard Module I've created 9 Macros. Here's a sample of My Macro, say only Cat4:

Sub Cat4()
ActiveSheet.ListObjects("Table1").ListColumns(3).Range.Select
End Sub

I want to be able to Double-Click on Cell D7 (which includes the text Cat4) and With my Double-Click event below run it

My Single Event macro is currently:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim MyMacro As String
Cancel = True
MyMacro = Target.Value
Run MyMacro
End Sub

But I'm Getting a RT 1004 at line 5: Run MyMacro

Can this be "Repaired" so as to work?

TIA,

Jim


Excel 2010

	A	B	C	D	E	F	G	H	I	J	K
1		Selecting Areas Of A Table With VBA
2
3	#	Select	VBA Coding	MacroName
4	1	Entire Table	ActiveSheet.ListObjects("Table1").Range.Select	Cat1
5	2	Table Header Row	ActiveSheet.ListObjects("Table1").HeaderRowRange.Select	Cat2
6	3	Table Data	ActiveSheet.ListObjects("Table1").DataBodyRange.Select	Cat3
7	4	Third Column	ActiveSheet.ListObjects("Table1").ListColumns(3).Range.Select	Cat4
8	5	Third Column (Data Only)	ActiveSheet.ListObjects("Table1").ListColumns(3).DataBodyRange.Select	Cat5
9	6	Select Row 4 of Table Data	ActiveSheet.ListObjects("Table1").ListRows(4).Range.Select	Cat6
10	7	Select 3rd Heading	ActiveSheet.ListObjects("Table1").HeaderRowRange(3).Select	Cat7
11	8	Select Data point in Row 3, Column 2	ActiveSheet.ListObjects("Table1").DataBodyRange(3, 2).Select	Cat8
12	9	Subtotals	ActiveSheet.ListObjects("Table1").TotalsRowRange.Select	Cat9		Table1: (below)
13
14					FName	LName	Address	City	State	Zip	Bdate
15					Jim	May	Avenham	Roanoke	VA	24014	11/27/1944
16					Bruce	Cody	SandleRidge	Roanoke	VA	24018	7/15/1954
17					Bud	Vaughn	Rt 460	Roanoke	VA	24153	11/10/1945
18					Jack	Causey	Allendale	Statesville	NC	28147	5/23/1934
19					Jerry	White	Poplar Spr	Meridian	MS	39301	5/14/1945
20

<tbody>
</tbody>

Sheet1​

 

[Johnny C]

You could use a SELECT CASE instead with the required code for each case.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim MyMacro As String
Cancel = True
MyMacro = Target.Value
Select Case MyMacro
Case "Cat1"
     --code from Cat1 macro--
Case "Cat2"
     --code from Cat2 macro--
End Case
End Sub

Not as elegant or well structured but it would work

 

[MrKowz]

Jim,

The syntax appears correct. I was able to use the below testing code and it worked with no problems:

Public Sub test()
MsgBox "foo"
End Sub

Public Sub test2()
Dim str As String
str = "test"
Run str
End Sub

Does the problem still happen at the same location if you step through the code?

 

[jim may]

Johnny - THANKS!!

I now have the following in my Sheet1 Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim MyMacro
Cancel = True
MyMacro = Target.Value
Select Case MyMacro
    Case "Cat1"
        ActiveSheet.ListObjects("Table1").Range.Select
    Case "Cat2"
        ActiveSheet.ListObjects("Table1").HeaderRowRange.Select
    Case "Cat3"
        ActiveSheet.ListObjects("Table1").DataBodyRange.Select
    Case "Cat4"
        ActiveSheet.ListObjects("Table1").ListColumns(3).Range.Select
    Case "Cat5"
        ActiveSheet.ListObjects("Table1").ListColumns(3).DataBodyRange.Select
    Case "Cat6"
        ActiveSheet.ListObjects("Table1").ListRows(4).Range.Select
    Case "Cat7"
        ActiveSheet.ListObjects("Table1").HeaderRowRange(3).Select
    Case "Cat8"
        ActiveSheet.ListObjects("Table1").DataBodyRange(3, 2).Select
    Case "Cat9"
        ActiveSheet.ListObjects("Table1").TotalsRowRange.Select
    Case Else
    'Do Nothing
End Select
End Sub

On my Spreadsheet now Double-Clicking on My Cells D4 thru D11 (Cat1 tp Cat8) It Works!!! But when I double-click on cell D12 (Cat9) I get RT error 9:
Object variable or With Block variable not set.

And, of course the line:

ActiveSheet.ListObjects("Table1").TotalsRowRange.Select

is HIGHLIGHTED in YELLOW wnem I click on the Debug Option Button...

Any thoughts?

TKs,

Jim

 

[jim may]

MrKnowz -- Thanks for the illustration. I was unable to carry it over to my Event-code. Got it going however... Jim