|
楼主 |
发表于 2023-3-14 16:25
|
显示全部楼层
本帖最后由 snqig 于 2023-3-14 16:58 编辑
Private Sub Workbook_Open()
Dim user As String
Dim password As String
Dim ws As Worksheet
Dim hideColumns As Variant
Dim protectSheet As Boolean
' Define the array of columns to hide/protect
hideColumns = Array("税率%", "含税单价", "未税单价", "净价合计", "已开票金额", _
"价税合计", "交货日期", "已交货", "结存数量", "已交货金额", _
"未税合计已交货", "未开票金额", "结存金额", "未税已交货总价")
' Prompt user for username and password
user = InputBox("Enter your username:")
password = InputBox("Enter your password:")
' Check username and password
If user = "admin" And password = "admin" Then
' Show only the "密码表" worksheet and hide others
For Each ws In ThisWorkbook.Worksheets
If ws.name <> "密码表" Then
ws.Visible = xlSheetHidden
End If
Next ws
Else
' Check if user exists in "密码表" worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.name = "密码表" Then
If ws.Range("A:A").Find(user) Is Nothing Then
' User not found, show error message and close workbook
MsgBox "Invalid username or password."
ThisWorkbook.Close False
Exit Sub
Else
' User found, check password
If ws.Range("B" & ws.Range("A:A").Find(user).Row) <> password Then
' Incorrect password, show error message and close workbook
MsgBox "Invalid username or password."
ThisWorkbook.Close False
Exit Sub
Else
' Correct password, hide "密码表" worksheet and unhide others
ws.Visible = xlSheetHidden
For Each ws In ThisWorkbook.Worksheets
If ws.name <> "密码表" Then
ws.Visible = xlSheetVisible
End If
Next ws
' Determine if user has "ON" or "OK" permission
protectSheet = True
If ws.Range("C" & ws.Range("A:A").Find(user).Row) = "OK" Then
protectSheet = False
End If
' Hide/protect specified columns
For Each ws In ThisWorkbook.Worksheets
If ws.name <> "密码表" Then
For Each col In hideColumns
If ws.Range("1:1").Find(col) Is Nothing Then
MsgBox "Column " & col & " not found in worksheet " & ws.name & "."
Else
ws.Range(ws.Range("1:1").Find(col), ws.Range("1:1").Find(col).End(xlDown)).EntireColumn.Hidden = protectSheet
ws.Protect password:="password"
End If
Next col
End If
Next ws
Exit Sub
End If
End If
End If
Next ws
End If
End Sub
Sub Workbook_BeforeClose_1()
'取消保护所有工作表
For Each ws In Worksheets
ws.Unprotect
Next ws
'取消隐藏保护指定列
Dim hideColumns As Variant
hideColumns = Array("税率%", "含税单价", "未税单价", "净价合计", "已开票金额", "价税合计", "交货日期", "已交货", "结存数量", "已交货金额", "未税合计已交货", "未开票金额", "结存金额", "未税已交货总价")
For Each ws In Worksheets
If ws.Visible = xlSheetVisible Then '只对可见工作表执行操作
For Each colName In hideColumns
If Not ws.Range("1:1").Find(colName) Is Nothing Then '判断列是否存在
ws.Columns(ws.Range("1:1").Find(colName).Column).Hidden = False '取消隐藏列
ws.Protect '保护工作表
End If
Next colName
End If
Next ws
End Sub
Sub Workbook_BeforeClose_2()
'隐藏指定工作表并取消保护所有工作表
For Each ws In Worksheets
If ws.name = "表" Then
ws.Visible = xlSheetVisible '显示指定工作表
'取消保护所有工作表
For Each s In ActiveWorkbook.Sheets
s.Unprotect
Next s
'取消隐藏保护指定列
Dim hideColumns As Variant
hideColumns = Array("税率%", "含税单价", "未税单价", "净价合计", "已开票金额", "价税合计", "交货日期", "已交货", "结存数量", "已交货金额", "未税合计已交货", "未开票金额", "结存金额", "未税已交货总价")
For Each s In ActiveWorkbook.Sheets
If s.Visible = xlSheetVisible Then '只对可见工作表执行操作
For Each colName In hideColumns
If Not s.Range("1:1").Find(colName) Is Nothing Then '判断列是否存在
s.Columns(s.Range("1:1").Find(colName).Column).Hidden = True '隐藏列
s.Protect '保护工作表
End If
Next colName
End If
Next s
Else
ws.Visible = xlSheetHidden '隐藏其他工作表
End If
Next ws
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Workbook_BeforeClose_1
Call Workbook_BeforeClose_2
End Sub
上面代码 For Each ws In
错误代码 能我查一下吗
|
|