Excel/Spinbutton to fire a Command_Click() button.
QUESTION: Dear Mr. Heritage, I'm back after 2 years to ask once more for your valuable expertise.
I have a userform in which I've built my own calendar/datepicker, complete with one spinbutton and 31 command buttons each representing a day of the month.
To each button I've given the name day1 for 1, day2 for 2 and so on up to day31 for 31.
Each command button has it's own Private Sub Routine.
What i need is to setup the spinup and spindown to fire the day*_Click routine.
Private Sub spnCal_SpinUp()
will fire the
Private Sub day2_Click()
txtDay.Text = day2.Caption
MonN = Me.spnCalm.Value
Me.TextBox2.Text = DateSerial(txtYear.Text, MonN, txtDay.Text)
But if i try to build up the day*_Click command using
Private Sub spnCal_SpinUP()
Dim d As Integer
lValue As String
d = Me.spnCal.Value 'the spnCal.Value is assigned by UserForm_Initialize() using Me.spnCal.Value = Format(Now, "dd")
lValue = "day" & d & "_Click"
MsgBox "Value is " & lValue
MsgBox works fine, it does give me the correct string. If the spinbutton value is 22, the string day22_Click is shown in the message box.
But the Application.Run lValue give's me an error, ( "Application.Run lValue" is highlighted in debug) and so does lValue alone.
This routine will then be used with the +1 and -1 to scale up or down the days each time the spinup or spindown is clicked, updating the Me.TextBox2.Text each time.
Hope you can help me out on this. (If it can be done, i know You will) :-)
Thank you in advance,
ANSWER: This is one of those questions where I'd be very tempted to say "I wouldn't start from there" - there is already a calender control in VBA which would make it easier. However, I can see the problem - in that IValue is a string variable, which is fine, but you then want to run whichever click event is needed - and unfortunately, the name of the macro cannot be a string variable so it isn't going to work! Messy, but what would work, would be to use a select case statement
select case d
'etc etc etc
---------- FOLLOW-UP ----------
QUESTION: Unfortunately I knew you would've said that, i was hoping there was a way to do it like i wanted to.
I tried the calender control in VBA but it just doesn't work like i'd like it to, and if it was possible i would've used that procedure for other routines also. Oh well...
I did consider the select case, but again it would've been a lot simpler my way. Who knows maybe the "VBA Guys" will implement somthing like that in further versions. :-)
Thank you once again for your help.
I had another thought which was a control array, but they don't exist in VBA - so I looked for an alternative and
so you could then do something like
Private Sub dayClicker()
txtDay.Text = Me.Controls("day" & counterVariable).caption
MonN = Me.spnCalm.Value
Me.controls("TextBox" & counterVariable).Text = DateSerial(txtYear.Text, MonN, txtDay.Text)
so one click subroutine