Excel VBA 多使用者權限控制

今天又承蒙一個業主賞飯吃了 (雖然最後沒吃到),就順便分享一下新學到的東西,取之社群回饋部落格~

本文主要是要說明如果在一個 .xlsm 檔案裡面,依內建的帳號密碼表,給予每位使用者存取不同的活頁簿。

Excel VBA 做帳號認証,依權限瀏覽

首先就給大家觀看程式運作時的畫面截圖,瞭解這個程式的用途。

 

程式啟動畫面

檔案開啟後,首先會看到【登入】和【隱藏】按鈕。【登入】會跳出視窗,提示輸入帳號密碼來登入。若登入成功,則會在下面的活頁簿列表,顯示其同名的活頁簿。而【隱藏】,則類似登出的功能,會隱藏除了ui外的所有活頁簿。

登入帳號

按下登入後的登入視窗,用戶名稱就是活頁簿的名稱 (除 ui 和 passwd 活頁簿)。密碼則是存於隱藏的 passwd 活頁簿內。

以蔡小英登入後,即可看到自己的活頁簿

上面的例子以蔡小英的帳號登入,就可以看到【蔡小英】的活頁簿了。

再以管理員登入,以觀看所有活業簿

再以用戶【管理員】登入,這個內建帳號可以觀看所有的活頁簿。

下面就會有ui,passwd,蔡小英三個活頁簿

使用【管理員】登入後,就可以看到下面全部三個活頁簿了。

passwd就是記載密碼

而其中一個最重要的,就是「passwd」這個活頁簿,記載了每個人的密碼。

功能說明與限制

業主的原始目的,是要將所有業務的資料都記在同一個檔案裡,但又不希望業務可以看到別人的資料,故有此需求。

這個程式另外有加了一個限制,只能存檔,而無法另存新檔。為什麼呢?因為另存新檔成 .xlsx 格式時,就無法以 VBA 來限制對活頁簿的存取,使得每個人都可以看到所有的資料,而造成資料外洩。可能的變通方法,是在另存新檔時,把非登入者擁有的資料都清除再存檔,這可能就要依每個人的需求了。

程式技術解說

程式碼加鎖

避免被破解的第一步,就是要把避免VBA的程式碼被修改移除。要將VBA程式碼保護,只要在 VBA 專案視窗內的選取工作表的屬性,就可以設置密碼了。

開啟Excel VBA設計介面

工作表上按右鍵選取屬性

在保護頁籤上設定密碼

下次進設計視窗時,要檢視程式碼就需要輸入密碼

 

進階活頁簿隱藏

這個程式控制使用者該看見哪些資料的方式,是使用隱藏活頁簿的方式。一般使用者通常是直接在活頁簿上按右鍵再選「隱藏」來達到這目的,但這種方式可以被一般的使用者任意解除隱藏。

一般的隱藏活頁簿

另一種進階的隱藏方式,則是透過程式介面或VBA才能解除,這就可以避免使用者在不具權限下去看到不該看的資料。目前活頁簿的隱藏設定,可以透過程式設計頁面,觀看活頁簿屬性而得知。

活頁簿的隱藏屬性

這個屬性有包含三種

  1. xlSheetVisible: 可看見的狀態
  2. xlSheetHidden: 一般的隱藏
  3. xlSheetVeryHidden: 只能透過程式或設計介面解除隱藏
隱藏按鈕的程式碼

在主畫面中有個「隱藏」,就是把除了”ui”外的所有活頁簿,都隱藏起來。其程式碼如下

解鎖行為

當我們按下【登入】按鈕時,首先會跳出一個帳號密碼視窗,這個我們稍後再談。當取得帳號密碼後,就會跟 “passwd” 活頁簿裡的資料比對。符合的話,就會解除隱藏以該用戶名稱命名的活頁簿。

帳號密碼表單

這個功能的一個特色就是會提示使用者名稱和密碼以星號顯示。主要的目的還是為了克服一般 inputbox 無法在輸入密碼時顯示成星號,附加好處是可以讓用戶以列表的方式選擇使用者,而不需自行輸入。

選擇用戶與遮罩密碼

為了讓密碼欄在輸入時顯示星號,需在其屬性欄位輸入一個星號,就可以了。

設置密碼遮罩

程式碼本身也算簡單,在按下確定鍵後會隱藏表單。在表單跳出時,會重新掃描所有活頁簿,並將其名稱列在使用者列表裡。

 

工作簿存檔保護

前面有提到,另存新檔成非 .xlsm 檔,會導致資料外洩。因此,我們不允除管理員外的帳號進行另存新檔的動作,但一般使用者還是可以執行存檔。我們可以在工作簿的 Workbook_BeforeSave() 方法裡,加入適當的檢查來達到這個需求。

禁止非管理員另存新檔

 

檔案下載

請到這裡下載

管理員的密碼是 123456

VBA 程式保護的密碼是 mypass

結語

透過上面的程式與設定,讓 Excel 也可以達到一個基本的權限管理。但還是要說,這種方式還是小不懂騙大不懂,終究還是有辦法破解的。算是在 Excel VBA 的小玩意吧~~~

Leave a Reply