|
本帖最后由 feiaoli 于 2024-2-25 15:27 编辑
请各位大佬伸出友爱的援助之手,不胜感激!
1、宏运行工作表保护后允许自动帅选。
2、保护时候去掉 "选定锁定单元格"勾选。
3、保护密码是1234
- Private Sub Worksheet_Change(ByVal Target As Range)
- ActiveSheet.Unprotect "1234" '解除工作表保护1234是保护密码
- Dim X As Integer '解除所有工作表保护
- For X = 1 To Sheets.Count '解除所有工作表保护
- ' Sheets(X).Unprotect "1234" '解除所有工作表保护
- Next X '解除所有工作表保护
- Dim sht As Worksheet
- Dim sh As Worksheet
- 'Dim sht As Worksheet
- 'Dim sh As Worksheet
- Set sht = ThisWorkbook.Sheets("数据库")
- Set sh = ThisWorkbook.Sheets("县级预算项目资金支出计划申请书")
- Application.EnableEvents = False
- If Target.Address = "$E$3" And Not IsEmpty(Target) Then
- With sht
- r = .Cells(.Rows.Count, "AV").End(3).Row
- For i = 2 To r
- If .Cells(i, "AV").Value = Target.Value Then
- arr = .Cells(i, 1).Resize(, 64)
- MsgBox "找到位置,在数据库的第:" & i & " 行"
- Exit For
- End If
- Next
- End With
-
- If Not IsEmpty(arr) Then
- With sh
- .[b2] = Mid(arr(1, 1), 9, 11)
- .[e2] = arr(1, 2)
- .[b3] = arr(1, 50)
- .[b4] = arr(1, 52)
- .[b5] = arr(1, 45)
- .[b6] = arr(1, 4)
- .[d4] = arr(1, 56)
- .[d5] = arr(1, 46)
- .[d6] = arr(1, 12) + arr(1, 13)
- ' .[f4] = arr(1, 56)
- ' .[f5] = arr(1, 56)
- ' .[f6] = arr(1, 56)
- .[a7] = "预算单位意见(领导签字盖公章):" + arr(1, 50)
- End With
- Else
- MsgBox "没有找到你输入的指标号,退出!"
- With sh
- .[b2:c2] = ""
- .[e2:f2] = ""
- .[b3:c3] = ""
- .[b4] = ""
- .[b5] = ""
- .[b6] = ""
- .[d4] = ""
- .[d5] = ""
- .[d6] = ""
- .[f4] = ""
- .[f5] = ""
- .[f6] = ""
- .[a7:f7] = ""
- End With
- End If
- ElseIf Target.Address = "$E$3" And IsEmpty(Target) Then
- With sh
- .[b2:c2] = ""
- .[e2:f2] = ""
- .[b3:c3] = ""
- .[b4] = ""
- .[b5] = ""
- .[b6] = ""
- .[d4] = ""
- .[d5] = ""
- .[d6] = ""
- .[f4] = ""
- .[f5] = ""
- .[f6] = ""
- .[a7:f7] = ""
- End With
- End If
- Application.EnableEvents = True
- ActiveSheet.Protect "1234" '工作表保护1234是保护密码
- AllowFiltering = True '密码保护后允许筛选
- Dim b As Integer '保护所有工作表
- For b = 1 To Sheets.Count '保护所有工作表
- Sheets(b).Protect "1234" '保护所有工作表
- Next b '保护所有工作表
- End Sub
复制代码
再次谢谢taller老师无私的帮助。
|
|