locked
On Exit from Worksheet Private Sub, Leave a Cell Other Than A1 Selected. RRS feed

  • Question

  • I am using the Worksheet BeforeDoubleClick method in an attempt to:

    1. Unprotect the worksheet.

    2. Hide a range of columns.

    3. Select the Merged Cell "B31"

    4. Protect the Worksheet 

    5. When the Macro is finished, I want Cell "B31" to REMAIN Selected.

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

    If Not Intersect(Target, Range("W28")) Is Nothing Then
            Cancel = True
            ActiveSheet.Unprotect Password:="mine"
            Columns("R:X").Select
            Selection.EntireColumn.Hidden = True
            ActiveSheet.Range("B31").Select
            ActiveSheet.Protect Password:="mine"
        End If

    End Sub

    After execution, Cell "A1" is selected instead of cell "B31".

    Any help would be appreciated.

    Wednesday, September 27, 2017 4:41 PM

Answers

  • Terry,

    I have solved the problem. As I was using a Merged Cell for the Hyperlink Target I apparently needed to use the Range in the Target Statement:

    If Target.Range.Address = "$W$28:$W$29" Then
            ActiveSheet.Unprotect Password:="mine"
            Columns("R:X").Select
            Selection.EntireColumn.Hidden = True
            ActiveSheet.Range("B31").Select
            ActiveSheet.Protect Password:="mine"
    End If

    This produces the proper result with the subject Columns hidden and B31 selected.

    Thanks for your time and help.

    • Proposed as answer by Tony---- Monday, October 2, 2017 7:32 AM
    • Marked as answer by TLQTX Monday, October 2, 2017 2:59 PM
    Friday, September 29, 2017 6:21 PM

All replies

  • T,
    re:  double click

    It works for me in xl2010.
    You must double click cell W28.
    A little cleaner version of the code might be...
    '---
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Me.Range("W28")) Is Nothing Then
       Cancel = True
       Me.Unprotect Password:="mine"
       Me.Columns("R:X").EntireColumn.Hidden = True
       Me.Range("B31").Select
       Me.Protect Password:="mine"
    End If
    End Sub
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Thursday, September 28, 2017 4:30 AM
  • Jim,

    Thanks for the reply.

    I ran both your version and my version of the code. I have xl2010 and am running windows 10.

    The code runs and hides the columns. However, at the end of the macro, Cell A1 is selected. 

    I am trying to end up on Cell B31. 

    FYI, in the worksheet form I have developed, Cell B31 is an Unlocked, Merged cell which includes B31:D31.

    Again, Thanks for your cleaned up version of the code.

    Thursday, September 28, 2017 6:30 PM
  • T,

    When I ran the code, cell B31 was selected.
    You will have to experiment with the protection and merge settings.
    You should also verify which cells are not locked.

    Not allowing the selection of unlocked cells may be the cause.

    '---
    Jim Cone

    Thursday, September 28, 2017 9:18 PM
  • Hi TLQTX,

    Could you select B31 manually after double clicking? Did you allow selecting locked cells when protecting the worksheet?

    I would suggest you use below code to make protecting sheet allow select both locked/unlocked cells.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    If Not Intersect(Target, Me.Range("W28")) Is Nothing Then
    
       Cancel = True
    
       Me.Unprotect Password:="mine"
    
       Me.Columns("R:X").EntireColumn.Hidden = True
    
       Me.Range("B31").Select
    
       'Add below line code
    
       Me.EnableSelection = xlNoRestrictions
    
       Me.Protect Password:="mine"
    
    End If
    
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 29, 2017 8:39 AM
  • Terry,

    Thanks again for your help.

    Unfortunately, this left me in the same quandary. At the end of the macro, A1 is selected.

    Is there a method of Stepping (F8) through a Worksheet Macro?

    Friday, September 29, 2017 4:09 PM
  • Terry,

    I have solved the problem. As I was using a Merged Cell for the Hyperlink Target I apparently needed to use the Range in the Target Statement:

    If Target.Range.Address = "$W$28:$W$29" Then
            ActiveSheet.Unprotect Password:="mine"
            Columns("R:X").Select
            Selection.EntireColumn.Hidden = True
            ActiveSheet.Range("B31").Select
            ActiveSheet.Protect Password:="mine"
    End If

    This produces the proper result with the subject Columns hidden and B31 selected.

    Thanks for your time and help.

    • Proposed as answer by Tony---- Monday, October 2, 2017 7:32 AM
    • Marked as answer by TLQTX Monday, October 2, 2017 2:59 PM
    Friday, September 29, 2017 6:21 PM
  • Hi TLQTX,

    Thanks for sharing the solution.

    I would suggest you mark your reply as answer which is the way to close a thread here.

    Regards,

    Tony


    Help each other

    Monday, October 2, 2017 7:33 AM
  • Thanks Tony!

    This is my 1st post and solution.

    I appreciate your info above and your attention to my question.

    Tim

    Monday, October 2, 2017 3:00 PM