Thema: UserForm-TextBox-Eingaben im Format [hh]:mm in Zellen übertragen

ClassModule: frmHours

Private Sub cmdCancel_Click()
   Unload Me
End Sub

Private Sub cmdInsert_Click()
   Dim iRow As Integer, iCol As Integer
   Dim sTxt As String, sFull As String, sTime As String, sHours As String
   iRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
   For iCol = 1 To 3
      sTime = Controls("TextBox" & iCol).Text
      sHours = Left(sTime, InStr(sTime, ":") - 1)
      sTxt = sHours
      sTxt = CInt(sTxt) Mod 24
      sFull = CInt(sHours) - CInt(sTxt)
      sTxt = sTxt & Right(sTime, Len(sTime) - InStr(sTime, ":") + 1)
      Cells(iRow, iCol).Value = CDate(sTxt) + CInt(sFull) / 24
   Next iCol
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Dim sTxt As String
   With TextBox1
      sTxt = .Text
      If Not sTxt Like "##:##" And _
         Not sTxt Like "###:##" And _
         Not sTxt Like "####:##" Then
         Cancel = True
         .SetFocus
         .SelStart = 0
         .SelLength = .TextLength
      End If
   End With
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Dim sTxt As String
   With TextBox2
      sTxt = .Text
      If Not sTxt Like "##:##" And _
         Not sTxt Like "###:##" And _
         Not sTxt Like "####:##" Then
         Cancel = True
         .SetFocus
         .SelStart = 0
         .SelLength = .TextLength
      
      End If
   End With
End Sub

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Dim sTxt As String
   With TextBox3
      sTxt = .Text
      If Not sTxt Like "##:##" And _
         Not sTxt Like "###:##" And _
         Not sTxt Like "####:##" Then
         Cancel = True
         .SetFocus
         .SelStart = 0
         .SelLength = .TextLength
      End If
   End With
End Sub

StandardModule: Modul1

Sub CallForm()
   frmHours.Show
End Sub

Beiträge aus dem Excel-Forum zu den Themen Dialog und TextBox