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

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

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

Show all headers | View raw

> Hi GS,
> I put the formulas in D4 and D7 but got a "Circular Reference" error.  Any 
> clues?

You probably defined the RefersTo for "ThisMonth" incorrectly. It needs to be 
col-absolute, row-relative...

  With A1 selected, RefersTo is "=$A1"

..where the "$" symbol denotes 'Absolute'! You should have removed this from 
the row as displayed ("=$A$1") when the dialog opened.
>
> What I really wanted to know -- and perhaps wasn't clear on -- was how you 
> call a subroutine based on combinations of two different option buttons in a 
> form.

I'd go with using 2 comboboxes for simplicity, but here's how I'd handle this 
approach:

  In the opt's 'Tag' property put the value you want that opt to return to
  a variable when clicked...

  OptionButton1:
    Name = optJan: Tag = January

  OptionButton2:
    Name = optFeb: Tag = February

  OptionButton3:
    Name = opt2016: Tag = 2016

  OptionButton4:
    Name = opt2017: Tag = 2017

In the _Click event for each button...

  Private Sub optJan_Click()
    msMonth = Iif(Me.optJan, optJan.Tag, "")
  End Sub

  Private Sub optFeb_Click()
    msMonth = Iif(Me.optJan, optFeb.Tag, "")
  End Sub

  Private Sub opt2016_Click()
    msYear = Iif(Me.optJan, opt2016.Tag, "")
  End Sub

  Private Sub opt2017_Click()
    msYear = Iif(Me.optJan, opt2017.Tag, "")
  End Sub

..where msMonth and msYear are defined as module level vars of type String:

  Dim msMonth$, msYear$


Sub CommandButton1_Click()
  Dim sSubToRun$
  sSubToRun = msMonth & msYear
  If sSubToRun <> "" Then
    Application.Run sSubToRun: Call ResetForm
End Sub

Sub ResetForm()
  'Assumes you used this event to set your form up
  Call Userform_Initialize: Me.Repaint
End Sub


Combobax Approach:

Controls on userform:
  Combobox1: Name = "cboMonths"
  Combobox2: Name = "cboYears"
  CommandButton1: Name = btnRun

code:
  '[module level declarations]
  Dim msSubToRun$
  Const sMonths$ = "<comma delimited list of month names>"
  Const sYears$ = "<comma delimited list of years>"

  Private Sub UserForm_Initialize()
    Me.cboMonths.List = Split(msMonths, ",")
    With Me.cboYears
      .List = Split(msYears, ","): .Enabled = False
    End With
    Me.btnRun.Enabled = False
  End Sub

  Private Sub cboMonths_Change()
    msSubToRun = Me.cboMonths.Text: ResetControls
  End Sub

  Private Sub cboYears_Change()
    If msSubToRun <> "" Then
      msSubToRun = msSubToRun & Me.cboYears.Text
      Me.btnRun.Enabled = True
    End If
  End Sub

  Private Sub btnRun_Click()
    If msSubToRun <> "" Then
      Application.Run msSubToRun: ResetForm
    End If
  End Sub

  Sub ResetControls()
    Me.cboYears.Enabled = (msSubToRun <> "")
    Me.btnRun.Enabled = Me.cboYears.Enabled
  End Sub

  Sub RestForm()
    Me.cboMonths = "": Me.cboYears.Text = ""
    msSubToRun = "": ResetControls
  End Sub

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

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