Change cell value with button

[mucah!t]

Hello,

Like in a calendar I'd like to change the month [in a cell] by pressing the ">" or "<" button.
Any ideas?

 

[Smitty]

Why don't you use a Data Validation list?

You'll find some articles on how to do it here.

HTH,

 

[mucah!t]

Thank you for helping Smitty but I rather don't use a listbox.
I really like to use a "next" and "previous" button to make my excel-form more user friendly.

 

[Smitty]

OK, this approach requires a bit of a workaround, but it should do what you want.

In an unused range enter a list of months in a column and to the left of that add the month numbers. Now you can use a Spin Button control (Forms control, not ActiveX) and link it to an unused cell, setting its Min and Max values to 1 and 12 respectively. Then wherever you want the date to change use VLOOKUP from the Spin Button's linked cell to the list of months. As you click the SpinButton, the number will increment, causing the vlookup to return a different month.

I can send you an example if you want, just PM me your e-mail address.

 

[lenze]

You could use a Spinner!!!
lenze

 

[mucah!t]

Sounds good Smitty, I'll give it a try.
First I'd like to try it myself, thanks for offering though

 

[mucah!t]

Hello Lenze, I'll give it a try as a workaround like smitty suggested.
I thought there would be a VBA solution for this.

 

[Smitty]

I thought there would be a VBA solution for this.

You didn't ask for a VBA solution.

 

[mucah!t]

I thought mentioning "button" would imply VBA

 

[jim may]

In 2007 I went to Insert, Shapes, Block Arrows, Got the Right and the left, assigning. The Arrows DO NOT APPEAR Belwo as HTML S/W doesn't bring them over but THEY ARE THERE IN MY SAMPLE SHEET, near Cell C4 and E4.
Macros to each (See below)... Works Great!!

Jim

Excel Workbook
	A	B	C	D	E	F
1
2
3		9/1/2011
4
5
6
Sheet1

Excel 2007

In a standard module:

Sub RightArrow1_Click()
Range("B3") = WorksheetFunction.EDate(Range("B3"), 1)
End Sub

Sub LeftArrow2_Click()
Range("B3") = WorksheetFunction.EDate(Range("B3"), -1)
End Sub