Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable the prompt when clicking on a protected cell?
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 wrote in message oups.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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable the prompt when clicking on a protected cell?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable the prompt when clicking on a protected cell?
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 wrote in message oups.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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable the prompt when clicking on a protected cell?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable the prompt when clicking on a protected cell?
Hi Frank,
AFAIK: This is a feature of sheet protection. You can't suppress this alert. Regards, GS " 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable the prompt when clicking on a protected cell?
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 -- uglyvb ------------------------------------------------------------------------ uglyvb's Profile: http://www.excelforum.com/member.php...o&userid=31720 View this thread: http://www.excelforum.com/showthread...hreadid=521554 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable the prompt when clicking on a protected cell?
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable the prompt when clicking on a protected cell?
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... '---------------- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable the prompt when clicking on a protected cell?
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... '---------------- |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable the prompt when clicking on a protected cell?
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable the prompt when clicking on a protected cell?
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable the prompt when clicking on a protected cell?
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable the prompt when clicking on a protected cell?
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable the prompt when clicking on a protected cell?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
do away prompt when protected cell is being written | Excel Discussion (Misc queries) | |||
Disable Allow ActiveX prompt | Excel Discussion (Misc queries) | |||
close WB after clicking DISABLE MACROS | Excel Programming | |||
Disable Message Prompt in VBA | Excel Programming | |||
Disable Excel Prompt | Excel Programming |