返回列表 上一主題 發帖

[發問] 工作表索引標籤 VB問題

[發問] 工作表索引標籤 VB問題

請問 『工作表索引標籤』 用VB程式設定不顯示(關閉)工作表索引標籤 ,
若要顯示工作表索引標籤時, 其他人員無法從 EXCEL選項中勾選 『顯示工作表索引標籤』( 如下圖範例 )
只能有管理者權限( 輸入密碼) 才能開啟『工作表索引標籤』 , 請各位VB前輩高手協助要如何寫 ~

    ActiveWindow.DisplayWorkbookTabs = False ( 關閉)
    ActiveWindow.DisplayWorkbookTabs = True  (開啟)

[attach]37378[/attach]

thumbnail.png
2024-1-29 23:05

TOP

回復 1# lkkchf


    謝謝前輩發表此主題
VB程式設定不顯示(關閉)工作表索引標籤目的為何?
如果是為了不讓使用者變換工作表,可以(隱藏其他工作表+保護活頁簿)或許可以有相似的效果
用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP

回復 3# Andy2483


   A大您好 ,  使用VB程式主要是工作上可使用巨集按鈕快速切換是否顯示工作表索引標籤 ,  但只限管理者有權限(輸入密碼) 可顯示有哪些工作表  , 還請前輩指點 感恩

TOP

本帖最後由 Andy2483 於 2024-1-30 16:17 編輯

回復 4# lkkchf


    謝謝前輩回復,以下替代方案請試試看  (限管理者有權限(輸入密碼)的需求: 以 取消保護活頁簿的輸入密碼作管控)
PS:後學也想知道什麼代碼可以控制 選項>>進階選項 不讓使用者改變

Sub 不顯示_工作表索引標籤()
Dim xS As Worksheet
If ActiveWorkbook.ProtectWindows Or ActiveWorkbook.ProtectStructure Then Exit Sub
For Each xS In Worksheets
   If Not xS Is ActiveSheet Then xS.Visible = False
Next
ActiveWorkbook.Protect "0000", Structure:=True, Windows:=True
ActiveWindow.DisplayWorkbookTabs = False
End Sub

Sub 顯示_工作表索引標籤()
Dim xS As Worksheet, acS As Worksheet
If ActiveWorkbook.ProtectWindows Or ActiveWorkbook.ProtectStructure Then
   MsgBox "請先取消保護活頁簿!": Exit Sub
End If
Set acS = ActiveSheet
For Each xS In Worksheets
   xS.Visible = True
Next
acS.Activate
ActiveWindow.DisplayWorkbookTabs = True
End Sub
20240130_1.jpg
2024-1-30 16:12
用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP

回復 5# Andy2483


剛測試不顯示_工作表索引標籤後, 仍可從EXCEL選項中也勾選 『顯示工作表索引標籤』
使用VB程式希望設定管理者權限密碼後,
從EXCEL選項中也無法勾選『顯示工作表索引標籤』
以避免其他人員開啟工作表索引標籤 (只有管理者密碼才可)
另外在執行巨集按鈕時只有1個
例如: 執行程式時會開窗詢問  『是否顯示工作表索引標籤 ? 』
當點選 是(Y)  --> 輸入密碼確認無誤後 -->顯示工作表索引標籤
當點選 否(N)  --> 輸入密碼確認無誤後 -->隱藏工作表索引標籤

TOP

回復 5# Andy2483

前面問題可能敘述說明或表達時不是很明確 , 茲截圖說明如下提供 給大師您參考 ~

範例

範例.jpg
2024-1-30 18:57

TOP

本帖最後由 Andy2483 於 2024-1-31 10:18 編輯

回復 7# lkkchf


    謝謝論壇,謝謝前輩再回復,一起學習
後學藉此帖學習到很多知識,學習方案如下,請前輩參考
20240131_1.jpg
2024-1-31 10:17


