Dynamic Option Buttons

[ctfreel]

I'm using VBA and Excel and I'm stuck on something. Say I have a worksheet range with 3 entries in it (North, South, East). I want to create a userform with those 3 items listed on a frame as option buttons (with associated label). The next time the macro is run there might be 2 or 4 entries in the range (say West is added). Now I need the userform to show those 4 option buttons on a frame that allows the user to select one of the 4 to perform an action on, say printing records. Can anyone help me?

[Erick de la Torre]

use a control array of option buttons

[ctfreel]

Can you give me a little more to go on?

[Erick de la Torre]

well, about 2 months i make a dynamic menu.

But here goes something moreless about your problem.

U have to insert in to your form an array with 10 (or what you want) option buttons.

When U load your form u have to put your option buttons property visible in false, then u have to know how many ranges u have, then u have to put your option buttons property visible to true.


code
____________________________________________________

Sub Form_load()
Dim i As Integer
For i = 1 To Option1.UBound
Option1(i).Visible = False
Next i
End Sub

For i = 1 to #of ranges u have
option(i).visible = true
next i

[HeSaidJoe]

'add an option button to your form
'right click it and copy and then add
'you will be asked if you want an array..say yes
'delete the button you just added
'leaving the option button with index = 0 on the form

Option Explicit

Public MaxId As Integer

Private Sub option1_Click(Index As Integer)
  Option1(0).Caption = "This is as good as it gets."
End Sub

Private Sub command1_Click()

MaxId = 4
   Dim i
   For i = 1 To MaxId
   Load Option1(i)     ' Create new button.
   Option1(0).SetFocus      ' Reset button selection.
   ' Set new button under previous button.
   Option1(i).Top = Option1(i - 1).Top + 400
   Option1(i).Visible = True  ' Display new
                                 ' button.
   Option1(i).Width = 4000
   Option1(i).Caption = "Option" & i + 1
   Next i
End Sub

'Option buttons are removed by the Click event procedure for the Delete command button:

Private Sub Command2_Click()
 For i = 1 To MaxId
   If MaxId < 1 Then Exit Sub   ' Keep first buttons.
   Unload Option1(i)          ' Delete last button.
   MaxId = MaxId - 1            ' Decrement button count.
   Option1(0).SetFocus       ' Reset button selection.
   Option1(0).Width = 4000

Next
End Sub

[ctfreel]

THANKS FOR RESPONDING, BUT...HOW DO YOU INSERT AN ARRAY OF OPTION BUTTONS ON A FORM? IS THAT SOMETHING YOU CAN DO IN VBA OR ONLY VB? PLEASE EXPLAIN...YOU SAY COPY AND THEN ADD....ADD WHAT?

[Erick de la Torre]

OK. in VBA u can´t insert an array of controls, u just add the number of option butoons you want.