A mask with Option Buttons in Excel

[deltaquattro]

Hi,

I'm writing a small application in VB 6.5 for Excel 2000, for me and
some coworkers in the office <-> doesn't have to be very robust since
it's not going to be sold to anyone. I read an array of strings from a
text file, corresponding to some test case names, and I want the user
to be able to select only one of them. I thought that a small windows
with radio buttons (maybe in VBA they're called Option Button?) would
do the job just nicely. Problem is, I just recently started dabbling
with VBA for Excel and concerning user interaction I only know about
MsgBox and InputBox functions. I tried to study the help docs
concerning OptionButton but I really don't understand what's going on.
Could you please help me? Thanks a lot,

ciao

Sergio

 

[Scott Ketelaar]

Ok, first of all, I assume you are using userforms. To make a userform,
simply go to Insert > Userform. Now you can draw contols on the box, resize
it, ect. To view it, simply press F5. by double clicking on a conrol, you
can see and edit the code it execures when the selected action is performed.
(eg. CommandButton1.click). Optionbuttons on userforms are very straight
forward. In each frame, only one optionbuttion can be selected. Thats it.
VBA takes care of everything. Add an option button and your done. To find
out which one is selected, you simply query every optionbutton until you
find the one set to True.

--Scott

 

[deltaquattro]

Scott,

thank you so much, I was able to solve my problem with your help. The
only difficulty was that the number of Optionbuttons has to be
variable, but I solved using a ListBox, and I just Add an item to it
each time I find a new case. I would never have found the way to do
this without your reply. You're great

Best Regards

Sergio Rossi

 

[Scott Ketelaar]

Oh, By the way, you can enumerate anything on a userform with a For Each...
Next loop. So (This is untested; may need a few tweaks)

'----------------------------------------------------------------------

Dim OB, ctr = 0

For each OB in userform1.optionbuttions
ctr = ctr + 1
next

Msgbox ctr

'-----------------------------------------------------------------------

Hope that helps

-Scott
Scott,

thank you so much, I was able to solve my problem with your help. The
only difficulty was that the number of Optionbuttons has to be
variable, but I solved using a ListBox, and I just Add an item to it
each time I find a new case. I would never have found the way to do
this without your reply. You're great

Best Regards

Sergio Rossi

 

[deltaquattro]

Hi, Scott!

Thank you to your help I was able to create the UserForm. However, I'm
not able to pass data between the UserForm code and my main code.
Example:

Sub Main

Dim StrArray(2) As String

StrArray(1)="foo"
StrArray(2)="bar"
Call UserForm_Initialize(StrArray)

End Sub

Now, in my UserForm code (I mean, the code shown when you point to
UserForm1 and you click on 'View Code'), I have the following:

Sub UserForm_Initialize(StrArray() As String)

Dim i As Long

'Load User Form
For i = 1 To NOpConds
ListBox1.AddItem OpCond(i)
Next

End Sub

However, it doesn't seem to work, i.e., I don't see the UserForm with
the loaded data. What am I doing wrong? Thanks.

Best Regards

Sergio Rossi

 

[deltaquattro]

Hi, Scott!

Thank you to your help I was able to create the UserForm. However, I'm
not able to pass data between the UserForm code and my main code.
Example:

Sub Main

Dim StrArray(2) As String

StrArray(1)="foo"
StrArray(2)="bar"
Call UserForm_Initialize(StrArray)

End Sub

Now, in my UserForm code (I mean, the code shown when you point to
UserForm1 and you click on 'View Code'), I have the following:

Sub UserForm_Initialize(StrArray() As String)

Dim i As Long

'Load User Form
For i = 1 To NOpConds
    ListBox1.AddItem OpCond(i)
Next

End Sub

However, it doesn't seem to work, i.e., I don't see the UserForm with
the loaded data. What am I doing wrong? Thanks.

Best Regards

Sergio Rossi

Problem solved! I cannot call UserForm_Initialize() from the main. And
I cannot pass it datas, because it's illegal in VBA. Neither I can
declare StrArray public in the object module of UserForm, since arrays
cannot declared Public in object modules. The solution is to declare
StrArray Public *in the main code*. So:


-----------------------------------------
'Main code
Public StrArray() As String

Sub Main

Redim StrArray(2) As String

StrArray(1)="foo"
StrArray(2)="bar"
UserForm1.Show vbModal

End Sub
---------------------------------------------


-----------------------------------------
'UserForm code
Private UserForm_Initialize()

Dim i As Long

'Load User Form
For i = 1 To UBound(StrArray)
ListBox1.AddItem StrArray(i)
Next

End Sub
------------------------------------------------

and hey pronto! It works I hope this can be of help also to other
people.

Best Regards

Sergio Rossi