vba does not work in shared workbook

arifpunekar

New Member
Joined
May 2, 2014
Messages
32
hi I am using windows 7 64 bit and excel 2007, I have large amount of macro enabled workbook, i have one vba code for auto lock cell when data entry and save, but when i shared workbook vba code not working , so i want to auto lock cell when data entry and save the file only that cells are locked which have data other blank cell are not protected in shared workbook. my code is here,
Code:
                                         Private Sub Workbook_beforesave(ByVal saveasui As Boolean, cancel As Boolean)
On Error Resume Next 'resume to next line if any error occurs
Dim cell As Range
With ActiveSheet
'frist of all unprotect the entire 'sheet and unlock all cells
.Unprotect Password:="arifpunekar"
.Cells.Locked = False
'now search for non blank cells
'and lock them and unlock blank cells
For Each cell In ActiveSheet.UsedRange
If cell.Value = "" Then
cell.Locked = False
Else
cell.Locked = True
End If
Next cell
.Protect Password:="arifpunekar"
'protect with blank password.you can change it
End With
Exit Sub
End Sub

sir so please send me answer as early as possible. thanks in advance.
 
Last edited by a moderator:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sir i want to work in shared workbook because of 10 peoples are work in workbook at a same time therefor i want lock that cell when data entry and save .sir please tell me the solution . as possible as early.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top