Solved: Click on protected sheet

[Paul_Hossler]

I have a WB, and one of the worksheets is used to only display status, inputs, results, with no user input on that WS allowed

It's protected with Allow User to select Locked Cells and Unlocked Cells not checked, since it's the VBA that updates data on it

Invariably someone will try to click on the the 'display only' cells and try to enter data on this Summary Only WS.

Then I get a call saying "I need the password" or "I can't enter data" etc.

What would be nice is if I could response to the click on the protected WS and popup something about the importance of following instructions

There is not a OnClick event that I can find, so I'm looking for ideas

Paul

Edit: There is a DoubleClick event that can be captured, but no single click

[nepotist]

You could use the worksheet activate event.

[Paul_Hossler]

I had thought about that, but there's reasons to show the 'Summary' worksheet.


It's only if they try to type on it I was looking for a way to display a message

Since it's protected, it only seems to respond to the Double CLick event

Paul

[nepotist]

Agree as it would be triggered once clicks on the sheet, It would give them the instructions list or the info and it would be up to the user if they want to follow or not to follow. I can think of anything else other than the BeforeDoubleClick event

[Paul_Hossler]

I did make some progress ...

I put a transparent rectangle over the worksheet cells, and use the shape's Click event

Really not happy doing that way since it's very manual, but best I have so far

Paul

[nepotist]

Nice

[Kenneth Hobs]

For a single click catch, you can do this.

[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range
Set cell = Cells(Target.Row, Target.Column)
If cell.Locked Then MsgBox "Cell " & cell.Address(False, False) & " is protected.", _
vbExclamation, "Cell Is Protected"
End Sub[/vba]

Of course you can do the same for a BeforeDoubleClick event. You could put the routine above in a Module and call it for the Selection and BeforeDoubleClick events. The BeforeDoubleClick event would show the message and then the message about protection that you are getting the calls on. If you add the Cancel=True at the end of the BeforeDoubleClick event that 2nd message would not show.

I will have to think on the protected message when you try to change it.

[nepotist]

In this case , cell selection is disabled, so i believe the selectionchange event wouldn't apply.

[GTO]

Hi Paul,

Barely tested, but if you don't mind an API hook...

http://www.mrexcel.com/forum/showthr...le+click+event

at post #6

Mark

[Kenneth Hobs]

The method that GTO linked to might work if you did not select the options to not check the Select locked cells and Select Unlocked cells. I don't see the point in not protecting cells if your code is going to be the only method to change those cells though. I guess that it saves some coding effort and a little overhead.

If you use the API method, the cell that is returned when the click event happens is based on what cell was selected when you protected the sheet. So, I see no point in checking the target if the message is the same. This code shows two messages based on the lock status for the selected cell (target) when you protect that sheet.

[vba]'Jaafar, http://www.mrexcel.com/forum/showthread.php?t=423388
Public WithEvents Worksheet_ As ThisWorkbook

Private Sub Worksheet__Click(ByVal Target As Range)
Dim cell As Range
Set cell = Cells(Target.Row, Target.Column)
If cell.Locked Then
MsgBox "Cell " & cell.Address(False, False) & " is protected.", _
vbExclamation, "Cell Is Protected"
Else
MsgBox "Cell " & cell.Address(False, False) & " is not protected but I won't let ""you"" change it.", _
vbExclamation, "Cell Is Not Protected"
End If
End Sub[/vba]
You should code for the right click event to handle that scenario. If you don't do that, they could copy and then paste to that one selected cell that was not locked when you protected the sheet.

[Paul_Hossler]

Ken -- my experience is that since it's protected with "Allow User to select Locked Cells" and "Allow User to Select Unlocked Cells" not checked, the SelectionChange event won't fire. Agree that the Target is not required since all I want is to pop up a msg to the effect that "THis WS is for display only. Inputs are made thru userforms, etc. See.... blah blah blah"

Mark -- Thanks for the link. Based on a very quick run thru I think I can make that work. It's less manual than the transparent rectangle approach (I just know that I'll forget to resize it when I make changes)

I'll leave the thread open for awhile just in case

Paul

[Paul_Hossler]

Mark -- got the API working, but it had some side effects tha I couldn't overcome

1. It spends all it's time in a tight Do Loop
2. If I switch to the VBE, the loop is still running and I have to stop the macro

I'll keep the code, but for now I think I'll have to use the transparent rectangle and it's OnClick event

Thanks

Paul

[shrivallabha]

