Spin Dates

[adam]

In my user form I have TextBox1 date as 18/06/2023 and TextBox2 Date as 25/06/2023

How could I make the spin button to spin up the date from TextBox1 to the date in TextBox2 only. And spin down the date from TextBox2 to TextBox1.

Following is the code I have now

Code: Select all

Private Sub SpinButton1_SpinDown()
TextBox1.Text = Format(CDate(TextBox1.Text) - _
CDate(SpinButton1.SmallChange), "dd/mm/yyyy")
End Sub

Private Sub SpinButton1_SpinUp()
TextBox1.Text = Format(CDate(TextBox1.Text) + _
CDate(SpinButton1.SmallChange), "dd/mm/yyyy")
End Sub

Any help would be kindly appreciated.

[HansV]

Once you have increased the date in TextBox1 to the date in TextBox2, there is nothing to spin down to...

[adam]

I forgot to mention TextBox3.

Having set the minimum date in TextBox1 as 18/06/2023 and maximum date in TextBox2 has 25/06/2023;

Lets say the user writes the start date in TextBox3 as 18/06/2023 and clicks the spin up button. The date in TextBox3 changes from 18/06/2023 to 19/06/2023 to the upper limit 25/06/2023.

and the same way when the user clicks the spin down button the date changes from 25/06/2023 to until 18/06/2023.

I hope I've made my question clear.

[HansV]

Try this:

Code: Select all

Private Sub SpinButton1_SpinDown()
    Dim d As Date
    d = Application.Max(CDate(TextBox3.Text) - SpinButton1.SmallChange, CDate(TextBox1.Text))
    TextBox3.Text = Format(d, "dd/mm/yyyy")
End Sub

Private Sub SpinButton1_SpinUp()
    Dim d As Date
    d = Application.Min(CDate(TextBox3.Text) + SpinButton1.SmallChange, CDate(TextBox2.Text))
    TextBox3.Text = Format(d, "dd/mm/yyyy")
End Sub

[snb]

Code: Select all

Private Sub Textbox1_change()
   spinbutton1.max=val(textbox2)-val(textbox1)
End Sub

Private Sub SpinButton1_SpinDown()
     TextBox3 = format(-spinbutton1+Textbox3,"yyyy-mm-dd")
End Sub

Private Sub SpinButton1_SpinUp()
     TextBox3 = format(spinbutton1+Textbox3,"yyyy-mm-dd")
End Sub

[adam]

Thanks for the help. It worked fine. Much appreciated.