Excel Forum

Code: Select all

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Set Target = Range("B2")

'รับงานเข้า
If Range("B3").Value = "" Then
Sheets("Sheet1").Shapes("rectangle 1").Visible = msoTrue
Else
  Sheets("Sheet1").Shapes("rectangle 1").Visible = msoFalse
End If


'เริ่มตรวจงาน
If Range("B3").Value <> "" Then
Sheets("Sheet1").Shapes("rectangle 2").Visible = msoTrue
Else
  Sheets("Sheet1").Shapes("rectangle 2").Visible = msoFalse
End If

'เสร็จงาน
If Range("B4").Value <> "" Then
Sheets("Sheet1").Shapes("rectangle 3").Visible = msoTrue
Else
  Sheets("Sheet1").Shapes("rectangle 3").Visible = msoFalse
End If

'ส่งงาน
If Range("B5").Value <> "" Then
Sheets("Sheet1").Shapes("rectangle 4").Visible = msoTrue
Else
  Sheets("Sheet1").Shapes("rectangle 4").Visible = msoFalse
End If
End Sub

Code: Select all

Private Sub Workbook_Open()
    With Worksheets("Sheet1")
        .Shapes("Rectangle 1").Visible = True
        .Shapes("Rectangle 2").Visible = False
        .Shapes("Rectangle 3").Visible = False
        .Shapes("Rectangle 4").Visible = False
    End With
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Range("b3:b6")) Is Nothing Then
        
        If Application.CountIfs(Range("b3:b6"), "") = 4 Then
            With Worksheets("Sheet1")
                .Shapes("Rectangle 1").Visible = True
                .Shapes("Rectangle 2").Visible = False
                .Shapes("Rectangle 3").Visible = False
                .Shapes("Rectangle 4").Visible = False
            End With
            Exit Sub
        End If
        
        Select Case Target.Address(0, 0)
            Case "B3"
                'เริ่มตรวจงาน
                If Range("b3").Value <> "" Then
                    Sheets("Sheet1").Shapes("rectangle 2").Visible = True
                    Sheets("Sheet1").Shapes("rectangle 1").Visible = False
                End If
            Case "B4"
                'เสร็จงาน
                If Range("b4").Value <> "" Then
                    Sheets("Sheet1").Shapes("rectangle 3").Visible = True
                    Sheets("Sheet1").Shapes("rectangle 2").Visible = False
                End If
            Case "B5"
                'ส่งงาน
                If Range("b5").Value <> "" Then
                    Sheets("Sheet1").Shapes("rectangle 4").Visible = True
                    Sheets("Sheet1").Shapes("rectangle 3").Visible = False
                End If
            Case "B6"
                With Worksheets("Sheet1")
                    .Shapes("Rectangle 1").Visible = False
                    .Shapes("Rectangle 2").Visible = False
                    .Shapes("Rectangle 3").Visible = False
                    .Shapes("Rectangle 4").Visible = False
                End With
        End Select
    End If
End Sub