On Aug 16, 9:25*pm, Chip Pearson wrote:
Try the following. It assumes that you have the 1904 date setting
enabled so Excel will display negative times.

Private Sub CommandButton1_Click()
* * Dim TS As String
* * Dim T As Double
* * Dim N As Integer

* * ' get the string from the text box
* * ' and get rid of spaces.
* * TS = Replace(Me.TextBox1.Text, Space$(1), vbNullString)
* * If Left(TS, 1) = "-" Then
* * * * ' begins with a negative sign. get time starting
* * * * ' at position 2.
* * * * N = -1
* * * * TS = Mid(Me.TextBox1.Text, 2)
* * Else
* * * * ' no negative sign. get time starting at position 1
* * * * N = 1
* * * * TS = Me.TextBox1.Text
* * End If
* * ' convert the time string to an actual time
* * ' and then make it negative is required.
* * On Error Resume Next
* * Err.Clear
* * T = TimeValue(TS) * N
* * If Err.Number < 0 Then
* * * * MsgBox "Invalid time string"
* * * * Exit Sub
* * End If
* * With Range("A1")
* * * * .NumberFormat = "hh:mm:ss"
* * * * .Value = T
* * End With
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
* * Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)

On Sun, 16 Aug 2009 08:07:46 -0700 (PDT), robzrob



wrote:
In a UserForm I've got a TextBox asking for an amount of time (hours
and minutes). *It could be negative. *If the user wants to enter a
negative date, I want them to be able to just enter, for example:
-3:04, -10:53, etc. *I'm in 1904 mode. *My code for this value is:

Worksheets("Leave").Range("K5").Value = UserForm1.TextBox3.Value

I've tried inputting times like this: -3:04 and this: -"3;04", but
neither will do.- Hide quoted text -

- Show quoted text -

Thanks Chip - works great. What a palaver to get a -ve time into a
cell!! I hope 2010 version fixes this.