If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Pasting to Protected Cells in Xl2000
I am trying to prevent users from making changes to certain cells in XL2000.
I have the cells 'Locked' and both the Sheet and the Workbook are Protected with a password. The locked cells prevent users from typing information into them, however, users can still copy another cell and then paste it into the locked and protected cells. Is there any way to prevent this? Thanks for your help. -VMill |
#2
|
|||
|
|||
Pasting to Protected Cells in Xl2000
-VMill wrote:
I am trying to prevent users from making changes to certain cells in XL2000. I have the cells 'Locked' and both the Sheet and the Workbook are Protected with a password. The locked cells prevent users from typing information into them, however, users can still copy another cell and then paste it into the locked and protected cells. Is there any way to prevent this? Thanks for your help. -VMill Hi, one way is to prevent users from selecting locked cells. This is a worksheet property that can be set for a session, however, the setting does not persist between sessions. To overcome this lack of persistence you can use the Workbook_Open Event to set it when the workbook is opened... Private Sub Workbook_Open() Dim Sht As Worksheet For Each Sht In Me.Worksheets Sht.EnableSelection = xlUnlockedCells Next End Sub To get the code in place... 1. Copy it 2. Right click any worksheet tab, then select "View Code" from the popup. This takes you to the VB Editor 3. In the VB Editor double click on the "ThisWorkbook" icon in the "Project Explorer" to open the Workbook's code module. 4. Paste the code into the code module that appears 5. For the code to work the workbook's Security level will need to be Medium. Also, everytime the workbook is opened the user must click on the "Enable Macros" button on the "Security Warning" dialog. To change security to medium, go Tools|Macro|Security...select Medium|OK|Close workbook|ReOpen workbook|click "Enable Macros" on "Security Warning" dialog. Ken Johnson |
#3
|
|||
|
|||
Pasting to Protected Cells in Xl2000
Thanks for the suggestion, however, I really need a solution that does not
rely on a user enabling macros. -VMill "Ken Johnson" wrote: -VMill wrote: I am trying to prevent users from making changes to certain cells in XL2000. I have the cells 'Locked' and both the Sheet and the Workbook are Protected with a password. The locked cells prevent users from typing information into them, however, users can still copy another cell and then paste it into the locked and protected cells. Is there any way to prevent this? Thanks for your help. -VMill Hi, one way is to prevent users from selecting locked cells. This is a worksheet property that can be set for a session, however, the setting does not persist between sessions. To overcome this lack of persistence you can use the Workbook_Open Event to set it when the workbook is opened... Private Sub Workbook_Open() Dim Sht As Worksheet For Each Sht In Me.Worksheets Sht.EnableSelection = xlUnlockedCells Next End Sub To get the code in place... 1. Copy it 2. Right click any worksheet tab, then select "View Code" from the popup. This takes you to the VB Editor 3. In the VB Editor double click on the "ThisWorkbook" icon in the "Project Explorer" to open the Workbook's code module. 4. Paste the code into the code module that appears 5. For the code to work the workbook's Security level will need to be Medium. Also, everytime the workbook is opened the user must click on the "Enable Macros" button on the "Security Warning" dialog. To change security to medium, go Tools|Macro|Security...select Medium|OK|Close workbook|ReOpen workbook|click "Enable Macros" on "Security Warning" dialog. Ken Johnson |
#4
|
|||
|
|||
Pasting to Protected Cells in Xl2000
-VMill wrote:
Thanks for the suggestion, however, I really need a solution that does not rely on a user enabling macros. -VMill Hi -VMill, If you add an extra worksheet and name it "Must Enable Macros" you can use the following codes to hide the worksheets except for "Must Enable Macros" which can also instruct the user to close the workbook then reopen and click on "Enable macros". If the user follows the instruction the reopened workbook will have visible sheets and will be able to work on them and will not be able to select your protected cells because the code also reinstates the xlUnlockedCells selection property. Use the same password for protecting each sheet and insert that password into the speech marks in the code where you see Password:= "".... Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ScreenUpdating = False Worksheets("Must Enable Macros").Visible = xlSheetVisible Dim Sht As Worksheet For Each Sht In Me.Worksheets On Error GoTo PROTECTED If Sht.Name "Must Enable Macros" Then Sht.Visible = xlSheetVeryHidden End If Next Sht Exit Sub PROTECTED: Sht.Unprotect password:="" Sht.Visible = xlSheetVeryHidden Sht.Protect password:="" Resume Next End Sub Private Sub Workbook_Open() Application.ScreenUpdating = False Dim Sht As Worksheet For Each Sht In Me.Worksheets Select Case Sht.Name Case "Must Enable Macros" On Error GoTo PROTECTED Sht.Visible = xlSheetVeryHidden Case Else Sht.Visible = xlSheetVisible Sht.EnableSelection = xlUnlockedCells End Select Next Sht Exit Sub PROTECTED: Worksheets(Worksheets.Count - 1).Unprotect password:="" Sht.Visible = xlSheetVeryHidden Worksheets(Worksheets.Count - 1).Protect password:="" Resume Next End Sub Both lots of go go into the ThisWorkbook code module as before Ken Johnson |
Thread Tools | |
Display Modes | |
|
|