|
Spin Button to increment or decrement the date in cell A1
How can I used the Spin Button to increment or decrement the date in cell A1
format(dd/mm/yyyy) I went thru the help but I could not find the location to change the spinup and spindown properties. Thanks Pal |
Spin Button to increment or decrement the date in cell A1
http://support.microsoft.com/default...b;en-us;161517
XL97: Visual Basic Example for Using SpinButton with a Date http://support.microsoft.com/default...b;en-us;213224 XL2000: Visual Basic Example for Using a Spin Button with a Date http://support.microsoft.com/default...b;en-us;161816 XL97: Min Can Exceed Max on ScrollBar or SpinButton Control -- Regards, Tom Ogilvy Pal wrote in message news:jQaXa.40079$cF.13520@rwcrnsc53... How can I used the Spin Button to increment or decrement the date in cell A1 format(dd/mm/yyyy) I went thru the help but I could not find the location to change the spinup and spindown properties. Thanks Pal |
Spin Button to increment or decrement the date in cell A1
wks is undefined from what you have shown
Private Sub SpinButton1_SpinUp() me.Range("g4") = me.Range("g4") + 1 End Sub Private Sub SpinButton1_SpinDown() me.Range("g4") = me.Range("g4") - 1 End Sub Worked fine for me given that G4 is on the same sheet as the spinbutton. -- Regards, Tom Ogilvy Pal wrote in message news:i0iXa.57686$Ho3.8134@sccrnsc03... Thanks Ken and Tom I looked at Toms links before and it was to use only on a userform. Here is what is happening. If I open a blank sheet and try the spinner control object it works perfectly. But if I try to incorporate it into my existing spreadsheet I get an run-time error 424: "object required" Below is the debug code it referenced: Private Sub SpinButton1_SpinUp() wks.Range("g4") = wks.Range("g4") + 1 End Sub Private Sub SpinButton1_SpinDown() wks.Range("g4") = wks.Range("g4") - 1 End Sub I am not sure what this error means. Also It always resets to 01/01/1900 where I want it to change from the current date in that cell. Thanks again Pal "Ken Wright" wrote in message ... Depends which spin button you are using. If you are using the one off the Forms Toolbar you will need to use a helper cell, as the max value is 30000, so you would not be able to link directly to a cell and then use that as a date if the date was later than 1982. You can use a nominal date though as your earliest date and then add the helper cell to it, with the spinner being linked to the helper cell. This would give you a potential range of your earliest date plus 82 years. If you are using the spinner from the control toolbox you can set that to be pretty much any value you want, so you can right click on the control, select properties, change the max value to say 50,000, link it directly to a cell by putting the reference in the LinkedCell field, and then set the date you want to start with as a number (eg today is 37,836) in the Value field. Make sure you exit design mode by clicking on the appropriate icon from the control toolbox when done, else you won't be able to use it. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP -------------------------------------------------------------------------- -- Attitude - A little thing that makes a BIG difference -------------------------------------------------------------------------- -- "Pal" wrote in message news:jQaXa.40079$cF.13520@rwcrnsc53... How can I used the Spin Button to increment or decrement the date in cell A1 format(dd/mm/yyyy) I went thru the help but I could not find the location to change the spinup and spindown properties. Thanks Pal |
Spin Button to increment or decrement the date in cell A1
Thank Tom
That worked But every time I change it manually, then hit the spinner it resets to 01/01/1900 Pal "Tom Ogilvy" wrote in message ... wks is undefined from what you have shown Private Sub SpinButton1_SpinUp() me.Range("g4") = me.Range("g4") + 1 End Sub Private Sub SpinButton1_SpinDown() me.Range("g4") = me.Range("g4") - 1 End Sub Worked fine for me given that G4 is on the same sheet as the spinbutton. -- Regards, Tom Ogilvy Pal wrote in message news:i0iXa.57686$Ho3.8134@sccrnsc03... Thanks Ken and Tom I looked at Toms links before and it was to use only on a userform. Here is what is happening. If I open a blank sheet and try the spinner control object it works perfectly. But if I try to incorporate it into my existing spreadsheet I get an run-time error 424: "object required" Below is the debug code it referenced: Private Sub SpinButton1_SpinUp() wks.Range("g4") = wks.Range("g4") + 1 End Sub Private Sub SpinButton1_SpinDown() wks.Range("g4") = wks.Range("g4") - 1 End Sub I am not sure what this error means. Also It always resets to 01/01/1900 where I want it to change from the current date in that cell. Thanks again Pal "Ken Wright" wrote in message ... Depends which spin button you are using. If you are using the one off the Forms Toolbar you will need to use a helper cell, as the max value is 30000, so you would not be able to link directly to a cell and then use that as a date if the date was later than 1982. You can use a nominal date though as your earliest date and then add the helper cell to it, with the spinner being linked to the helper cell. This would give you a potential range of your earliest date plus 82 years. If you are using the spinner from the control toolbox you can set that to be pretty much any value you want, so you can right click on the control, select properties, change the max value to say 50,000, link it directly to a cell by putting the reference in the LinkedCell field, and then set the date you want to start with as a number (eg today is 37,836) in the Value field. Make sure you exit design mode by clicking on the appropriate icon from the control toolbox when done, else you won't be able to use it. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP -------------------------------------------------------------------------- -- Attitude - A little thing that makes a BIG difference -------------------------------------------------------------------------- -- "Pal" wrote in message news:jQaXa.40079$cF.13520@rwcrnsc53... How can I used the Spin Button to increment or decrement the date in cell A1 format(dd/mm/yyyy) I went thru the help but I could not find the location to change the spinup and spindown properties. Thanks Pal |
Spin Button to increment or decrement the date in cell A1
Dates are stored as the number of days since 1/1/1900. The value you show
would indicate a zero is being stored in the cell (or a decimal number less than 1 - but more than likey, a zero). Format the cell as general to see the serial number stored in the cell. How it gets there, I can't say. I had no trouble manually editing G4 using the code I posted. It works fine for me. You don't have the spinbutton linked to G4 do you. You should only have code affect the value of G4. Regards, Tom Ogilvy Pal wrote in message et... Thank Tom That worked But every time I change it manually, then hit the spinner it resets to 01/01/1900 Pal "Tom Ogilvy" wrote in message ... wks is undefined from what you have shown Private Sub SpinButton1_SpinUp() me.Range("g4") = me.Range("g4") + 1 End Sub Private Sub SpinButton1_SpinDown() me.Range("g4") = me.Range("g4") - 1 End Sub Worked fine for me given that G4 is on the same sheet as the spinbutton. -- Regards, Tom Ogilvy Pal wrote in message news:i0iXa.57686$Ho3.8134@sccrnsc03... Thanks Ken and Tom I looked at Toms links before and it was to use only on a userform. Here is what is happening. If I open a blank sheet and try the spinner control object it works perfectly. But if I try to incorporate it into my existing spreadsheet I get an run-time error 424: "object required" Below is the debug code it referenced: Private Sub SpinButton1_SpinUp() wks.Range("g4") = wks.Range("g4") + 1 End Sub Private Sub SpinButton1_SpinDown() wks.Range("g4") = wks.Range("g4") - 1 End Sub I am not sure what this error means. Also It always resets to 01/01/1900 where I want it to change from the current date in that cell. Thanks again Pal "Ken Wright" wrote in message ... Depends which spin button you are using. If you are using the one off the Forms Toolbar you will need to use a helper cell, as the max value is 30000, so you would not be able to link directly to a cell and then use that as a date if the date was later than 1982. You can use a nominal date though as your earliest date and then add the helper cell to it, with the spinner being linked to the helper cell. This would give you a potential range of your earliest date plus 82 years. If you are using the spinner from the control toolbox you can set that to be pretty much any value you want, so you can right click on the control, select properties, change the max value to say 50,000, link it directly to a cell by putting the reference in the LinkedCell field, and then set the date you want to start with as a number (eg today is 37,836) in the Value field. Make sure you exit design mode by clicking on the appropriate icon from the control toolbox when done, else you won't be able to use it. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP -------------------------------------------------------------------------- -- Attitude - A little thing that makes a BIG difference -------------------------------------------------------------------------- -- "Pal" wrote in message news:jQaXa.40079$cF.13520@rwcrnsc53... How can I used the Spin Button to increment or decrement the date in cell A1 format(dd/mm/yyyy) I went thru the help but I could not find the location to change the spinup and spindown properties. Thanks Pal |
Spin Button to increment or decrement the date in cell A1
Removing that linked cell property fixed the problem
Thanks again "Tom Ogilvy" wrote in message ... Dates are stored as the number of days since 1/1/1900. The value you show would indicate a zero is being stored in the cell (or a decimal number less than 1 - but more than likey, a zero). Format the cell as general to see the serial number stored in the cell. How it gets there, I can't say. I had no trouble manually editing G4 using the code I posted. It works fine for me. You don't have the spinbutton linked to G4 do you. You should only have code affect the value of G4. Regards, Tom Ogilvy Pal wrote in message et... Thank Tom That worked But every time I change it manually, then hit the spinner it resets to 01/01/1900 Pal "Tom Ogilvy" wrote in message ... wks is undefined from what you have shown Private Sub SpinButton1_SpinUp() me.Range("g4") = me.Range("g4") + 1 End Sub Private Sub SpinButton1_SpinDown() me.Range("g4") = me.Range("g4") - 1 End Sub Worked fine for me given that G4 is on the same sheet as the spinbutton. -- Regards, Tom Ogilvy Pal wrote in message news:i0iXa.57686$Ho3.8134@sccrnsc03... Thanks Ken and Tom I looked at Toms links before and it was to use only on a userform. Here is what is happening. If I open a blank sheet and try the spinner control object it works perfectly. But if I try to incorporate it into my existing spreadsheet I get an run-time error 424: "object required" Below is the debug code it referenced: Private Sub SpinButton1_SpinUp() wks.Range("g4") = wks.Range("g4") + 1 End Sub Private Sub SpinButton1_SpinDown() wks.Range("g4") = wks.Range("g4") - 1 End Sub I am not sure what this error means. Also It always resets to 01/01/1900 where I want it to change from the current date in that cell. Thanks again Pal "Ken Wright" wrote in message ... Depends which spin button you are using. If you are using the one off the Forms Toolbar you will need to use a helper cell, as the max value is 30000, so you would not be able to link directly to a cell and then use that as a date if the date was later than 1982. You can use a nominal date though as your earliest date and then add the helper cell to it, with the spinner being linked to the helper cell. This would give you a potential range of your earliest date plus 82 years. If you are using the spinner from the control toolbox you can set that to be pretty much any value you want, so you can right click on the control, select properties, change the max value to say 50,000, link it directly to a cell by putting the reference in the LinkedCell field, and then set the date you want to start with as a number (eg today is 37,836) in the Value field. Make sure you exit design mode by clicking on the appropriate icon from the control toolbox when done, else you won't be able to use it. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP -------------------------------------------------------------------------- -- Attitude - A little thing that makes a BIG difference -------------------------------------------------------------------------- -- "Pal" wrote in message news:jQaXa.40079$cF.13520@rwcrnsc53... How can I used the Spin Button to increment or decrement the date in cell A1 format(dd/mm/yyyy) I went thru the help but I could not find the location to change the spinup and spindown properties. Thanks Pal |
| All times are GMT +1. The time now is 05:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com