统计VBA学习小组正式组第八课（第十讲）的积分帖之作业上交贴（第11周）

寂寞深水鱼

F18寂寞深水鱼.rar (10.2 KB, 下载次数: 2)

2012-3-9 20:58 上传

点击文件名下载附件 F18：寂寞深水鱼

1. Sub 添加保护()
   
2. Worksheets("sheet2").Protect Password:=2
   
3. Worksheets("sheet3").Protect Password:=89
   
4. Worksheets("sheet4").Protect Password:=2
   
5. Worksheets("sheet5").Protect Password:=5
   
6. End Sub

复制代码

1. Sub 隐藏工作表()
   
2. Dim i As Integer
   
3. For i = 1 To Sheets.Count
   
4. If Sheets(i).Name <> "主界面" Then
   
5. Sheets(i).Visible = 0
   
6. End If
   
7. Next i
   
8. End Sub

复制代码

1. Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   
2. Worksheets("主界面").Select
   
3. End Sub

复制代码

无聊的疯子

A03:无聊的疯子

忘了贴代码了...

1. 
   
2. Sub 隐藏工作表()
   
3. Dim Sh As Worksheet
   
4.   For Each Sh In Worksheets
   
5.     If Sh.Name <> "主界面" Then Sh.Visible = 0
   
6.   Next
   
7. End Sub
   
8. Sub 添加密码()
   
9. Dim X As Byte
   
10.   For X = 2 To 5
    
11.     Sheets(Cells(X, 1).Value).Protect Val(Cells(X, 2).Value)
    
12.   Next
    
13. End Sub
    
14. Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
15.   If Sh.Name <> Sheets("主界面").Name Then Sheets("主界面").Select
    
16. End Sub
    

复制代码

无聊的疯子_第9讲作业题.zip (16.22 KB, 下载次数: 4)

2012-3-9 21:04 上传

点击文件名下载附件

awxgaige

第一题代码
Sub 批量工作表添加保护()
Dim x As Integer
  For x = 2 To 5
    Sheets(x).Protect Password:=Sheet1.Range("B" & x)
  Next x
End Sub
第二题代码
Sub 隐藏工作表()
    Sheets(Array("密码表", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).Visible = 0
End Sub
第三题代码
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  If ActiveSheet.Name = "密码表" Or ActiveSheet.Name = "Sheet5" Or ActiveSheet.Name = "Sheet4" Or ActiveSheet.Name = "Sheet3" Or ActiveSheet.Name = "Sheet2" Then
    Sheets(6).Select
   End If
End Sub
第三题的代码if部分这样做肯定的不合理,但我又不知怎么做,请老师明示.本人已经56岁了,可以是最老的学员,外语无基础,单词不记得,只凭一腔热血,请老师不要见笑.

晓梦迷蝶

Sub 保护()
    Dim x As Integer
        For x = 2 To 5
            Sheets(Range("A" & x).Value).Protect Password:=Range("B" & x).Value
        Next x
End Sub
Sub 隐藏()
    Dim x As Integer
        For x = 1 To Sheets.Count
            If Sheets(x).Name <> "主界面" Then
                Sheets(x).Visible = flase
            End If
        Next x
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name <> "主界面" Then
        Sheets("主界面").Select
    End If
End Sub

xxjjdd0000

A01:xxjjdd0000Sub 隐藏工作表()  Application.ScreenUpdating = False
    Sheet1.Visible = xlSheetVeryHidden
    Sheet2.Visible = xlSheetVeryHidden
    Sheet3.Visible = xlSheetVeryHidden
    Sheet4.Visible = xlSheetVeryHidden
    Sheet5.Visible = xlSheetVeryHidden
  Application.ScreenUpdating = True
End Sub
Sub 批量保护()
  Sheets("sheet2").Protect Password:=2
  Sheets("sheet3").Protect Password:=89
  Sheets("sheet4").Protect Password:=2
  Sheets("sheet5").Protect Password:=5
End Sub
Sub 所有工作表取消隐藏()
  Application.ScreenUpdating = False
    Sheet1.Visible = xlSheetVisible
    Sheet2.Visible = xlSheetVisible
    Sheet3.Visible = xlSheetVisible
    Sheet4.Visible = xlSheetVisible
  Application.ScreenUpdating = True
End Sub
Sub 添加保护()
  Dim x As Integer
  For x = 2 To 5
    With Sheets("密码表")
     Sheets(.Range("a" & x).Value).Protect Password:=Range("b" & x).Value
    End With
  Next
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Sheets("主界面").Select
End Sub

awxgaige

awxgaige 发表于 2012-3-9 21:30
第一题代码
Sub 批量工作表添加保护()
Dim x As Integer

自己细想一下,第一题也有问题,目前只能这样了,再研究!

w2001pf

H07:w2001pf 第9讲作业题答题.rar (17.46 KB, 下载次数: 13)

2012-3-10 22:51 上传

点击文件名下载附件

一缕忧兰

A07: 一缕忧兰
第一题

1. 
   
2. Sub 批量添加保护()
   
3. Dim m, i As Integer
   
4. With Sheets("密码表")
   
5.   For i = 2 To 5
   
6.     m = .Cells(i, 1)
   
7.     Sheets(m).Protect .Cells(i, 2)
   
8.   Next i
   
9. End With
   
10. End Sub
    

复制代码

第二题

1. 
   
2. Sub 隐藏工作表()
   
3. Dim i
   
4. For i = 1 To Sheets.Count
   
5.   If Not Sheets(i) Is Sheets("主界面") Then
   
6.     Sheets(i).Visible = False
   
7.   End If
   
8. Next i
   
9. End Sub
   

复制代码

第三题

1. 
   
2. Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   
3. Dim i
   
4. For i = 1 To Sheets.Count
   
5.   If Not Sheets(i) Is Sheets("主界面") Then
   
6.     Sheets("主界面").Select
   
7.   End If
   
8. Next i
   
9. End Sub
   

复制代码

lslly

Sub 批量设置密码()
Dim sh As Worksheet, x As Integer

  For x = 2 To 5
    Sheets(x).Protect Password:=Range("b" & x).Value
  Next x
End Sub

Sub 隐藏工作表()
Dim sh As Worksheet
For Each sh In Sheets
If sh.Name <> "主界面" Then sh.Visible = xlSheetVeryHidden
Next sh
End Sub


Option Explicit
Dim sh As Worksheet

'Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'For Each Sh In Sheets
'    If Sh.Name <> "主界面" Then
'        Sheet6.Activate
'        Range("a1").Select
'    End If
'Next
'
'End Sub

第9讲作业题.rar (16.44 KB, 下载次数: 2)

2012-3-11 01:00 上传

点击文件名下载附件

wenchduan

A组长:wenchduan

1. Sub 添加保护()
   
2.   Dim X As Integer
   
3.   Dim Y As Integer
   
4.     For Y = 2 To 5
   
5.       For X = 2 To 5
   
6.         If Sheets("sheet" & Y).Name = Sheets("密码表").Range("A" & X) Then
   
7.           Sheets("sheet" & Y).Protect Password:=Sheets("密码表").Range("B" & X)
   
8.           Exit For
   
9.         End If
   
10.     Next X, Y
    
11. End Sub
    
12. Sub 隐藏工作表()
    
13.   Sheets(Array("密码表", "SHEET2", "SHEET3", "SHEET4", "SHEET5")).Visible = 0
    
14. End Sub
    
15. Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
16.   Sheets("主界面").Select
    
17. End Sub
    

复制代码