More Dates

  • Thread starter Thread starter robzrob
  • Start date Start date
R

robzrob

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
 
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
 
Hi Rob,

As before,

use timevalue(me..TextBox3.text)

AFAIK Excel cannot handle negative times.

Wkr,








- Show quoted text -
Click to expand...

Thx, but it wn't work. I'm not quite sure what I'm supposed to
enter. Shoud it be CTime etc?
 
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







- Show quoted text -
Click to expand...

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

As before,

use timevalue(me..TextBox3.text)

AFAIK Excel cannot handle negative times.

Wkr,








- Show quoted text -
Click to expand...

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)
 
AFAIK Excel cannot handle negative times.
Click to expand...

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





- Show quoted text -
Click to expand...

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