none
Spin Buttons used to set start and stop time in cells can't zero results? RRS feed

  • Question

  • The following is a code block intended to set a start time in   cell  "G41" (one spinner for hour, the other for minutes) and a similar code block for stop time. This seems to work, but my problem is when I have them both set to the same time I can't seem to calulate them in such a way to read zero hours. I get either 23:59 or 00:01.  My excel formula was lifted off the net, then I added the .0005 to try and zero the equation. =MOD(G41-D41,1)+0.0005 I'm missing something here, any help would be greatly appreciated.

    Code block for spinner

    Private Sub SpinButton3_SpinUp()
    'Hour
    'Stop Hour time scroller up
     Dim MyDate As Date
     MyDate = TimeValue("01:00:00")
     Range("G41").Value = Range("G41").Value + MyDate
    End Sub
    Private Sub SpinButton3_SpinDown()
    'Hour
    'Stop time scroller down
     Dim MyDate As Date
     MyDate = TimeValue("01:00:00")
     Range("G41").Value = Range("G41").Value - MyDate
    End Sub

    Private Sub SpinButton4_SpinUp()
    'Minute
    'Start Min time scroller up
     Dim MyDate As Date
     MyDate = TimeValue("00:10:00")
     Range("G41").Value = Range("G41").Value + MyDate
    End Sub
    Private Sub SpinButton4_SpinDown()
    'Minute
    'Start min hour time scroller down
     Dim MyDate As Date
     MyDate = TimeValue("00:01:00")
     Range("G41").Value = Range("G41").Value - MyDate
    End Sub


    heads up

    Saturday, January 4, 2014 2:16 AM

