Excel VBA: How do I make spin button control multiple text boxes?

Question

I have a form with a date composed of 3 text boxes and a spinner. I used 3 text boxes to solve a problem with international dates, so there is no way the intended date can be misinterpreted by Excel based on different formats.

Anyway... When I click the spinner, the next or previous date appears in the 3 boxes. I.e. if it is Feb 28, 2013 it will go to Mar 01, 2013. It moves one day at a time.

My boss wants it to move based on which text box the cursor is in. So if it is in txtMonth, the date should go from Feb 28, 2013 to Mar 28, 2013, etc.

How can I make the spinner detect which textbox contains the cursor (if any?) Once I know that, I can do the math.

Private Sub spinDate_SpinDown()
    Dim bGoodDate As Boolean
    Dim iMonth As Integer

    Dim dDate As Date
    If Not bIsValidDate(bGoodDate) Then   ' Check that the 3 boxes make a valid date
        GoTo ErrorExit
    End If
    iMonth = Month(DateValue("01-" & Me.txtMon & "-2014"))
    dDate = DateValue(Me.txtDay & "-" & Me.txtMon & "-" & Me.txtYear)

    dDate = DateAdd("d", -1, dDate)  ' Decrement date


    Me.txtDay = Format(Day(dDate), "00")
    Me.txtMon = Format(dDate, "mmm")
    Me.txtYear = Format(Year(dDate), "0000")

    m_clsCRE.ETA = dDate
    m_clsCRE.bChanged = True
ErrorExit:
End Sub

* UPDATED WITH SOLUTION BELOW ** ' This solution detects whether the cursor was last in the day, month or year box. ' Spin button increments by the chosen amount (day, month or year) ' The boxes are linked so that spinning takes you to the next valid date based on your choice

Private Sub spinDate_SpinDown()  ' Same for SpinUp but used +1
    Dim bGoodDate As Boolean

    Dim dDate As Date
    If Not bIsValidDate(bGoodDate) Then    ' check that date user typed in boxes is valid
        GoTo ErrorExit
    End If

    dDate = DateValue(Me.txtDay & "-" & Me.txtMon & "-" & Me.txtYear)
    If Len(msDatePart) = 0 Or StrComp(msDatePart, "DAY", vbTextCompare) = 0 Then
        dDate = DateAdd("d", -1, dDate)
    ElseIf StrComp(msDatePart, "MONTH", vbTextCompare) = 0 Then
        dDate = DateAdd("m", -1, dDate)
    Else ' Year
        dDate = DateAdd("yyyy", -1, dDate)
    End If


    Me.txtDay = Format(Day(dDate), "00")
    Me.txtMon = Format(dDate, "mmm")
    Me.txtYear = Format(Year(dDate), "0000")

    m_clsCRE.ETA = dDate   ' Save real date value (not text form)
    m_clsCRE.bChanged = True
ErrorExit:
End Sub

Private Sub txtDay_Enter()
    ' Used with calendar spinner
    msDatePart = "DAY"
End Sub
Private Sub txtMon_Enter()
    ' Used with calendar spinner
    msDatePart = "MONTH"
End Sub
Private Sub txtYear_Enter()
    ' Used with calendar spinner
    msDatePart = "YEAR"
End Sub

BTW, have you seen [THIS](http://stackoverflow.com/questions/12012206/formatting-mm-dd-yyyy-dates-in-textbox-in-vba/12013961#12013961) — Siddharth Rout, Dec 16 '13 at 13:27
Siddharth, I downloaded your calendar picker and played with it. Made some changes which I described on the other post. It's very useful! I might end up using it in this project or the next. — Shari W, Dec 16 '13 at 15:58

sam092 · Answer 1 · 2013-12-16T09:45:05.737

I'm not sure how to detect the current object which is on focus.

As a preliminary workaround, you may see my code below. Instead of detecting the object on focus, we detect when a textbox is "entered" by the enter event. Then we use a global variable to store the name of that textbox

Option Explicit
'Declare a global variable here
Dim onFocus As String

Private Sub SpinButton1_SpinDown()
    Dim obj As Object
    Set obj = Controls(onFocus)
    obj.Value = obj.Value + 1
End Sub

Private Sub TextBox1_Enter()
    onFocus = "TextBox1"
End Sub

Private Sub TextBox2_Enter()
    onFocus = "TextBox2"
End Sub

Private Sub TextBox3_Enter()
    onFocus = "TextBox3"
End Sub

Private Sub UserForm_Initialize()
    Controls("Textbox1").SetFocus
End Sub

score 1 · Accepted Answer · answered Dec 16 '13 at 09:39

Try this:

This code will detect the last TextBox with focus prior pressing the SpinButton.
Take note that Textbox or any other object will lose focus once you set focus on another object.

Option Explicit
Public LastActiveObject As String 'Declare a public variable to store the last active object name

Private Sub SpinButton1_SpinDown() 'Here you test which Textbox was last active

If LastActiveObject = "TextBox1" Then MsgBox "TB1"
If LastActiveObject = "TextBox2" Then MsgBox "TB2"
If LastActiveObject = "TextBox3" Then MsgBox "TB3"

End Sub

Add Exit event on all TextBoxes to store it's name in the LastActiveObject Variable

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

LastActiveObject = TextBox1.Name

End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

LastActiveObject = TextBox2.Name

End Sub

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

LastActiveObject = TextBox3.Name

End Sub

Hope this helps you a bit to accomplish what you want.

Thanks to L42 and @sam092 for great answers! – Shari W Dec 16 '13 at 15:57 — Shari W, Dec 16 '13 at 15:57

Excel VBA: How do I make spin button control multiple text boxes?

2 Answers2