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.

Hi Rob,

As before,

use timevalue(me..TextBox3.text)

AFAIK Excel cannot handle negative times.

Wkr,

JP
"robzrob" wrote in message
...
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.

Treat the date as text as you should be ok.

Dim DateStr as STRING

DateStr = trim(UserForm1.TextBox3.Value)
'make the cell text format.
Worksheets("Leave").Range("K5").numberformat = "@"
'put a single queote in fron ta dtate just to be sure in is a strin
Worksheets("Leave").Range("K5").numberformat = "'" & DateStr


"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.

On Aug 16, 5:11*pm, "JP Ronse" wrote:
Hi Rob,

As before,

use timevalue(me..TextBox3.text)

AFAIK Excel cannot handle negative times.

Wkr,

JP"robzrob" wrote in message

...



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 -

Thx, but it wn't work. I'm not quite sure what I'm supposed to
enter. Shoud it be CTime etc?

On Aug 16, 5:12*pm, Joel wrote:
Treat the date as text as you should be ok.

Dim DateStr as STRING

DateStr = trim(UserForm1.TextBox3.Value)
'make the cell text format.
Worksheets("Leave").Range("K5").numberformat = "@"
'put a single queote in fron ta dtate just to be sure in is a strin
Worksheets("Leave").Range("K5").numberformat = "'" & DateStr



"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 -

Major Excel crash. It's still sending the error report even now!

On Aug 16, 5:11*pm, "JP Ronse" wrote:
Hi Rob,

As before,

use timevalue(me..TextBox3.text)

AFAIK Excel cannot handle negative times.

Wkr,

JP"robzrob" wrote in message

...



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 -

I'm in 1904 mode - which can handle negative dates - and it does in
the workbook itself, ie not in VBA - when I'm subtracting 2 dates
which are already in cells to give a -ve time result in another cell.
It just won't work with the UserForm.

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, LLC
www.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.

AFAIK Excel cannot handle negative times.

With the 1904 date setting turned off, Excel cannot DISPLAY negative
times, but since a time is just a number, it can certainly "handle"
negative times in the sense that it can use them in calculations.

With the 1904 date setting turned on, Excel can display negative
times.

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


On Sun, 16 Aug 2009 18:11:26 +0200, "JP Ronse"
wrote:

Hi Rob,

As before,

use timevalue(me..TextBox3.text)

AFAIK Excel cannot handle negative times.

Wkr,

JP
"robzrob" wrote in message
...
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.

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.