All replies

  • Don't understand. Need some more explanation. I don't understand the relevance of the following lines. Why is one 10 mins and the other 1 min. If trying to set the increments/decrements then do so in SmallChange property of the Spin Button.

    MyDate = TimeValue("00:10:00")   'In Private Sub SpinButton4_SpinUp()

    MyDate = TimeValue("00:01:00")   'In Private Sub SpinButton4_SpinDown() - Different to Spin Up

     Range("G41").Value = Range("G41").Value - MyDate  'What is relevance

    If simply trying to create a time picker then the following code does that using the Change event with the 2 spin buttons. It changes 24 to 0 and also increments the hours when minutes get to 60.


    Private Sub SpinButton3_Change()
        If SpinButton3 = 24 Then SpinButton3 = 0
        Range("G41") = TimeValue(Format(SpinButton3.Value, "00") & ":" & Format(SpinButton4.Value, "00") & ":00")
    End Sub


    Private Sub SpinButton4_Change()
        If SpinButton4.Value = 60 Then
            SpinButton4.Value = 0
            SpinButton3.Value = SpinButton3.Value + 1
        End If
        Range("G41") = TimeValue(Format(SpinButton3.Value, "00") & ":" & Format(SpinButton4.Value, "00") & ":00")
    End Sub


    Regards, OssieMac

    Saturday, January 4, 2014 6:48 AM
  • OssieMac,

    You amaze me!

    The datepicker in excel 2010 corrupts my file in really strange ways. Could you show me the code to use a horizonal scroll bar or spinner to change the date in the cell. Date is starting with =today() and I just want to increment/decrement the days even if they transition a month? Thank you . Not getting date format to behave on this end.


    Saturday, January 18, 2014 10:20 PM
  • The datepicker in excel 2010 corrupts my file in really strange ways. Could you show me the code to use a horizonal scroll bar or spinner to change the date in the cell. Date is starting with =today() and I just want to increment/decrement the days even if they transition a month? Thank you . Not getting date format to behave on this end.


    Yes! I have the same problem with the Date picker on a worksheet but in a Userform it works OK.

    I have created code for a simple Spin Button.

    Create an ActiveX  Spin Button on your worksheet. (Must be an ActiveX control because the code will not work with a Forms control.)

    The following code goes in ThisWorkbook module. (Open the VBA editor and double click ThisWorkbook in the Project explorer in the left column to open ThisWorkbook module.

    Copy and paste the following code into the editor and note the comments where you may need to edit the code to match your worksheet and the cell to display the date plus the number of days you want after today in the Spin Button.

    Private Sub Workbook_Open()
       
        Dim ws As Worksheet
        Dim rng As Range
        Dim oleObj As OLEObject
       
        Set ws = Worksheets("Sheet1")   'Edit "Sheet1" to your workhseet name
        Set rng = ws.Range("A15")       'Edit "A15" to cell to display the date
        Set oleObj = ws.OLEObjects("SpinButton1")   'Edit "SpinButton1" to your SpinButton name
       
        With oleObj.Object
            .Min = CLng(Date) 'Minimum value is serial number equal to today
           
            'Maximum value is Minimum value plus number of days in year.
            .Max = .Min + 365   'Edit 365 to number of days you require in the Spin Button
        End With
       
        'Assign required date format to the date display cell
        rng.NumberFormat = "ddd dd mmm yyyy"    'Edit format to desired format
    End Sub

    The following code goes into the worksheet module. (Right click the worksheet tab name and select View code to open the worksheets module.)

    Copy and paste the following code into the editor. If your Spin Button name is not SpinButton1 then edit SpinButton1 in the sub name to suit your spin button.

    Private Sub SpinButton1_Change()
        Me.Range("A15") = SpinButton1.Value
    End Sub

    Save the workbook as macro enabled and close and re-open and the Workbook Open code will run and set up the Spin Button with the desired date range from today for 1 year. Each time you open the workbook then the range of dates for the Spin Button will start from the current date.


    Regards, OssieMac

    Sunday, January 19, 2014 12:12 PM
  • I have had a look at using a Scroll Bar. However, set the Small change property to one but the Large Change property can be a greater number (of your choice) to move dates a little quicker.

    For the Scroll Bar the Workbook Open event code needs to reset the value otherwise  the Scroll remains in the last used position.

    In the Workbook Open event you can set the minimum value for both the Spin Button and Scroll Bar to the last used Date in the cell by changing the line to set the minimum to the following.

            .Min = CLng(rng.Value) 'Minimum value is serial number equal to last used date

    Private Sub Workbook_Open()
        'Following is for Scroll Bar
       
        Set ws = Worksheets("Sheet1")   'Edit "Sheet1" to your workhseet name
        Set rng = ws.Range("A20")       'Edit "A15" to cell to display the date
        Set oleObj = ws.OLEObjects("ScrollBar1")   'Edit "ScrollBar1" to your SpinButton name
       
        With oleObj.Object
            .Min = CLng(Date) 'Minimum value is serial number equal to today
            
            'Maximum value is Minimum value plus number of days in year.
            .Max = .Min + 365   'Edit 365 to number of days you require in the Spin Button
           
            .Value = .Min   'Reset the value otherwise Scroll remains at last used position.
        End With
       
        'Assign required date format to the date display cell
        rng.NumberFormat = "ddd dd mmm yyyy"    'Edit format to desired format
       
    End Sub


    Regards, OssieMac

    Sunday, January 19, 2014 8:49 PM
  • Thank you kind Sir! I'm just amazed!

    I have been asked to dumb this form down to 2003 for a few users. The spin button appears to dislike the "  (Range("G41") = TimeValue(Format(SpinButton3.Value, "00") & ":" & Format(SpinButton4.Value, "00") & ":00")" format? Any help with this? I know its old school.. Thank you for all the great support, you are a resource! 


    heads up

    Monday, January 20, 2014 6:58 AM
  • I have been asked to dumb this form down to 2003 for a few users. The spin button appears to dislike the "  (Range("G41") = TimeValue(Format(SpinButton3.Value, "00") & ":" & Format(SpinButton4.Value, "00") & ":00")" format? Any help with this?

    Do I understand correctly that the code works in a version of Office 2007 or later but does not work in version 2003? If so, what did you do to transfer to Excel 2003?

    Did you simply save as an xls file from within the later version of Office and then transfer the xls file to the Excel 2003 computer?

    I don't have a copy of an earlier version of Office so cannot test but if you have it working in a later version of Excel and saved as xls file then I would have thought that it will work with the earlier version of Excel.


    Regards, OssieMac

    Monday, January 20, 2014 10:59 AM
  • yes i saved as a 2003-.xls. the spinners worked fine in 2007. i ran into several little problems and was able to get thru them IN 2003. im guessing that it has something to do with "Format" but i dont know....  If I have stumpted the wizard i must be in deep dodo. THANK YOU just the same!

    heads up

    Monday, January 20, 2014 2:05 PM
  • The following worked without the format. Thank you.

    Private Sub SpinButton7_Change()

       If SpinButton7 = 24 Then SpinButton7 = 0
         Range("D5") = TimeValue((SpinButton7.Value) & ":" & (SpinButton8.Value) & ":00")
       End Sub

    Private Sub SpinButton8_Change()
        If SpinButton8.Value = 60 Then
            SpinButton8.Value = 0
            SpinButton7.Value = SpinButton7.Value + 1
        End If
        Range("D5") = TimeValue((SpinButton7.Value) & ":" & (SpinButton8.Value) & ":00")
    End Sub


    heads up

    Monday, January 20, 2014 7:05 PM
  • yes i saved as a 2003-.xls. the spinners worked fine in 2007. i ran into several little problems and was able to get thru them IN 2003. im guessing that it has something to do with "Format" but i dont know....  If I have stumpted the wizard i must be in deep dodo. THANK YOU just the same!

    heads up

    I wasn't stumped. Before spending time on the problem, I simply wanted to know if you used the same code as you had used in the later version of Excel because I understood you had it working in the later version and I didn't really believe that it would not work with an earlier version of Excel but because I couldn't test, I wanted to be sure.

    The problem example line of code that you posted had a syntax error with a leading bracket before Range so I am doubtful that the Format was really the problem. I am assuming that the extra double quotes before and at the end of the line were added in the post because if either was in the line of actual code then also syntax error.

    You are correct in that the Format is not required even for the later versions of Excel. I had left them in because I had been experimenting with the best way to code and they were left over from earlier testing. I should have realized that it was not required.

    Anyway I guess that you feel good about having resolved the problem yourself and I wish you all the best with your future programming.

     


    Regards, OssieMac

    Monday, January 20, 2014 10:39 PM
  • Too funny!

    Anytime I can get the code to work in my favor as a nube, I'm thrilled. Now is it the fastest code or most reliable... thats what I have no confidence in. Your expertise is always apprecaited and sincere thanks for browsing the boards!


    heads up

    Thursday, January 23, 2014 3:08 PM