如何为某些用户打开excel文件读写，并为其他用户只读 [英] How can I open an excel file read-write for some users and readonly for others

问题描述

我有一个excel文件，我的所有同事都必须具有读取权限，但只有少数可能有写入权限。

我试图在workbook_open中介绍一个过程测试用户，并根据它来决定如何打开文件。我知道这不能直接工作，所以我试图访问一个加载项，该加载项具有更改只读状态的过程。

 私人Sub Workbook_Open（）
 users = Environ（USERNAME）
选择案例用户
案例chris：MsgBoxok
案例david：MsgBoxok 
 Casesam：MsgBoxok
 Case Else：Application.Run（read_only）
结束选择
 End Sub 
  

这是Addin中的子功能

  Sub read_only（）
 file_name = ActiveWorkbook.Path& \& ActiveWorkbook.Name 
 Status = ActiveWorkbook.readonly 
 
 ActiveWorkbook.Close'（问题出在这里，因为当我的工作簿关闭时，这个sub停止）
如果Status = False然后
 Workbooks.Open fileName：= file_name，readonly：= True 
 Else 
 Workbooks.Open fileName：= file_name，readonly：= False 
 End If 
 End Sub $ b $有没有人想到如何解决这个问题？
解决方案
你可以直接做到：）
 
 
 
尝试这个
  Private Sub Workbook_Open（）
 Users = Environ（USERNAME）
选择案例用户
案例chris：MsgBoxok
案例david：MsgBoxok
案例sam：MsgBoxok
案例Else 
 Application.DisplayAlerts = False 
错误简历Next 
'可能已经被读取
如果ThisWorkbook.Path<> vbNullString Then ThisWorkbook.ChangeFileAccess xlReadOnly 
错误GoTo 0 
 Application.DisplayAlerts = True 
结束选择
 End Sub 
  
 
I have an excel file that all my colleagues must have read access, but only a few may have write access.

I tried to introduce in workbook_open a procedure to test user and depending on it to decide how the file to be opened. I learned that does not work directly, so I tried to access an add-in that has a procedure that changes the readonly status.
Private Sub Workbook_Open()
    users = Environ("USERNAME")
    Select Case users
        Case "chris": MsgBox "ok"
        Case "david": MsgBox "ok"
        Case "sam": MsgBox "ok"            
        Case Else: Application.Run ("read_only")
    End Select
End Sub
This is the sub function in the Addin
Sub read_only()
   file_name = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
   Status = ActiveWorkbook.readonly

   ActiveWorkbook.Close ' (the problem is here because also this sub stops when my workbook closes)
   If Status = False Then
       Workbooks.Open fileName:=file_name, readonly:=True 
    Else
       Workbooks.Open fileName:=file_name, readonly:=False  
   End If
End Sub
Has anyone an idea how to solve this?
解决方案
You can do it directly :)

Try this
Private Sub Workbook_Open()
Users = Environ("USERNAME")
    Select Case Users
        Case "chris": MsgBox "ok"
        Case "david": MsgBox "ok"
        Case "sam": MsgBox "ok"
        Case Else
        Application.DisplayAlerts = False
        On Error Resume Next
       'may already be read only
        If ThisWorkbook.Path <> vbNullString Then ThisWorkbook.ChangeFileAccess xlReadOnly
        On Error GoTo 0
        Application.DisplayAlerts = True
    End Select
End Sub


                        
这篇关于如何为某些用户打开excel文件读写，并为其他用户只读的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！