Preventing Editing Of Data While Allowing Cell To Be Double CLicked

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,860
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a range of cells that are populated with data. The worksheet is protected, but the range is unlocked to allow the user to double click on a cell in that range to execute some code. The problem is, the values in these cells are open to be changed, which I cannot have.

Does anyone have any solution they can share in which would allow the cells to be double clicked but not editable?
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Candyman8019

Board Regular
Joined
Dec 2, 2020
Messages
138
Office Version
  1. 365
Platform
  1. Windows
Perhaps it would be easier to add a button to the spreadsheet for the user to execute code...that would allow the cells to remain locked. On the developer tab | insert | form control.
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,860
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks Candyman for your suggestion. For this particular application, I don't think it provide for the flexibility I'm looking for.
Maybe it just can't be done ... I have to choose one (maintain the integrity of the data) or th eother (ability to double click a cell).
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
If I have understood correctly ..
Leave the cells 'Locked' but when protecting the sheet choose 'Allow all users of this worksheet to: Select locked cells'
 
Solution

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,860
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

If I have understood correctly ..
Leave the cells 'Locked' but when protecting the sheet choose 'Allow all users of this worksheet to: Select locked cells'
Hello Peter, yes ... your solution provided the direction I needed. I can accept the compromise of users clicking on cells they shouldn't be (which disallowing the user to select locked cells accomplished). With that, is there a way to disable the message that pops up when a user tries to enter information to a protected worksheet?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Are the users actually trying to change the cells or are you getting the message when the cell is double-clicked?
If it is the double-click issue, then try putting this at the start of your double-click code
VBA Code:
Cancel = True

If they are actually selecting a locked cell and trying to change it then AFAIK you cannot disable the message.
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,860
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

In my testing, I found another "flaw" with the approach I'm pursuing with your advice Peter (and again, thank you!). Here's the scenario.

I have a worksheet that functions as a dynamic form for users to view and manipulate only certain portions of the worksheet. It extracts data from other worksheets and presents it to the user. But within this data, only certain portions are accessible to the user to actually edit. For these reasons, it's nice to have the ability to disable a user from being able to select a cell. Certianly with a protected sheet and locked cells, the most that will happen is an interrupting message, but for the ease of navigation and not implying to the user that a cell is editable, disabling the click on locked cells is preferred.

However, there are ranges of this populated data that although can't be edited (changed or manipulated), the ability to double click of a cell (in essence selecting that value) will call up a routine which will provide additional information for the user. For simplicity, lets just say double clicking a cell A14 will display a simple message ("Aha you want more information on this cell's value"). We don't want the user to be able to edit the value in A14, but we want an event that will pull up additional code for that value.

Here is my code thus far ...
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    If Not Intersect(Target, ws_master.Columns(1)) Is Nothing Then 
        MsgBox "RID number change - delete?"
    End If
End Sub

So, on my protected sheet, with users able to select locked and unlocked cells, the cells in column A are coded to be locked. When the user double clicks on a cell in column A, they get the message (+1). They are unable to edit the values in the cell though (+1).

However, these are the unfavourable consequences to this method:
1) the user can click all over the place now, in areas where they shouldn't try to edit. The result is Excel's default message. An annoyance, and a loss of a fraction of time. Optimally to be able to prevent the user clicking would be best, but would settle with no message, or a custom message instead of the generic one.
2) the user can click on any cell in column A, whether it has a value or not. The user should be restricted to being able to only select on populated cells in that column. Running code that is expecting a value on an action to a cell that doesn't, will create errors. I suppose I could build into the doubleclick event code to end the routine if the value of Ax is null.

So, I share this in hopes that either an alternate solution might be revealed, find a different approach, or work within and accept the limitations.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Perhaps I have not understood fully, but I can't see that you can achieve what you are asking.
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,860
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Peter, I'm pretty sure you understand. :) Maintaining a worksheet of locked cells to prevent value editing, yet able to access select locked cells to enable events. Can't happen. Perhaps, with some help, I can use hyperlinks to accomplish the same needs as what I was wanting a double click on the cell to accomplish.

If there was a solution, I have no doubts you would be one to share it.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,519
Messages
5,625,280
Members
416,086
Latest member
CaptainGD

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