今天又承蒙一個業主賞飯吃了 (雖然最後沒吃到)，就順便分享一下新學到的東西，取之社群回饋部落格~
本文主要是要說明如果在一個 .xlsm 檔案裡面，依內建的帳號密碼表，給予每位使用者存取不同的活頁簿。
Excel VBA 做帳號認証，依權限瀏覽
首先就給大家觀看程式運作時的畫面截圖，瞭解這個程式的用途。
程式啟動畫面
檔案開啟後，首先會看到【登入】和【隱藏】按鈕。【登入】會跳出視窗，提示輸入帳號密碼來登入。若登入成功，則會在下面的活頁簿列表，顯示其同名的活頁簿。而【隱藏】，則類似登出的功能，會隱藏除了ui外的所有活頁簿。
登入帳號
按下登入後的登入視窗，用戶名稱就是活頁簿的名稱 (除 ui 和 passwd 活頁簿)。密碼則是存於隱藏的 passwd 活頁簿內。
以蔡小英登入後，即可看到自己的活頁簿
上面的例子以蔡小英的帳號登入，就可以看到【蔡小英】的活頁簿了。
再以管理員登入，以觀看所有活業簿
再以用戶【管理員】登入，這個內建帳號可以觀看所有的活頁簿。
下面就會有ui,passwd,蔡小英三個活頁簿
使用【管理員】登入後，就可以看到下面全部三個活頁簿了。
passwd就是記載密碼
而其中一個最重要的，就是「passwd」這個活頁簿，記載了每個人的密碼。
功能說明與限制
業主的原始目的，是要將所有業務的資料都記在同一個檔案裡，但又不希望業務可以看到別人的資料，故有此需求。
這個程式另外有加了一個限制，只能存檔，而無法另存新檔。為什麼呢？因為另存新檔成 .xlsx 格式時，就無法以 VBA 來限制對活頁簿的存取，使得每個人都可以看到所有的資料，而造成資料外洩。可能的變通方法，是在另存新檔時，把非登入者擁有的資料都清除再存檔，這可能就要依每個人的需求了。
程式技術解說
程式碼加鎖
避免被破解的第一步，就是要把避免VBA的程式碼被修改移除。要將VBA程式碼保護，只要在 VBA 專案視窗內的選取工作表的屬性，就可以設置密碼了。
開啟Excel VBA設計介面
工作表上按右鍵選取屬性
在保護頁籤上設定密碼
下次進設計視窗時，要檢視程式碼就需要輸入密碼
進階活頁簿隱藏
這個程式控制使用者該看見哪些資料的方式，是使用隱藏活頁簿的方式。一般使用者通常是直接在活頁簿上按右鍵再選「隱藏」來達到這目的，但這種方式可以被一般的使用者任意解除隱藏。
一般的隱藏活頁簿
另一種進階的隱藏方式，則是透過程式介面或VBA才能解除，這就可以避免使用者在不具權限下去看到不該看的資料。目前活頁簿的隱藏設定，可以透過程式設計頁面，觀看活頁簿屬性而得知。
活頁簿的隱藏屬性
這個屬性有包含三種
- xlSheetVisible: 可看見的狀態
- xlSheetHidden: 一般的隱藏
- xlSheetVeryHidden: 只能透過程式或設計介面解除隱藏
隱藏按鈕的程式碼
在主畫面中有個「隱藏」，就是把除了”ui”外的所有活頁簿，都隱藏起來。其程式碼如下
|
1 2 3 4 5 6 |
For I = 1 To Worksheets.Count sname = Worksheets(I).Name '活頁簿名稱 <> "ui" If sname <> "ui" Then Worksheets(I).Visible = xlSheetVeryHidden End If Next I |
解鎖行為
當我們按下【登入】按鈕時，首先會跳出一個帳號密碼視窗，這個我們稍後再談。當取得帳號密碼後，就會跟 “passwd” 活頁簿裡的資料比對。符合的話，就會解除隱藏以該用戶名稱命名的活頁簿。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Private Sub unhide_by_user(user As String) '解隱藏使用者活頁簿. 管理員則全部解除 For I = 1 To Worksheets.Count sname = Worksheets(I).Name '活頁簿名稱與用戶相同. 或使用者為管理員. 解除隱藏 If sname = user Or user = "管理員" Then Worksheets(I).Visible = xlSheetVisible End If Next I End Sub Private Sub CommandButton1_Click() '登入按鈕 Dim password As String Dim user As String passform.password.Text = "" '將帳號目錄表單的密碼清除. passform.Show '呼叫顯示帳號密碼表單 '取得使用者的輸入 user = passform.user.Text password = passform.password.Text '比較密碼 If PasswordVerify(user, password) = False Then MsgBox "密碼錯誤" Exit Sub End If '解隱藏除以用戶名稱命名的表單 unhide_by_user (user) End Sub |
帳號密碼表單
這個功能的一個特色就是會提示使用者名稱和密碼以星號顯示。主要的目的還是為了克服一般 inputbox 無法在輸入密碼時顯示成星號，附加好處是可以讓用戶以列表的方式選擇使用者，而不需自行輸入。
選擇用戶與遮罩密碼
為了讓密碼欄在輸入時顯示星號，需在其屬性欄位輸入一個星號，就可以了。
設置密碼遮罩
程式碼本身也算簡單，在按下確定鍵後會隱藏表單。在表單跳出時，會重新掃描所有活頁簿，並將其名稱列在使用者列表裡。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Private Sub CommandButton1_Click() '確定後隱藏表單 passform.Hide End Sub Private Sub UserForm_Activate() passform.user.Clear '掃描所有活頁簿名稱，除了 "ui" 與 "passwd"外，加入用戶列表 For I = 1 To Worksheets.Count sname = Worksheets(I).Name If sname <> "passwd" And sname <> "ui" Then passform.user.AddItem (sname) End If Next I '加入一個管理員的用戶 passform.user.AddItem ("管理員") passform.user.ListIndex = 0 End Sub |
工作簿存檔保護
前面有提到，另存新檔成非 .xlsm 檔，會導致資料外洩。因此，我們不允除管理員外的帳號進行另存新檔的動作，但一般使用者還是可以執行存檔。我們可以在工作簿的 Workbook_BeforeSave() 方法裡，加入適當的檢查來達到這個需求。
禁止非管理員另存新檔
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim password As String Dim user As String user = "管理員" password = passform.password.Text '比對最後登入的帳號是否為管理員 If ui.PasswordVerify(user, password) = False Then '如果不是管理員，又是另存新檔，則跳出警示禁止存檔 If SaveAsUI = True Then MsgBox "因資料安全，只有管理員身份登入才可以存檔" Cancel = True End If End If End Sub |
檔案下載
請到這裡下載
管理員的密碼是 123456
VBA 程式保護的密碼是 mypass
結語
透過上面的程式與設定，讓 Excel 也可以達到一個基本的權限管理。但還是要說，這種方式還是小不懂騙大不懂，終究還是有辦法破解的。算是在 Excel VBA 的小玩意吧~~~
感謝分享~!!
很高興對您有幫助~
我寫的 vba 或excel 只想在某電腦 被特定使用 就是不想檔案被複製到其他電腦也被使用
有這種功能嗎 我看過 某個檔案需要輸入帳密 但我不確定 複製到其他電腦能不能使用 因為我沒帳密可以驗證當時那個檔案
應該只能密碼保護，無法防止複製喔。或許你可以偷產生一個檔案在特定位置，執行時檢查一下，這也算是一種變通方法。