Calculating Endtime/Duration

[lukayeh]

Hi I'm creating a time-sheet in Excel and want to be able to calculate the duration based on start and end time, however I also want it to be able to calculate the end time if end time cell is left blank and duration is filled in? I'm quite new to VBA so i hope you can help! Thanks

[Teeroy]

Hi lukayeh, can you post a sample workbook to show how your data will be placed on the worksheet? Otherwise you can get code designed for columns when you're working in rows etc.

[lukayeh]

Well i haven't quite figured out the layout yet but lets say:
A2 the start time formatted hh:mm AM/PM
B2 the end time formatted hh:mm AM/PM
C2 the duration hh:mm "hrs"
What i want for is if B2 is left empty and C2 is filled in B2 is calculated but if B2 is filled in, C2 will be calculated, just not sure how to go about doing it as at somepoint all three could be filled in causing confusion

[lukayeh]

I've attached my spreadsheet, what i've managed to do so far, i've added spinners to the start and end times but these might over complicate things as they require formulas in the start and end cells

[Teeroy]

The attached spreadsheet has some code that might help you. It's pretty raw though, you'd probably need error handling.

[lukayeh]

That works great, I'm looking to use a dialog box to change the times, however when submitted this doesn't update the values, for example if i use the dialog to change start and finish times it doesn't update the duration, I understand that the macro runs when the worksheet is changed but how do i do it so it updates with dialog input?
Thanks for your help so far!

[VBA]Sub Transfer_Time()

Worksheets("Sheet1").Range("a2").Value = DialogSheets("Dialog1").EditBoxes("Edit Box 4").Text
'Worksheets("Sheet1").Range("b2").Value = DialogSheets("Dialog1").EditBoxes("Edit Box 6").Text
Worksheets("Sheet1").Range("c2").Value = DialogSheets("Dialog1").EditBoxes("Edit Box 8").Text

End Sub[/VBA]

As i said i'm new to VBA so that code is very basic for the dialog box

[Teeroy]

I haven't used DialogSheets myself but I'd recommend using a UserForm over a DialogSheet. DialogSheets were replaced by UserForms quite a while ago (excel 95?) and have really been maintained for backward compatibility. In excel 2007 onwards they're hidden objects to the object browser so you won't get any IntelliSense help when trying to write code for them.