Posts by Chrisowen

Private Sub lbxCaseSearch_Click()
'Selection.AutoFilter Field:=1, Criteria1:="<>"
    Dim r As Range, rAll As Range, lRow As Long
    Dim sTerm As String, ctl As Control
    sTerm = lbxCaseSearch.Value
     
    With Sheets("Case Log")
        Set rAll = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
        For Each r In rAll
            If r = sTerm Then
                lRow = r.Row
                For Each objFrame In Me.Controls
                    If TypeName(objFrame) = "Frame" Then
                        For Each ctl In Me(objFrame.Name).Controls
                             If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
                                If ctl.Tag <> "" Then
                            ctl.ControlSource = ctl.Tag & lRow
                        End If
                    End If
                Next ctl
            End If
        Next objFrame
        End If
    Next
   End With
End Sub

Private Sub btnEdit_Click()
Dim lRow As Long
    Dim ctl As Control
    lRow = Worksheets("Case Log").Range("A1").CurrentRegion.Rows.Count + 1
    For Each ctl In Frame1.Controls
          If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
                If ctl.Tag <> "" Then
                    ctl.ControlSource = ctl.Tag & lRow
           End If
        End If
    Next ctl
    Unload Me
End Sub

Private Sub lbxCaseSearch_Click()
Selection.AutoFilter Field:=1, Criteria1:="<>"
    Dim r As Range, rAll As Range, lRow As Long
    Dim sTerm As String, ctl As Control
    sTerm = lbxCaseSearch.Value
     
    With Sheets("Case Log")
        Set rAll = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
        For Each r In rAll
            If r = sTerm Then
                lRow = r.Row
                For Each ctl In Frame1.Controls
                    If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
                        If ctl.Tag <> "" Then
                            ctl.ControlSource = ctl.Tag & lRow
                        End If
                    End If
                Next ctl
            End If
        Next r
    End With
    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     
    If Target.Column > 1 Or Target.Value = "" Then Exit Sub
     
    With Sheets("Overtime Report")
        .Range("D18") = Target
        .Range("D22") = Target.Offset(, 1)
        .Range("G22") = Target.Offset(, 2)
        .Range("K17") = Target.Offset(, 3)
        .Range("M1") = Target.Offset(, 6)
        .Range("G18") = Target.Offset(, 9)
    With Sheets("Admin")
        .Range("M2") = Target.Offset(, 3)
        .Range("N2") = Target.Offset(, 4)
    End With
  End With
End Sub

Option Explicit




Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H5")) Is Nothing Then
        ChangeCheckBoxes Worksheets("Overtime Report"), True, Target.Value
    End If
End Sub




Sub ChangeCheckBoxes(xlWs As Worksheet, bVal As Boolean, Optional objName As String = vbNullString)
    Dim oOle As OLEObject
    With xlWs
        For Each oOle In .OLEObjects
            If InStr(LCase(oOle.progID), "checkbox") > 0 Then
                If LCase(Left(oOle.Name, 3)) = "chk" Then
                    If Mid(oOle.Name, 4) = objName Then
                        oOle.Object.Value = bVal
                    Else
                        oOle.Object.Value = Not bVal
                    End If
                End If
            End If
        Next oOle
    End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String




On Error GoTo EndMacro
If Application.Intersect(Target, Range("B5:E370")) Is Nothing Then
    Exit Sub
End If
If Target.Cells.Count > 1 Then
    Exit Sub
End If
If Target.Value = "" Then
    Exit Sub
End If




Application.EnableEvents = False
With Target
If .HasFormula = False Then
    Select Case Len(.Value)
        Case 1 ' e.g., 1 = 00:01 AM
            TimeStr = "00:0" & .Value
        Case 2 ' e.g., 12 = 00:12 AM
            TimeStr = "00:" & .Value
        Case 3 ' e.g., 735 = 7:35 AM
           TimeStr = Left(.Value, 1) & ":" & _
            Right(.Value, 2)
        Case 4 ' e.g., 1234 = 12:34
            TimeStr = Left(.Value, 2) & ":" & _
            Right(.Value, 2)
        Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
            TimeStr = Left(.Value, 1) & ":" & _
            Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
        Case 6 ' e.g., 123456 = 12:34:56
            TimeStr = Left(.Value, 2) & ":" & _
            Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
        Case Else
            Err.Raise 0
    End Select
    .Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub