Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Customising Standard Popups
HI
This may be an impossible thing to do.... When I try to access a protected cell , I am given the standard Excel popup saying that it is protected and that I can modify it by putting a password in via the tools menu. I do realise that the text in the popup can't be changed , but I was wondering if the popup itself could be replaced with a message box or validation popup whenever access to a protected cell is attempted? Perhaps a small macro applying to all the protected parts for the sheets could be triggered to replace the standard popup with a new one with a customised message. (One that doesn't suggest users go digging around trying to unprotect cells...) Grateful for any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Customising Standard Popups
Maybe you could stop the users from selecting a locked cell on a protected
worksheet. If all your users are using xl2002+, then there's an option in the Tools|Protection|protect sheet dialog that allows you to specify if the user can select unlocked cells. If you're using xl2k or below, you'd need a macro: Sub Auto_Open() With Worksheets("Sheet99999") .Unprotect Password:="hi" .EnableSelection = xlUnlockedCells .Protect Password:="hi", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End With End Sub In fact, in xl2k and below, this setting isn't remembered when you close the workbook and reopened. Setting it in code was the only way to do this. Colin Hayes wrote: HI This may be an impossible thing to do.... When I try to access a protected cell , I am given the standard Excel popup saying that it is protected and that I can modify it by putting a password in via the tools menu. I do realise that the text in the popup can't be changed , but I was wondering if the popup itself could be replaced with a message box or validation popup whenever access to a protected cell is attempted? Perhaps a small macro applying to all the protected parts for the sheets could be triggered to replace the standard popup with a new one with a customised message. (One that doesn't suggest users go digging around trying to unprotect cells...) Grateful for any help. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Customising Standard Popups
Hi Dave OK thanks. I did think of that , but it doesn't help me because they complain then that they can't select the cell...! I do need a popup each time a protected cell is selected saying ' You can't change this cell , and don't need to access it' , rather than saying ' go and dig around in the options panel for a password..' Not sure it's possible. Thanks again. In article , Dave Peterson writes Maybe you could stop the users from selecting a locked cell on a protected worksheet. If all your users are using xl2002+, then there's an option in the Tools|Protection|protect sheet dialog that allows you to specify if the user can select unlocked cells. If you're using xl2k or below, you'd need a macro: Sub Auto_Open() With Worksheets("Sheet99999") .Unprotect Password:="hi" .EnableSelection = xlUnlockedCells .Protect Password:="hi", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End With End Sub In fact, in xl2k and below, this setting isn't remembered when you close the workbook and reopened. Setting it in code was the only way to do this. Colin Hayes wrote: HI This may be an impossible thing to do.... When I try to access a protected cell , I am given the standard Excel popup saying that it is protected and that I can modify it by putting a password in via the tools menu. I do realise that the text in the popup can't be changed , but I was wondering if the popup itself could be replaced with a message box or validation popup whenever access to a protected cell is attempted? Perhaps a small macro applying to all the protected parts for the sheets could be triggered to replace the standard popup with a new one with a customised message. (One that doesn't suggest users go digging around trying to unprotect cells...) Grateful for any help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Customising Standard Popups
You can try this event in the sheet module
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Locked = True Then MsgBox "Sorry this cell is locked" End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Colin Hayes" wrote in message ... Hi Dave OK thanks. I did think of that , but it doesn't help me because they complain then that they can't select the cell...! I do need a popup each time a protected cell is selected saying ' You can't change this cell , and don't need to access it' , rather than saying ' go and dig around in the options panel for a password..' Not sure it's possible. Thanks again. In article , Dave Peterson writes Maybe you could stop the users from selecting a locked cell on a protected worksheet. If all your users are using xl2002+, then there's an option in the Tools|Protection|protect sheet dialog that allows you to specify if the user can select unlocked cells. If you're using xl2k or below, you'd need a macro: Sub Auto_Open() With Worksheets("Sheet99999") .Unprotect Password:="hi" .EnableSelection = xlUnlockedCells .Protect Password:="hi", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End With End Sub In fact, in xl2k and below, this setting isn't remembered when you close the workbook and reopened. Setting it in code was the only way to do this. Colin Hayes wrote: HI This may be an impossible thing to do.... When I try to access a protected cell , I am given the standard Excel popup saying that it is protected and that I can modify it by putting a password in via the tools menu. I do realise that the text in the popup can't be changed , but I was wondering if the popup itself could be replaced with a message box or validation popup whenever access to a protected cell is attempted? Perhaps a small macro applying to all the protected parts for the sheets could be triggered to replace the standard popup with a new one with a customised message. (One that doesn't suggest users go digging around trying to unprotect cells...) Grateful for any help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Customising Standard Popups
In article , Ron de Bruin
writes You can try this event in the sheet module Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Locked = True Then MsgBox "Sorry this cell is locked" End Sub Hi Ron Yes , that would do it. Seems to be single click rather than double as in the standard Excel popup. Is there a way to apply it to the whole workbook at one go , or do I need to put it in every worksheet separately , do you know? Great stuff Ron - Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Customising Standard Popups
Remove any code that you added to any of the worksheets.
Put this behind the ThisWorkbook module. Option Explicit Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) If Sh.ProtectContents = True _ Or Sh.ProtectDrawingObjects = True _ Or Sh.ProtectScenarios = True Then If Target.Cells(1).Locked = True Then MsgBox "Sorry, the activecell cell is locked" End If End If End Sub === Just my opinion--as a user, I would soon tire of this. Colin Hayes wrote: In article , Ron de Bruin writes You can try this event in the sheet module Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Locked = True Then MsgBox "Sorry this cell is locked" End Sub Hi Ron Yes , that would do it. Seems to be single click rather than double as in the standard Excel popup. Is there a way to apply it to the whole workbook at one go , or do I need to put it in every worksheet separately , do you know? Great stuff Ron - Thanks. -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Customising Standard Popups
Colin
In Thisworkbook module, not in a sheet module. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) If Target.Locked = True Then MsgBox "Sorry this cell is locked" End Sub Gord Dibben MS Excel MVP On Sun, 27 May 2007 19:58:20 +0100, Colin Hayes wrote: In article , Ron de Bruin writes You can try this event in the sheet module Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Locked = True Then MsgBox "Sorry this cell is locked" End Sub Hi Ron Yes , that would do it. Seems to be single click rather than double as in the standard Excel popup. Is there a way to apply it to the whole workbook at one go , or do I need to put it in every worksheet separately , do you know? Great stuff Ron - Thanks. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Customising Standard Popups
Colin
Be aware that this pop-up will occur even if the sheet does not have protection enabled. Setting the cells' format to locked but not protecting the sheet is sufficient to trigger the pop-up when a locked-format cell is selected. Gord On Sun, 27 May 2007 12:16:34 -0700, Gord Dibben <gorddibbATshawDOTca wrote: Colin In Thisworkbook module, not in a sheet module. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) If Target.Locked = True Then MsgBox "Sorry this cell is locked" End Sub Gord Dibben MS Excel MVP On Sun, 27 May 2007 19:58:20 +0100, Colin Hayes wrote: In article , Ron de Bruin writes You can try this event in the sheet module Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Locked = True Then MsgBox "Sorry this cell is locked" End Sub Hi Ron Yes , that would do it. Seems to be single click rather than double as in the standard Excel popup. Is there a way to apply it to the whole workbook at one go , or do I need to put it in every worksheet separately , do you know? Great stuff Ron - Thanks. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Customising Standard Popups
Hi Dave OK Thanks for that. It will work well. I take your point about it being tiresome after a while. Although I suppose it's only really taking the place of the standard excel pop-up. The only difference is that the standard one is double-click , whereas this one is on simple selection. Could it be made double-click , so that it only pops up if the user insists? That would make it much less wearisome and it would operate at the same frequency as the standard one. Don't know if that's possible. Thanks for your help with this. In article , Dave Peterson writes Remove any code that you added to any of the worksheets. Put this behind the ThisWorkbook module. Option Explicit Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) If Sh.ProtectContents = True _ Or Sh.ProtectDrawingObjects = True _ Or Sh.ProtectScenarios = True Then If Target.Cells(1).Locked = True Then MsgBox "Sorry, the activecell cell is locked" End If End If End Sub === Just my opinion--as a user, I would soon tire of this. Colin Hayes wrote: In article , Ron de Bruin writes You can try this event in the sheet module Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Locked = True Then MsgBox "Sorry this cell is locked" End Sub Hi Ron Yes , that would do it. Seems to be single click rather than double as in the standard Excel popup. Is there a way to apply it to the whole workbook at one go , or do I need to put it in every worksheet separately , do you know? Great stuff Ron - Thanks. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Customising Standard Popups
In article , Gord Dibben
<gorddibbATshawDOTca@?.? writes Colin Be aware that this pop-up will occur even if the sheet does not have protection enabled. Setting the cells' format to locked but not protecting the sheet is sufficient to trigger the pop-up when a locked-format cell is selected. Gord HI Gord Yes , I see. I was hoping it would operate in the same way as the standard popup , the difference being that I'd be able to control the text in the popup. Still , it would do the job and I'm grateful for your advice. Best Wishes On Sun, 27 May 2007 12:16:34 -0700, Gord Dibben <gorddibbATshawDOTca wrote: Colin In Thisworkbook module, not in a sheet module. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) If Target.Locked = True Then MsgBox "Sorry this cell is locked" End Sub Gord Dibben MS Excel MVP On Sun, 27 May 2007 19:58:20 +0100, Colin Hayes wrote: In article , Ron de Bruin writes You can try this event in the sheet module Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Locked = True Then MsgBox "Sorry this cell is locked" End Sub Hi Ron Yes , that would do it. Seems to be single click rather than double as in the standard Excel popup. Is there a way to apply it to the whole workbook at one go , or do I need to put it in every worksheet separately , do you know? Great stuff Ron - Thanks. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Customising Standard Popups
The double click means that you're trying to edit the cell.
You can have your own pop up when you select the cell or after you update the cell--but not right before you start editing the cell. Colin Hayes wrote: Hi Dave OK Thanks for that. It will work well. I take your point about it being tiresome after a while. Although I suppose it's only really taking the place of the standard excel pop-up. The only difference is that the standard one is double-click , whereas this one is on simple selection. Could it be made double-click , so that it only pops up if the user insists? That would make it much less wearisome and it would operate at the same frequency as the standard one. Don't know if that's possible. Thanks for your help with this. In article , Dave Peterson writes Remove any code that you added to any of the worksheets. Put this behind the ThisWorkbook module. Option Explicit Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Range) If Sh.ProtectContents = True _ Or Sh.ProtectDrawingObjects = True _ Or Sh.ProtectScenarios = True Then If Target.Cells(1).Locked = True Then MsgBox "Sorry, the activecell cell is locked" End If End If End Sub === Just my opinion--as a user, I would soon tire of this. Colin Hayes wrote: In article , Ron de Bruin writes You can try this event in the sheet module Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Locked = True Then MsgBox "Sorry this cell is locked" End Sub Hi Ron Yes , that would do it. Seems to be single click rather than double as in the standard Excel popup. Is there a way to apply it to the whole workbook at one go , or do I need to put it in every worksheet separately , do you know? Great stuff Ron - Thanks. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Amending standard Excel popups | Excel Worksheet Functions | |||
Disabling security popups | Excel Worksheet Functions | |||
Automated Popups | Excel Worksheet Functions | |||
Customising Charts | Charts and Charting in Excel | |||
Customising Footers - not doing too well! | Excel Discussion (Misc queries) |