[RESOLVED] MSFlexGrid & Times

[DJHotIce]

Hey Guys, I'm using MSFlexGrid as the title suggests.

This one has had be stumped for some time, and not real sure on how to approach it as MSFlexGrid has querks of its own.

I have times that need to be entered, they go in the format of

mm:ss not really using hours or any type of dates. Hours can be optional, would like to add that in so finally it would be hh:mm:ss but would like the sequence as 0:00 which is the default.

I have two "time" collumns. Then finally a totals column which adds the two together. My question is how to do this error free. I keep comming up with wierd numbers, and don't know if I'm exactly calling the thing right.

Second Q: I'm using the mod that puts a floating textbox above the flex-grid. Question is, can I make it change on just those 3 columns (I have other stringed columns) to make it do a insert type thing, so pressing the number 1 will from the right update it to 0:01 then typing in #0 will make 0:10

Thanks fellow bretherans for the help with the code. THe software I'm developing is coming along very fine! Gotta love VB.

[si_the_geek]

For your first question, show us the code you currently have as we can amend it for you to remove the problems (far easier than writing it from scratch!).

For the second question I would suggest using a different textbox for those columns, which does the formatting in the change event. Bear in mind tho that people may want to input the : characters themselves.

Here is some possible code:

'dont let the event fire when we are changing the value (infinite loop!)
Static bSelfChanging as Boolean
  If bSelfChanging Then Exit Sub
  bSelfChanging = True
 
Dim sTime as String
Dim iCursorpos as Integer
'get current value (& cursor pos)
  iCursorpos = txtTimeInput.SelStart
  sTime = Replace(txtTimeInput.Text,":","")
 
'format
  sTime = Format$(sTime,"#####:#0:00")
  If Left$(sTime,1) = ":" Then sTime = Mid$(sTime,2)
 
'set value (& return cursor pos)
  txtTimeInput.Text = sTime
  txtTimeInput.SelStart = iCursorpos
 
  bSelfChanging = False

[DJHotIce]

Honestly I removed the complete time code because of that very reason, was too buggy and was going to wait for an another possible approach.

Oh, and further looking into the code used, I am using a non-floating textbox for writing to the flexgrid. I'm using this

Private Sub Form_Load()
 
    MSFlexGrid1.HighLight = flexHighlightWithFocus
 
End Sub
 
Private Sub MSFlexGrid1_KeyPress(KeyAscii As Integer)
 
    With MSFlexGrid1
        Select Case KeyAscii
            Case 8
                If Not .Text = "" Then
                    .Text = Left(.Text, Len(.Text) - 1)
                End If
            Case 9 ' Tab
                If .Col + 1 = .Cols Then
                    .Col = 0
                    If .Row + 1 = .Rows Then
                        .Row = 0
                    Else
                        .Row = .Row + 1
                    End If
                Else
                    .Col = .Col + 1
                End If
            Case Else
                .Text = .Text & Chr(KeyAscii)
        End Select
    End With
End Sub

I don't know how I can still incorporate a control into the columns i wish to use. Si, where would I put the code at.... that is something I've been looking at for quite a while.

[si_the_geek]

That code isn't using a textbox at all That means you can use a simpler version of the code I posted, eg:

Dim sTime as String
'get current value
  sTime = Replace(.Text,":","")
 
'format
  sTime = Format$(sTime,"#####:#0:00")
  If Left$(sTime,1) = ":" Then sTime = Mid$(sTime,2)
 
'set value
  .Text = sTime

This should go just before the 'End With' line.

As for the adding of time values, I think you can do this (where r is the row number):

With MSFlexGrid1
  .TextMatrix(r, [i]totalcol[/i]) = CDate(.TextMatrix(r, [i]firstcol[/i])) + CDate(.TextMatrix(r, [i]secondcol[/i]))
End With

[DJHotIce]

thanks si_the_geek I think that code will work! what I ment in the earlier post 'non-floating' textbox... I'm not using a textbox.... sorry for the confusion.

THe code does look very straight foward though.

[DJHotIce]

Si, the code works, to an extent...

I am not real familiar with date/times and of the such with VB just yet so bear with me

1) Time represented is not right.. 0:25 + 0:00 yields 2:25:00 AM
2) No AM/PM and no hour representation unless needed. it should be :25 which is pronounced 25 Seconds.

But the code does seem to add properly... In the meantime I will be searching my VB books for info.

[si_the_geek]

You probably just need to format the values (before and after the CDate's) appropriately, eg:

Dim sTime as String
With MSFlexGrid1
  sTime = Format$(CDate(Format$(.TextMatrix(r, firstcol),"####0:00:00")) + CDate(Format$(.TextMatrix(r, secondcol),"####0:00:00"))"#####:##:00")
  If Left$(sTime,2) = "::" Then sTime = Mid$(sTime,2)
  .TextMatrix(r, totalcol) = sTime
End With

[DJHotIce]

Si, here's what I did for the revised code you did above. I had this earlier, but then yours had an error, so I decided to revert to the code that was working... Only diff is I'm treating the Hours like min and mins like seconds... Hopefully there is an easier way... Marking thread resolved.

With MSFlexGrid1
        With MSFlexGrid1
          .TextMatrix(.Row, 10) = Format(CDate(.TextMatrix(.Row, 8)) + CDate(.TextMatrix(.Row, 9)), "h:mm")
        End With
    End With

[si_the_geek]

You should also format the values inside the CDate (as I did), to ensure that the values are converted to times accurately.

but then yours had an error

[DJHotIce]

I recieved a type mismatch error, if memory serves correct.

[si_the_geek]

I've just checked my code and can't see anything obvious, so I would assume that the values could not be converted into times.

Therefore I would recommend using a bit of validation, possibly like this:

Dim sTime as String
Dim sTempTime as String
With MSFlexGrid1
  sTime = "0:00:00"
  sTempTime = Format$(.TextMatrix(r, firstcol),"####0:00:00")
  If IsDate(sTempTime) Then    sTime = sTime + CDate(sTempTime)
  sTempTime = Format$(.TextMatrix(r, secondcol),"####0:00:00")
  If IsDate(sTempTime) Then    sTime = sTime + CDate(sTempTime)
  If Left$(sTime,2) = "::" Then sTime = Mid$(sTime,2)
  .TextMatrix(r, totalcol) = sTime
End With

If you still get an error, you should let me know which line it occurs on, and what the text values are.