Disable the prompt when clicking on a protected cell?

Hi all,

I have protected the worksheet to avoid any unwanted user intervention.
Then I was told to remove the corresponding message box when a user
attempts to modifed such a protected cell, i.e. When such an attempt
happens, Excel does NOTHING, rather than displaying a message telling
the user

"the cell or chart you are trying to change is protected and therefore
read-only...
<How to modify it>.."

So how can this message be stopped from being seen by the user? Thanks
any help will be appreciated.

Regards
Frank

Hi Frank

Which Excel version do you use and your users use ?
In 2002-2003 you have the option to not allow select locked cells

In 97-2003 you can use code to do it
Copy this event in the thisworkbook module

Sub Workbook_open()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Protect
sh.EnableSelection = xlUnlockedCells
Next sh
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


<CatchFrank@gmail.com> wrote in message news:1142199936.352464.124070@p10g2000cwp.googlegroups.com...
> Hi all,
>
> I have protected the worksheet to avoid any unwanted user intervention.
> Then I was told to remove the corresponding message box when a user
> attempts to modifed such a protected cell, i.e. When such an attempt
> happens, Excel does NOTHING, rather than displaying a message telling
> the user
>
> "the cell or chart you are trying to change is protected and therefore
> read-only...
> <How to modify it>.."
>
> So how can this message be stopped from being seen by the user? Thanks
> any help will be appreciated.
>
> Regards
> Frank
>

Many thanks Ron for your quick response! Im using Excel 2003 and the
spreadsheet is supposed to funtion at earlier versions as well...So
maybe coding is a better option in my case.

I inserted the code but it seems not doing anything particular...The
locked cell can still be selected and when trying to change its value,
Excel still prompts the same message as before....Can the message be
stopped being displayed by any other means?

Thanks again and regards
Frank

The code I posted is run automatic when you open the workbook.
Copy it in the thisworkbook module

Save/Close/Reopen and try again



--
Regards Ron de Bruin
http://www.rondebruin.nl


<CatchFrank@gmail.com> wrote in message news:1142202845.314109.232730@e56g2000cwe.googlegroups.com...
> Many thanks Ron for your quick response! Im using Excel 2003 and the
> spreadsheet is supposed to funtion at earlier versions as well...So
> maybe coding is a better option in my case.
>
> I inserted the code but it seems not doing anything particular...The
> locked cell can still be selected and when trying to change its value,
> Excel still prompts the same message as before....Can the message be
> stopped being displayed by any other means?
>
> Thanks again and regards
> Frank
>

Thanks again Ron. I re-pasted the code into the VB excel object
associated with the entire workbook, i.e. 'ThisWorkbook'. It now works!
(Before it was pasted into some other excel object related to an
individual sheet. My apologies for the previous misunderstanding!)

Another question regarding directory path validation...The following
code works fine with normal path input:

'Set FileExists to FileSystemObject
If outgoingFilePath = "\" Or Dir(outgoingFilePath, vbDirectory)
= "" Then
MsgBox outgoingFilePath & " is not a valid file path.
Please try again", vbExclamation, "Invalid Path"
End
End If

However, it will fail if outgoingFilePath is somewhat outrageous, say
'C:C:\' or 'CC:\', in which case run-time error 52 is fired, stating
'Bad file name or number'. Idealy ANY incorrect path input should be
captured by the code instead, without generating any run-time
error...So how the above code can be fortified to properly handle ALL
input situations? Many thanks in advance!!

Regards
Frank

Thanks GS for pointing out the stubbornness trait of such a prompt. I do not think displaying it or not matters much but I have been requested to 'remove' it. Thanks to Ron who supplies a nice workaround, i.e. disable selection of locked cell which successfully hold back proceeding to the message.

Pertaining to my previous post, anyone could help me with the directory path validation problem? Thank you!!

Regards
Frank

How about:

Dim FSO As Object
Dim outgoingFilePath As String

Set FSO = CreateObject("Scripting.FileSystemObject")

outgoingFilePath = "CC:\"

If FSO.folderexists(outgoingFilePath) Then
'keep going
MsgBox "It exists"
Else
'error message
MsgBox "Doesn't exist"
End If



CatchFrank@gmail.com wrote:
>
> Thanks again Ron. I re-pasted the code into the VB excel object
> associated with the entire workbook, i.e. 'ThisWorkbook'. It now works!
> (Before it was pasted into some other excel object related to an
> individual sheet. My apologies for the previous misunderstanding!)
>
> Another question regarding directory path validation...The following
> code works fine with normal path input:
>
> 'Set FileExists to FileSystemObject
> If outgoingFilePath = "\" Or Dir(outgoingFilePath, vbDirectory)
> = "" Then
> MsgBox outgoingFilePath & " is not a valid file path.
> Please try again", vbExclamation, "Invalid Path"
> End
> End If
>
> However, it will fail if outgoingFilePath is somewhat outrageous, say
> 'C:C:\' or 'CC:\', in which case run-time error 52 is fired, stating
> 'Bad file name or number'. Idealy ANY incorrect path input should be
> captured by the code instead, without generating any run-time
> error...So how the above code can be fortified to properly handle ALL
> input situations? Many thanks in advance!!
>
> Regards
> Frank

--

Dave Peterson

Hi Frank,

Here's a function I use to determine if a file exists or not. It doesn't
detect a path error per se, but it requires the fullname of the file being
tested for. You could display a message that shows the user what the path and
file being tested is.

Regards,
Garry

'--------------
Function bFileExists(fileName As String) As Boolean
' Checks if a file exists in the specified folder
' Arguments: fileName The fullname of the file
' Returns: TRUE if the file exists

Const sSource As String = "bFileExists()"

On Error Resume Next
bFileExists = (Dir$(fileName) <> "")

End Function

To use it:

If bFileExists(FullPathAndFilename) then...
-OR-
If Not bFileExists(FullPathAndFilename) then...
'----------------

Well.. I should have scrolled further down or used the dropdown. I don't use
it often, but this one works specifically for paths.

Function bPathExists(sPath As String) As Boolean
' Checks if a path to a folder exists.
' Arguments: sPath The full path to search
' Returns: TRUE if sPath exists

Const sSource As String = "bPathExists()"

On Error Resume Next
' "\nul" appended to the path makes it work with empty folders
bPathExists = (Dir$(sPath & "\nul") <> "")

End Function

Regards,
GS

Hi Frank,

AFAIK: This is a feature of sheet protection. You can't suppress this alert.

Regards,
GS


"CatchFrank@gmail.com" wrote:

> Hi all,
>
> I have protected the worksheet to avoid any unwanted user intervention.
> Then I was told to remove the corresponding message box when a user
> attempts to modifed such a protected cell, i.e. When such an attempt
> happens, Excel does NOTHING, rather than displaying a message telling
> the user
>
> "the cell or chart you are trying to change is protected and therefore
> read-only...
> <How to modify it>.."
>
> So how can this message be stopped from being seen by the user? Thanks
> any help will be appreciated.
>
> Regards
> Frank
>
>