Having A Macro Run When A Selection Is Made

[bgkgmg]

I have a combobox on a userform with a drop down list consisting of the 12
months. When I select a month I want the corresponding macro to run.
Example-After selecting January macro4 will run:
Sub Macro4()
ActiveSheet.Unprotect
Application.Goto Reference:="PAYMENTS!R1C16:R1C32"
ActiveSheet.Protect
End Sub

Private Sub cbomonth_Change()
If cbomonth.Value = "January" Then Macro4

I am having trouble in what to add above when I select February(Macro3)
Sub Macro3()
ActiveSheet.Unprotect
Application.Goto Reference:="PAYMENTS!R1C35:R1C51"
ActiveSheet.Protect
End Sub
which I want to bring me to
Application.Goto Reference:="PAYMENTS!R1C35:R1C51"

Thanks

 

[Jacob Skaria]

Use Select...Case statement..Refer the below link which will be useful.

http://support.microsoft.com/kb/141691

If this post helps click Yes

 

[Dave Peterson]

If you name the macros nicely (January_Mac, February_Mac, ..., December_Mac),
you could use something like:

if me.combobox1.listindex < 0 then
'nothing chosen, do nothing
else
application.run me.combobox1.value & "_Mac"
end if

 

[Patrick Molloy]

if your months are in order, then JAn is index 0 and Dec is inex 11
if your macros are also similarly numerically assigned, then

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = -1 Then Exit Sub
Run "macro" & ComboBox1.ListIndex + 1
End Sub

 

[Chip Pearson]

Here's one way to do it, such that the name of the procedure for a
month doesn't have to have any relationship to the month to which it
is associated. The names can be anything you like.



Sub RunProcForMonth()
Dim Procs As Variant
Dim N As Long
Dim MonthName As String
MonthName = UserForm1.Combobox1.Value
' The Procs array should have 12 elements, each of which
' is the name of the procedure to run for a particular month.
' The first name is for January, the next for February, and so
' to the 12th element for December. The names can be anything
' you want. There need not be anything in the name of the proc
' related to the month to which it is associated.
Procs = Array("JanProc", "FebProc", "MarchProc", "AprilProc")
'....
' The IIF piece of the following line compensates for any
' Option Base statement that might be in effect.
N = Month(DateValue(MonthName & " 1,2000")) - _
IIf(LBound(Procs) = 0, 1, 0)
Application.Run Procs(N)
End Sub


Fill the Array with the names of each procedure, in month order (e.g,.
the first element is the array is the proc for January, the fifth
element is for May, and so on). The code creates the string
'monthName 1, 2000' and then converts that strinng to an actual date,
and then uses the Month function gets the month number (1 = Jan, 2 =
Feb, etc) for that date string. The day of month and the year used
with DateValue are irrelevant. Only the month name matters. That
month number is used as the index into the array Procs of function
names (the IIF makes the code agnostic to whatever Option Base
statement might be in effect) and the appropriate function, Procs(N),
is executed by Application.Run.

So, for example, if your combobox has the value "May", the string "May
1,2000" is created and passed to DateValue which converts it to a real
date value. The Month function gets the month number of that date (in
this case, 5), and then adds 0 or 1 to the month number to compensate
for any Option Base statement, and puts that month number in the
variable N. The Nth element of Procs, Procs(N), is passed to
Application.Run, which runs MayProc, or whatever was in the 5th
position of Procs.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)