Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109999

Re: Call subroutine based on combination of Option buttons (two groups)

Show all headers | View raw

On Sunday, April 9, 2017 at 1:44:49 PM UTC-6, Robert Logan wrote:
> Goal: Calculate average precipitation based on Month and Year.
> Worksheets: "2016" and "2017"
> Option Buttons: OptionButton1, OptionButton2, OptionButton3, OptionButton4,
> Form: UserForm1
> 
> I have two groups of Option buttons on UserForm1 -- one group under "Month" (Frame1) and one group under "Year (Frame2). 
> 
> I'd like to call a subroutine based on the combination of buttons selected. 
> 
> For example, if a user selects January and selects 2016 (and then clicks "OK"), subroutine "January2016" is called.  From there, I'd like to 1) activate sheet "2016" and 2) calculate the average precipitation for January and 3) put the value in the appropriate cell, in this case cell C9. Same for January 2017, etc.
> 
> Here's what "Form1" looks like.  (The zeros are option buttons.)
> 
> Month            Year                [OK]
> O January        O 2016	             [Cancel]   
> O February       O 2017
> 
> Here's what the sheet, "2016" looks like.  (Sheet "2017" is similar.)
> 	-A-	-B-	     -C-
> 1	Month	Date	     Precip
> 2	Jan	01/01/17	5
> 3	Jan	01/15/17	10
> 4	Jan	01/31/17	15
> 5	Feb	02/01/17	20
> 6	Feb	02/15/17	30
> 7	Feb	02/28/17	40
> 8	===============================	
> 9	Jan	AvgPrecip	_____	
> 10	Feb	AvgPrecip	_____	
> 
> Here's my code. (It works so far but I haven't figured out how to go to the appropriate sheet and calculate the average rainfall.)
> 
> Sub CommandButton1_Click()
> If Me.OptionButton1.Value = True And Me.OptionButton3.Value = True Then Call January2016
> 
> If Me.OptionButton1.Value = True And Me.OptionButton4.Value = True Then Call January2017
> 
> If Me.OptionButton2.Value = True And Me.OptionButton3.Value = True Then Call February2016
> 
> If Me.OptionButton2.Value = True And Me.OptionButton4.Value = True Then Call February2017
> End Sub
> 
> Sub CommandButton2_Click()
>   Unload UserForm1
> End Sub
> 
> Sub January2016()
> MsgBox "January 2016"
> ' Go to Sheet 2016 and calculate average precipitation for January
> End Sub
> 
> Sub January2017()
> MsgBox "January 2017"
> ' Go to Sheet 2017 and calculate average precipitation for January
> End Sub
> 
> Sub February2016()
> MsgBox "February 2016"
> ' Go to Sheet 2017 and calculate average precipitation for January
> End Sub
> 
> Sub February2017()
> MsgBox "February 2017"
> ' Go to Sheet 2017 and calculate average precipitation for February
> End Sub
> 
> Sub ClearOptionButtons()
>   UserForm1.OptionButton1.Value = False
>   UserForm1.OptionButton2.Value = False
>   UserForm1.OptionButton3.Value = False
>   UserForm1.OptionButton4.Value = False
> End Sub

GS -
I will try your code over the next several days because I'm slammed with meetings. I think your last suggestion(s) address my original design goal. Thank you so much!  I appreciate it.  I'll get back to you...

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Call subroutine based on combination of Option buttons (two groups) Robert Logan <robert.logan.montana@gmail.com> - 2017-04-09 12:44 -0700
  Re: Call subroutine based on combination of Option buttons (two groups) GS <gs@v.invalid> - 2017-04-10 06:07 -0400
  Re: Call subroutine based on combination of Option buttons (two groups) cacuoc334465c <cacu123oc365a@gmail.com> - 2017-04-10 17:36 +0100
  Re: Call subroutine based on combination of Option buttons (two groups) Robert Logan <robert.logan.montana@gmail.com> - 2017-04-10 13:36 -0700
    Re: Call subroutine based on combination of Option buttons (two groups) GS <gs@v.invalid> - 2017-04-10 19:16 -0400
  Re: Call subroutine based on combination of Option buttons (two groups) cacuoc334465c <cacu123oc365a@gmail.com> - 2017-04-10 21:37 +0100
  Re: Call subroutine based on combination of Option buttons (two groups) cacuoc334465c <cacu123oc365a@gmail.com> - 2017-04-11 00:35 +0100
  Re: Call subroutine based on combination of Option buttons (two groups) cacuoc334465c <cacu123oc365a@gmail.com> - 2017-04-11 13:36 +0100
  Re: Call subroutine based on combination of Option buttons (two groups) cacuoc334465c <cacu123oc365a@gmail.com> - 2017-04-11 18:36 +0100
  Re: Call subroutine based on combination of Option buttons (two groups) cacuoc334465c <cacu123oc365a@gmail.com> - 2017-04-11 19:37 +0100
  Re: Call subroutine based on combination of Option buttons (two groups) Robert Logan <robert.logan.montana@gmail.com> - 2017-04-11 15:02 -0700
    Re: Call subroutine based on combination of Option buttons (two groups) GS <gs@v.invalid> - 2017-04-11 18:04 -0400
  Re: Call subroutine based on combination of Option buttons (two groups) cacuoc334465c <cacu123oc365a@gmail.com> - 2017-04-12 19:10 +0100
  Re: Call subroutine based on combination of Option buttons (two groups) cacuoc334465c <cacu123oc365a@gmail.com> - 2017-04-12 20:11 +0100

csiph-web