Excel view code - BeforeDoubleClick

Saarek

New Member
Joined
Aug 19, 2014
Messages
31
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:

Code:
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*
 
Last edited:

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Perhaps

Code:
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

New Member
Joined
Aug 19, 2014
Messages
31
Perhaps

Code:
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.
 
Last edited:

Saarek

New Member
Joined
Aug 19, 2014
Messages
31
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

New Member
Joined
Aug 19, 2014
Messages
31
Thanks everyone who looked, issue resolved. The Macro was at fault.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,489
Messages
5,596,455
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top