userform textbox

[Phil]

How can i set a userform textbox properties so i can enter HH:MM.?

Thanks

 

[Bob Phillips]

Phil,

Here is one way, a bit kludgy but it works in full HH:MM mode

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim fColon As Boolean
Dim iPos As Long

iPos = InStr(1, TextBox1.Value, ":")
fColon = iPos > 0
Select Case KeyAscii
Case 48 To 57: ' 0-9
Case 58: 'colon
If fColon Then
KeyAscii = 0 'colon already entered
End If
Case Else:
KeyAscii = 0
Exit Sub
End Select

If Len(TextBox1.Text) = 0 Or Len(TextBox1.Text) = 3 Then
If KeyAscii > Asc("5") Then
KeyAscii = 0
Exit Sub
End If
ElseIf Len(TextBox1.Text) = 1 Then
'nothing to do
ElseIf Len(TextBox1.Text) = 2 Then
If KeyAscii > Asc(":") Then
KeyAscii = 0
Exit Sub
End If
End If

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Phil]

Excellent, thanks Bob.
Can i trouble you for another ?
It concerns an If statement.
In C8 i have
=IF(C2<0.208333333333333,0.208333333333333-C2,0)
which works fine but i also want to have to C7 decrease by 1 if the
statement is true and remain the same if it is false!
hope you understand me.

Thanks again

 

[Bob Phillips]

Phil,

You can't do this withn a formula as you want to change the cell that the
formula would be in, so you need event code.

I have put this together, and it will reduce C7 every time C2 is changed. It
will only reduce C7 if it is greater than zero. Is this what you want?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "$C$2" Then
If .Value < 0.208333333333333 Then
If Range("C7").Value > 0 Then
Range("C7").Value = Range("C7").Value - 1
End If
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Phil]

Tried it Bob with no luck. This is what i get...

C2 04:00
C3 18:00
C4 13:30

C5 8:00
C6 5:30
C7 1:00

Its a timesheet scenario. C4 is total hrs(less break), C5 is basic, c6 is
time x1.5 and c7 is time x2. If x2 hrs(C7) increase , x1.5hrs(c6) decrease
by same amount.
In the above the total hrs(C4) is 13:30, but basic(C5) + x1.5(C6) + x2 (C7)
= 14:30.
A pain i know, thats why i ask!

 

[Phil]

Have sorted it by shifting cells and using helper cells. thanks anyway
Bob(and all who looked)