You are here:

Excel/Spinbutton to fire a Command_Click() button.

Advertisement


Question
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.

Example:
Private Sub spnCal_SpinUp()
   day2_Click
End Sub

will fire the

Private Sub day2_Click()
txtDay.Text = day2.Caption
MonN = Me.spnCalm.Value
Me.TextBox2.Text = DateSerial(txtYear.Text, MonN, txtDay.Text)
End Sub

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
   Application.Run lValue
   
End Sub

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,
John.

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
case 1
day1_click
case 2
day2_click
case 3
day3_click
'etc etc etc
end select




---------- 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.

Answer
I had another thought which was a control array, but they don't exist in VBA - so I looked for an alternative and

http://www.vbforums.com/showthread.php?530767-RESOLVED-Excel-VBA-Simulate-Contro

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)
End Sub


so one click subroutine  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.