Problem transferring data from Userform to Worksheet

[rforob]

Hi all,

I have designed a userform where time is entered into several text boxes in the [hh]:mm format. This data is then transferred to a worksheet where I was hoping to utilise the Sum function to add up the total time.

I am formatting the target worksheet cell in to [hh]:mm (in vba) as well as the userform variable prior to transfer. The data appears to transfer successfully and appears in the cell. However when I try and sum a number of these cells the sum always appears as zero. When I select the cell in excel the sum value at the bottom of the excel screen also displays zero.

Hoping somebody has has an idea why this is happening ... I suspect a formatting issue ...

many thanks in advance,

rforob

 

[RoryA]

How is the data transferred to the worksheet?

 

[rforob]

How is the data transferred to the worksheet?

Hi Rory

The time is gathered in the form and then transferred from the sheet on clicking an enter button. Vba from the form reads similar to:

Sheets(sheet1).Range("A2").NumberFormat = "[hh]:mm"
FlightDuration = Format(FlightDuration,"[hh]:mm")
Sheets(sheet1).Range("A2").value = FlightDuration

Following execution the time appears in the cell but as described the sum function shows zero.

cheers,

rforob

 

[rforob]

Hi Rory

The time is gathered in the form and then transferred from the sheet on clicking an enter button. Vba from the form reads similar to:

Sheets(sheet1).Range("A2").NumberFormat = "[hh]:mm"
FlightDuration = Format(FlightDuration,"[hh]:mm")
Sheets(sheet1).Range("A2").value = FlightDuration

Following execution the time appears in the cell but as described the sum function shows zero.

cheers,

rforob

sorry meant Sent to the sheet

 

[RoryA]

Is FlightDuration the name of the textbox, or a variable? What is actually entered into it? If it actually contains values like 48:15 then you'll need something like:

Sheets("Sheet1").Range("A2").NumberFormat = "[hh]:mm"
Sheets("Sheet1").Range("A2").Value = TimeSerial(Split(flightduration, ":")(0), Split(flightduration, ":")(1), 0)

 

[rforob]

Rory,

Many thanks this worked a treat!

rforob

 

[RoryA]

Glad to help. Welcome to the forum, by the way!