GBKEE

¦^´_ 4# j88141
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Private Sub Workbook_Open()    'ThisWorkbook ¼Ò²Õªºµ{¦¡½X,¶}Àɮɦ۰ʰõ¦æ
   
3.     Dim xWeek, xM, Rng(0 To 2) As Range, i As Integer, ii As Integer
   
4.     For Each xM In ActiveWorkbook.Names
   
5.         xM.Delete
   
6.     Next
   
7.     xWeek = Split("¬P´Á¤@,¬P´Á¤G,¬P´Á¤T,¬P´Á¥|,¬P´Á¤­", ",")
   
8.     xM = Split("¦­¤W,¤U¤È,±ß¤W", ",")
   
9.    
   
10.     With Sheets("½ÒªíÂú§Î")
    
11.         Set Rng(0) = .Range("D3:D18")       '¦­¤W®É¬q  '¦p¦³¤£¹ï½Ð¦Û¦æ­×§ï
    
12.         Set Rng(1) = .Range("D19:D34")      '¤U¤È®É¬q
    
13.         Set Rng(2) = .Range("D35:D42")      '±ß¤W®É¬q
    
14.         For i = 0 To 4
    
15.             For ii = 0 To 2
    
16.                 Rng(ii).Offset(, i).Name = xWeek(i) & xM(ii)
    
17.             Next
    
18.         Next
    
19.     End With
    
20. End Sub

½Æ»s¥N½X

"½ÒªíÂú§Î" ¤u§@ªí¼Ò²Õªºµ{¦¡½X

1. Option Explicit Private
   
2. Sub Worksheet_Change(ByVal Target As Range)
   
3.     Application.EnableEvents = False
   
4.     If ®É¬q(Target) Then Target = ""
   
5.     Application.EnableEvents = True
   
6. End Sub
   
7. Private Function ®É¬q(xRng As Range) As Boolean   '¶Ç¦^ False ¦¨¬° 0 ¡A¦Ó True ¦¨¬° -1
   
8.     Dim xR As String, N As Name, C As CheckBox
   
9.     For Each N In ActiveWorkbook.Names
   
10.         If Not Application.Intersect(Range(N), xRng) Is Nothing Then
    
11.             xR = N.Name              '¨ú±o ¬P´Á¤W¤U±ßªº®É¬q
    
12.             Exit For
    
13.         End If
    
14.     Next
    
15.     With Sheets("¦Ñ®v¤£±Æ½Ò®É¬q")
    
16.         For Each C In .CheckBoxes
    
17.            If .Cells(C.TopLeftCell.Row, 1) = xRng And C = 1 Then
    
18.                If xR = .Cells(1, C.TopLeftCell.Column).MergeArea.Cells(1) & C.TopLeftCell.End(xlUp) Then
    
19.                     '¬P´Áªº®É¬q    .Cells(1, C.TopLeftCell.Column).MergeArea.Cells(1)
    
20.                     '¤W¤U±ßªº®É¬q  C.TopLeftCell.End(xlUp)
    
21.                     ®É¬q = True
    
22.                     MsgBox xRng & vbLf & xR & vbLf & "¤£±Æ½Òµ{"
    
23.                     Exit For
    
24.                 End If
    
25.            End If
    
26.         Next
    
27.     End With
    
28. End Function

½Æ»s¥N½X