Option Explicit
#If Win64 Then
   Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
   Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
   Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
   Private Declare PtrSafe Function timeSetEvent Lib "winmm.dll" (ByVal uDelay As Long, ByVal uResolution As Long, ByVal lpFunction As LongPtr, ByVal dwUser As LongPtr, ByVal uFlags As Long) As Long
   Private Declare PtrSafe Function timeKillEvent Lib "winmm.dll" (ByVal uID As Long) As Long
   #Else
   Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
   Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
   Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
   Private Declare Function timeSetEvent Lib "winmm.dll" (ByVal uDelay As Long, ByVal uResolution As Long, ByVal lpFunction As Long, ByVal dwUser As Long, ByVal uFlags As Long) As Long
   Private Declare Function timeKillEvent Lib "winmm.dll" (ByVal uID As Long) As Long
#End If
Private Const Em_SetPassWordChar = &HCC
Dim lTimeID As Long
Const pswdInputBoxTitle = "pswdInputBox"
Sub TimeProc(ByVal uID As Long, ByVal uMsg As Long, ByVal dwUser As Long, ByVal dw1 As Long, ByVal dw2 As Long)
Dim hwd As LongPtr
hwd = FindWindow("#32770", pswdInputBoxTitle)
If hwd <> 0 Then hwd = FindWindowEx(hwd, 0, "Edit", vbNullString): SendMessage hwd, Em_SetPassWordChar, 42, 0:  timeKillEvent lTimeID
End Sub
Function pswdInputBox() As Variant
lTimeID = timeSetEvent(10, 50, AddressOf TimeProc, 1, 1)
pswdInputBox = InputBox(Prompt:="請輸入管理員密碼", Title:=pswdInputBoxTitle)
End Function
'=========================================================
Sub 工作表索引標籤_S_H()
Dim xS As Worksheet, acS As Worksheet
If ActiveWorkbook.ProtectWindows Or ActiveWorkbook.ProtectStructure Then
   If pswdInputBox <> "12345" Then Exit Sub
   ActiveWorkbook.Unprotect "0000"
   Set acS = ActiveSheet
   For Each xS In Worksheets: xS.Visible = True: Next
   acS.Activate: ActiveWindow.DisplayWorkbookTabs = True: Exit Sub
End If
For Each xS In Worksheets: xS.Visible = IIf(Not xS Is ActiveSheet, False, True): Next
ActiveWorkbook.Protect "0000", Structure:=True, Windows:=True
ActiveWindow.DisplayWorkbookTabs = False
End Sub
'=========================================================
Sub 顯示_工作表1()
Dim xS As Worksheet, acS As Worksheet
ActiveWorkbook.Unprotect "0000": Set acS = Sheets("工作表1"): acS.Visible = True: acS.Activate
For Each xS In Worksheets: xS.Visible = IIf(Not xS Is acS, False, True): Next
ActiveWorkbook.Protect "0000", Structure:=True, Windows:=True: ActiveWindow.DisplayWorkbookTabs = False
End Sub
'=========================================================
Sub 顯示_工作表2()
Dim xS As Worksheet, acS As Worksheet
ActiveWorkbook.Unprotect "0000": Set acS = Sheets("工作表2"): acS.Visible = True: acS.Activate
For Each xS In Worksheets: xS.Visible = IIf(Not xS Is acS, False, True): Next
ActiveWorkbook.Protect "0000", Structure:=True, Windows:=True: ActiveWindow.DisplayWorkbookTabs = False
End Sub
'=========================================================
Sub 顯示_工作表3()
Dim xS As Worksheet, acS As Worksheet
ActiveWorkbook.Unprotect "0000": Set acS = Sheets("工作表3"): acS.Visible = True: acS.Activate
For Each xS In Worksheets: xS.Visible = IIf(Not xS Is acS, False, True): Next
ActiveWorkbook.Protect "0000", Structure:=True, Windows:=True: ActiveWindow.DisplayWorkbookTabs = False
End Sub
參考:
https://forum.twbts.com/viewthre ... amp;page=1#pid29595
用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP

回復 8# Andy2483

辛苦您了~ 可否提供您的EXCEL檔案 讓學生學習您寫得VB程式範例~ 感恩

TOP

回復 9# lkkchf
20240131.zip (28.19 KB)
用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP

        靜思自在 : 人的眼睛長在前面,只看到別人的缺點,絲毫看不到自己的缺點。
返回列表 上一主題