Why not use then Workbook_Open event (as I'm guessing this is going to be your display sheet) when user opens workbook?

Something simple like:
[VBA]Private Sub Workbook_Open()
Msgbox "To change data in this worksheet use controls and forms provided!"
End Sub[/VBA]

The only drawback will be the message will appear only once but that should suffice.

[Paul_Hossler]

Thanks

The only drawback will be the message will appear only once but that should suffice.

1. You have better users that I do

2. The protected sheet is one that people keep going back to, so I think that a Reminder more than when they open the workbook is helpful, but I didn't want to have it pop up everytime they go to the sheet unless they actually try to click on the protected sheet so I couldn't use the WS Activate event.

Paul

[GTO]

Mark -- got the API working, but it had some side effects tha I couldn't overcome

1. It spends all it's time in a tight Do Loop
2. If I switch to the VBE, the loop is still running and I have to stop the macro

I'll keep the code, but for now I think I'll have to use the transparent rectangle and it's OnClick event

Thanks

Paul

Hi Paul,

I am definitely not the person to be speaking reference Class Modules and/or RaiseEvent, just barely getting my feet wet (probably just a toe or two) so far. That said (and Ken or others, please correct anything I get wrong or less than clear), I believe that the continuous running is needed for RaiseEvent as we are creating our own event.

I took it from your first post that double-click would not be of interest, as you don't want the calls for "why can't I select anything", but just to mention...

Excel's warning only comes up if the user double-clicks. If I leave the 'Protect worksheet and contents of locked cells' box ticked (in the 'Protect Sheet' dialog) and un-tick all boxes in the 'Allow all users of this worksheet to:' area, then, this seems to substitute for Excel's msg.

Option Explicit
    
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    MsgBox "Sorry, no editing on this sheet...", vbInformation, vbNullString
End Sub

Now if the single-click would seem much better, but seems less loveable because of the continuous running, how about a couple of mods?

In ThisWorkbook, the code as shown in post 6 in the link, except:

Change these to Public Functions.

Public Function SetClickEvent(TargetSheet As Worksheet)
Public Function StopClickEvent()

This will keep the StopClickEvent from showing up in the run macro dialog.

Then ditch both the workbook open and close events, and add this to the worksheet's module:

Option Explicit
    
Public WithEvents Worksheet_ As ThisWorkbook
    
Private Sub Worksheet_Activate()
    Call ThisWorkbook.SetClickEvent(TargetSheet:=Me)
End Sub
    
Private Sub Worksheet_Deactivate()
    Call ThisWorkbook.StopClickEvent
End Sub
    
Private Sub Worksheet__Click(ByVal Target As Range)
    '// Msg or warning to suit...   //
    MsgBox "Seriously, no editing on this sheet...", vbInformation, vbNullString
End Sub

ACK! Sorry, a tad out of order, but just thought to test. In case the sheet of interest is the last active sheet when the wb is saved/closed, the Workbook_Open could be:

Private Sub Workbook_Open()
    If TypeName(ActiveSheet) = "Worksheet" Then
        If ActiveSheet.CodeName = "Sheet1" Then
            Call SetClickEvent(Sheet1)
        End If
    End If
End Sub

The transparent rectangle certainly sounds nice as well, especially if scrolling is limited. I just thought to suggest the above in case it sounds niftier w/o the code running all the time.

Mark

[Paul_Hossler]

Mark

but seems less loveable (LOL) because of the continuous running, how about a couple of mods

I'll certainly give the new code a try since it's much more maintainable than the transparent rectangle.

Thanks again

Paul

[GTO]

Hey Paul,

I realized that I had missed ensuring killing things if the file was closed with the sheet active, and/or if switching to another wb. I scrapped the Open event and used workbook Activate and Deactivate to resolve both.

Private Sub Workbook_Activate()
    MsgBox "Activate:"
    If TypeName(ActiveSheet) = "Worksheet" Then
        If ActiveSheet.CodeName = "Sheet1" Then
            MsgBox "Called SetClick"
            Call SetClickEvent(Sheet1)
        End If
    End If
End Sub
 
Private Sub Workbook_Deactivate()
    MsgBox "Deactivate"
    If TypeName(ActiveSheet) = "Worksheet" Then
        If ActiveSheet.CodeName = "Sheet1" Then
            MsgBox "Called StopClick"
            Call StopClickEvent
        End If
    End If
End Sub

Unfortunately (and yes, I was using harsher words upon discovery), I found a hiccup I don't see a way around and it's irksome.

If the user has Windows in Taskbar ticked (I usually do) and the sheet is active (and our event is 'alive'), another workbook cannot be activated (at least not in the same instance of Excel) from the taskbar. I am clueless as to why.

If menubar/Window is used, the deactivate event is called and workbooks switch w/o a hitch. Whatever happens in the taskbar is a mystery to me.

Hopefully you get this before spending time making the same discovery. Sorry about that :-(

Well, time for the weekend for me; a great one to you and yours,

Mark

[GTO]

PS - the above in XP|Excel 2003

[Paul_Hossler]

Mark - thanks for the update

I'll go with the new and improved version and see how it works

Paul

[shrivallabha]

Not very sure why this thread remained at the back of my mind. And today when when I came across p45cal's post then I felt you wanted something of this sort:
http://www.vbaexpress.com/forum/showthread.php?t=38277
Have look at post #5 (lock user interface only)

With this you can use selectionchange event as Kenneth has shown.

Still not sure if this is it