Excel view code - BeforeDoubleClick

[Saarek]

Hello Everyone,

Thanks for taking a look at this post, I'm stuck in a bit of a loop.

I have the following code within my worksheet, set to beforedoubleclick:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$A$8" Then

Call Hide_All

Range("9:14").Select
Range("A9").Activate
Selection.EntireRow.Hidden = False

    End If
    
End Sub

This works fine, but just once. Once I have used this function I have to close excel and relaunch the sheet before it will work again.

Ideally I need to have 49 of these doubleclick functions, each running my macro to hide all relevant rows and then unhiding those specified when the required cell is double clicked.

I had, perhaps naively, assumed that simply pasting the same code in one after the other with the required tweaks would work, but this does not seem to be the case.

Can someone help me with:

A: why excel will only allow one double click operation before having to close the sheet
B: How to add the code for 49 specified cells with custom unhide.

*Using Excel 2010, sheet is saved as .xlsm*

 

[VoG]

Perhaps

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$A$8" Then
    Cancel = True
    Call Hide_All
    Rows("9:14").Hidden = False
End If
End Sub

 

[Saarek]

Perhaps

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$A$8" Then
    Cancel = True
    Call Hide_All
    Rows("9:14").Hidden = False
End If
End Sub

Hello, your code certainly looks cleaner and does work. Unfortunately I have the same issue whereby the function only works once.

If I hide rows 9:14 and click out and then double click back on A8 excel sends me in to the usual edit mode.

On the brightside, with your code I can simply copy and paste the formula and that side is working.

So, all I need to solve is how to get excel to keep the double click function working.

 

[Saarek]

Hello again, just to confirm as I may have been unclear:

I have an excel sheet that has a list of companies, each company has a number of branches underneath. These branches are hidden from view, using the BeforeDoubleClick function I want excel to run a macro and then un-hide the specified rows if a company name cell is double clicked on.

My VBA code under the spreadsheet works, but for some reason it will only work once and then default behaviour of double clicking on a cell to edit resumes.

I have to close excel completely for the code to function again. Very confused.

The code I am now using was suggested by VoG, any help on this confusing glitch would really be appreciated.

 

[Saarek]

Thanks everyone who looked, issue resolved. The Macro was at fault.