How to make shared workbook editable using userform only?

0
votes

I have this workbook which is shared in a network drive. What I want is, only 5 persons can edit the file through user-form/or directly and the rest can only see.

Some background:
My file is shared in a network file, where at least 15 users input data through User-form concurrently and when one person saves the file, The workbook merges itself.

Now I want

  • only 3 users will input the data through user-form and rest all will be able to see the sheets but not edit.

  • or the worksheet will be visible to all but its editable through user-form only

The problem is when I share the workbook, the protect option/object gets inaccessible, so the question of making it protected-unprotected is not valid here.

This is what I have tried in sub Workbook_Open()

Private Sub Workbook_Open()
    Protect_sheet
End Sub


Sub Protect_sheet()
    Dim WS As Worksheet
    Dim PWS As String
    PWS = "12345"    
    For Each WS In Worksheets
        WS.Protect Password:=PWS, USERINTERFACEONLY:=True
    Next WS    
End Sub

It works only if the workbook is not shared.

I have a macro button to show the form, I tried:
Unlock the sheet before loading the User-form and change the data, lock the sheet again, showing error (cell is locked).

The last choice I have got left is to, store the data in one workbook wb_master and copy its content to another workbook wb_slave. The master one will be populated by the designated 3 users rest all users will use the slave one. Here, how can I achieve this real-time refresh/reload from master to slave?

Is there any way around this?

1
excelvbaexcel-2007

1 Answers

0
votes

Proposal:

  • InputWorkbook.xlsb

saves data to

  • access database



and

  • ReadWorkbook.xlsb
    shows the data for all users

Advantages:
No need to protect/unprotect workbooks/worksheets!
No issue with sharing/unsharing a workbook!
No problems with parallel access to the files!
(Even parallel write from your three editors is possible!)