Clear radio-buttons after completing forms

I'm having a series of userforms, making a questionnaire. Each userform consists of a couple optionbuttons/checkboxes. The program works fine. However, when I start the program again (without quitting Excel) the radiobuttons and checkboxes are filled in the same way like the previous user did.

I tried with each userform initialization to call to a module:

Option base 0

Sub clearBtns()
Dim optBtn(10) As Variant
optBtn(0) = "optA"
optBtn(1) = "optB"
optBtn(2) = "optC"
optBtn(3) = "optD"
optBtn(4) = "optE"
optBtn(5) = "chkA"
optBtn(6) = "chkB"
optBtn(7) = "chkC"
optBtn(8) = "chkD"
optBtn(9) = "chkE"
optBtn(10) = "chkF"

Dim cnt As Integer
For cnt = 0 To 10
    If Not optBtn(cnt) Is Nothing Then
    optBtn(cnt).Value = False
    End If
Next cnt

End Sub

unfortunately this doesn't work (error 424, object required). It has probably something to do with this part:

If Not optBtn(cnt) Is Nothing Then

There has to be an easier way to do this. For extra information: after each form is completed, I use:

Me.Hide
form_x.Show

The last form is different, with:

Unload Me

Maybe there's a problem?

For further explanation:

Option Base 0

Public Sub clearBtns()

Dim optBtn(10) As Variant
optBtn(0) = "optA"
optBtn(1) = "optB"
optBtn(2) = "optC"
optBtn(3) = "optD"
optBtn(4) = "optE"
optBtn(5) = "chkA"
optBtn(6) = "chkB"
optBtn(7) = "chkC"
optBtn(8) = "chkD"
optBtn(9) = "chkE"
optBtn(10) = "chkF"

Dim formArray(27) As Variant
formArray(0) = "page1_1"
formArray(1) = "page1_2"
formArray(2) = "page1_3"
formArray(3) = "page2_1"
formArray(4) = "page2_2"
formArray(5) = "page2_3"
formArray(6) = "page3_1"
formArray(7) = "page3_2"
formArray(8) = "page3_3"
formArray(9) = "page4_1"
formArray(10) = "page4_2"
formArray(11) = "page4_3"
formArray(12) = "page5_1"
formArray(13) = "page6_1"
formArray(14) = "page6_2"
formArray(15) = "page6_3"
formArray(16) = "page7_1"
formArray(17) = "page7_2"
formArray(18) = "page7_3"
formArray(19) = "page8_1"
formArray(20) = "page8_2"
formArray(21) = "page8_3"
formArray(22) = "page9_1"
formArray(23) = "page9_2"
formArray(24) = "page9_3"
formArray(25) = "page10_1"
formArray(26) = "page10_2"
formArray(27) = "page10_3"

Dim cnt As Integer
Dim fCnt As Integer

For fCnt = 0 To 27
    For cnt = 0 To 10
    On Error Resume Next
        formArray(fCnt).Controls(optBtn(cnt)).Value = False
    Next cnt
Next fCnt

End Sub

Above code doesn't work.

Public Sub clearBtns()

Dim optBtn(10) As Variant
optBtn(0) = "optA"
optBtn(1) = "optB"
optBtn(2) = "optC"
optBtn(3) = "optD"
optBtn(4) = "optE"
optBtn(5) = "chkA"
optBtn(6) = "chkB"
optBtn(7) = "chkC"
optBtn(8) = "chkD"
optBtn(9) = "chkE"
optBtn(10) = "chkF"

Dim cnt As Integer

    For cnt = 0 To 10
    On Error Resume Next
        page1_1.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page1_2.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page1_3.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page2_1.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page2_2.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page2_3.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page3_1.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page3_2.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page3_3.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page4_1.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page4_2.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page4_3.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page5_1.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page6_1.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page6_2.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page6_3.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page7_1.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page7_2.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page7_3.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0
    For cnt = 0 To 10
    On Error Resume Next
        page8_1.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0
    For cnt = 0 To 10
    On Error Resume Next
        page8_2.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page8_3.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page9_1.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page9_2.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0
    For cnt = 0 To 10
    On Error Resume Next
        page9_3.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page10_1.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page10_2.Controls(optBtn(cnt)).Value = False
    Next cnt
cnt = 0

    For cnt = 0 To 10
    On Error Resume Next
        page10_3.Controls(optBtn(cnt)).Value = False
    Next cnt

On Error GoTo 0

End Sub

Above code works, but it takes a lot more code and is very inefficient.

Answers


There has to be an easier way to do this. For extra information: after each form is completed, I use:

Me.Hide
form_x.Show

The last form is different, with:

Unload Me

Instead of using Hide on all the forms use Unload. The Userform_Initialize method will only be called when the forms are actually initialized - this does NOT happen when you show a formerly hidden form.

So, when you do something with these forms like

form1.show   'initialize called
'stuff that changes the form attributes
form1.hide
form1.show    'initialize NOT called

You need to either use Unload or move your code into the UserForm_Activate method of each UserForm (which may cause you other issues depending on how these are actually being used).


Need Your Help

filled rubber band in Winforms Application

c# winforms drawing rubber-band

how can i draw a zero opacity rubber band over a windows form with 0.3 opacity?

How does manage.py work?

python django

I just installed django and I'm doing the tutorial on their website.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.