Unusual Protection Problem. Please help!

kertappa

New Member
Joined
Apr 2, 2004
Messages
9
Hello all, if someone has ever come accross a problem like this, can they please help..

I have a worksheet with the majority of cells protected and only 'data entry' cells unprotected. The protection works fine for most of the sheet, but for some reason a block of cells halfway down the page does not let the user mouse click on them to enter data. Instead Excel shows the 'hand' mouse pointer and will not let the user select the cells at all. The strange thing is that if you scroll to the cells using the keyboard, they work fine and data can be entered. This problem only happens when using the mouse.
The sheet is protected/unlocked cells correctly and the problem only affects a block of about ten lines. The cells before/after work fine..

Please help, as this is starting to baffle me :(

Not sure if it makes a difference, but the sheet is unprotected/protected by a macro prior to use. But this is just the standard macro recorded by the recorder..
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
kertappa,

Sounds like you have some sort of hyperlink formatting on these cells. Have you highlighted them and tried Edit - Clear - Formats to reset the formatting ? Once you have done that, you may need to reset protection.

Give that a shot and let us know.

Bob
 
Upvote 0
hello, kertappa
Welcome to the Board !!!

an invisible shape or button can be the cause
try this
if you have other shapes on the sheet set the range properly
else Set rng = Cells
Code:
Sub remove_controls_within_range()
'Erik Van Geit
'050517 0053

Dim sh As Shape
Dim rng As Range

Set rng = Range("A1:D10")

    For Each sh In ActiveSheet.Shapes
    If Not Intersect(sh.TopLeftCell, rng) Is Nothing Then sh.Delete
    Next sh
End Sub
kind regards,
Erik
 
Upvote 0
No luck I'm afraid

Hi there, thanks for the response. Tried clrearing all the formatting, but still having the same problem I'm afraid.

Not sure if it helps, but there is no problem when protection is off, only when it is on. As soon as you get near those cells, the hand pointer appears..

Thanks
 
Upvote 0
Not sure if it helps, but there is no problem when protection is off, only when it is on. As soon as you get near those cells, the hand pointer appears..
then my code didn't work neighter ?
did you try this ?
I suppose you don't have any code in your workbook, especially within the worksheetmodule ?
 
Upvote 0
Hello Erik,

Sorry the response was for the previous message. I have a feeling you are right, because there are a lot of shapes/buttons on the worksheet. I will try your idea shortly..

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top