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

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

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

Hi Frank,

The "workaround" is standard procedure for when we don't want users to edit
LOCKED cells. This doesn't me they won't try to when met with the restricted
access.

My point about the alert was meant to be in addition to Ron's suggestion.
Although it's mute on protected sheets that have at least one unlocked cell,
if you have, for example, an instruction sheet with no unlocked cells that
you want protected, any attempt to invoke edit mode will be met with the
alert regardless of .EnableSelection being used. On sheets with unlocked
cells, it affects the current "active" cell, thus the alert isn't fired
because the active cell is allowed editing.


In regards to your other issue, I didn't see the post you're referring to
yet, but I'll look for it.

HTH
Garry

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...
'----------------

Let me clarify: It returns FALSE if there's any error, whether the path or
filename.

Regards,
Garry

"GS" wrote:

> 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

Thanks GB for pointing out the complementary point, which clarifies the scenario in which the workaround really works - with at least one unlocked cell for the .EnableSelection to work on.

Re path validation(Thanks go to both Garry and Dave), Dave's solution suits the situation better since only the path information is supplied by the user at the point of validation(the user will then manually select whatever file at his discretion in the subsequent file chooser should the path be valid). A preliminary test shows it can capture all path errors as desired.

Again, all endeavours to help have been appreciated!!

Regards
Frank

Frank,

<FWIW> The reason I gave you the bFileExists() function is because the
bPathExists() function got replaced by it. It works for paths or filenames,
making it more versatile. As I said in my post of bPathExists(), I rarely use
it.

Regards,
Garry

"uglyvb" wrote:

>
> Thanks GB for pointing out the complementary point, which clarifies the
> scenario in which the workaround really works - with at least one
> unlocked cell for the .EnableSelection to work on.
>
> Re path validation(Thanks go to both Garry and Dave), Dave's solution
> suits the situation better since only the path information is supplied
> by the user at the point of validation(the user will then manually
> select whatever file at his discretion in the subsequent file chooser
> should the path be valid). A preliminary test shows it can capture all
> path errors as desired.
>
> Again, all endeavours to help have been appreciated!!
>
> Regards
> Frank
>
>
> --
> uglyvb
> ------------------------------------------------------------------------
> uglyvb's Profile: http://www.excelforum.com/member.php...o&userid=31720
> View this thread: http://www.excelforum.com/showthread...hreadid=521554
>
>

Thanks Garry for the further explanation on the code, which also resolves the problem by using bPathExist().

Thanks and regards
Frank

You're welcome Frank! I'm curious though.. If you're concerned about
ambiguous input from the user for a path, why not use a folder picker dialog?
Not only will it guarantee the selected path is valid, it eliminates the
possibility of input errors. Also, I'm sure your user would rather select
than type!

Regards,
Garry


"uglyvb" wrote:

>
> Thanks Garry for the further explanation on the code, which also
> resolves the problem by using -bPathExist()-.
>
> Thanks and regards
> Frank
>
>
> --
> uglyvb
> ------------------------------------------------------------------------
> uglyvb's Profile: http://www.excelforum.com/member.php...o&userid=31720
> View this thread: http://www.excelforum.com/showthread...hreadid=521554
>
>