Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code does not work when sheet or workbook is protected
I have a workbook that requires protection so that the users can onl
see the main worksheet. It wouldnt matter if they saw the other sheet but I need to hide a code that automatically puts the users budget i the budget cell. When the user enters an item in the first column it automatically put the cost and later in the row makes some calculations based on how man they are selecting. My problem is this. I used a small vb code that says if they chang their mind and delete the item in the first column the entire ro becomes null. I did this by saying Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Column 1 Then Exit Sub If .Row < 7 Then Exit Sub Application.EnableEvents = False If .Value = "" Then .Offset(0, 1).Value = "" End If If .Value = "" Then .Offset(0, 2).Value = "" End If If .Value = "" Then .Offset(0, 3).Value = "" End If If .Value = "" Then .Offset(0, 4).Value = "" End If If .Value = "" Then .Offset(0, 6).Value = "" End If If .Value = "" Then .Offset(0, 7).Value = "" End If If .Value = "" Then .Offset(0, 8).Value = "" End If Application.EnableEvents = True End With End Sub When the protection is on sometimes when you delete from the firs column it says "you are trying to change a read only worksheet" an other times it just deletes that item but not the row. All of the cells in the worksheet are unlocked and the property for th file is not read only. Can anyone tell me how to get around this? Thanks a lot!! -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Code does not work when sheet or workbook is protected
What version of Excel are you using? I'm using 2003 and
wasn't able to replicate your problem. As long as the cell the user is changing and the cells affected by the macro are unlocked the macro worked as described. A couple notes: Before exiting the macro mid-routine you should send it to a subroutine to enable events. Also your macro can be condensed as follows: (Note I moved the EnableEvents commands to the beginning and end so that there is no extraneous code within the With / End With section.) Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False With Target If .Column 1 Then GoTo Rtn1 If .Row < 7 Then GoTo Rtn1 If .Value = "" Then Range(.Offset(0, 1), .Offset(0, 8)).Value = "" End If End With Rtn1: Application.EnableEvents = True End Sub -----Original Message----- I have a workbook that requires protection so that the users can only see the main worksheet. It wouldnt matter if they saw the other sheets but I need to hide a code that automatically puts the users budget in the budget cell. When the user enters an item in the first column it automatically puts the cost and later in the row makes some calculations based on how many they are selecting. My problem is this. I used a small vb code that says if they change their mind and delete the item in the first column the entire row becomes null. I did this by saying Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Column 1 Then Exit Sub If .Row < 7 Then Exit Sub Application.EnableEvents = False If .Value = "" Then .Offset(0, 1).Value = "" End If If .Value = "" Then .Offset(0, 2).Value = "" End If If .Value = "" Then .Offset(0, 3).Value = "" End If If .Value = "" Then .Offset(0, 4).Value = "" End If If .Value = "" Then .Offset(0, 6).Value = "" End If If .Value = "" Then .Offset(0, 7).Value = "" End If If .Value = "" Then .Offset(0, 8).Value = "" End If Application.EnableEvents = True End With End Sub When the protection is on sometimes when you delete from the first column it says "you are trying to change a read only worksheet" and other times it just deletes that item but not the row. All of the cells in the worksheet are unlocked and the property for the file is not read only. Can anyone tell me how to get around this? Thanks a lot!!! --- Message posted from http://www.ExcelForum.com/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlinks on protected sheet don't work | Excel Discussion (Misc queries) | |||
protected work sheet | Excel Discussion (Misc queries) | |||
Protected work sheet | Excel Discussion (Misc queries) | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming | |||
Checking with code to see if a Workbook is Protected? | Excel Programming |