How to reset only some controls on UserForm?

K

keatlim

New Member
Joined
Dec 12, 2017
Messages
6
Dear all,

I am new to Excel VBA.
Been trying to deal with the following problem for days but could not figure out a solution.

I want to enter data for individuals.
  • Each individual has data that are "static" e.g. ID, age, gender and data that "varies" e.g. addresses and contact numbers
  • I would like to enter the data in long format, such that the "static" data will be repeated for every row of varying data i.e. address and contact numbers
  • To avoid re-entering the "static" data for every individual, I would like to write codes that remove only the "varying data" before I enter the next "varying" data.
  • In other words, I want to remove address 1 and contact detail 1 before I enter address 2 and contact detail 2, and to remove address 2 and contact detail 2 before I enter address 3 and contact detail 3 for the same individual ID, age and gender.

Below is my current code:
  • Controls 1 to 10 are for "static" data whereas Controls 11 to 20 are for "varying" data.
  • The codes are intended to remove only Controls 11 to 20 which could be textbox, combobox, option button or listbox.
  • These codes give "Run-time error 438. Object doesn't support this property or method".
  • When I click the "Debug" button, it highlights "For Each ctrl In Me.Controls(x)" but I am not sure how else to edit it.

Can anyone help?
Any advice would be appreciated.

Thank you very much in advanced!


Code: 
Private Sub Clear_Prog()

Dim x As Integer
For x = 11 To 20
    For Each ctrl In Me.Controls(x)
        Select Case TypeName(ctrl)
            Case "TextBox"
                ctrl.Text = ""
            Case "ComboBox"
                ctrl.ListIndex = -1
            Case "OptionButton", "CheckBox"
                ctrl.Value = False
            Case "ListBox"
                For i = 0 To ctrl.ListCount - 1
                    If ctrl.Selected(i) Then
                        ctrl.Selected(i) = False
                    End If
                Next i
        End Select
    Next
Next x

End Sub
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Try this (not tested):

Code: 
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Clear_Prog()


[color=darkblue]Dim[/color] x [color=darkblue]As[/color] [color=darkblue]Integer[/color]
[color=darkblue]For[/color] x = 11 [color=darkblue]To[/color] 20
[B]    [color=darkblue]Set[/color] ctrl = Me.Controls(x)[/B]
    [color=darkblue]Select[/color] [color=darkblue]Case[/color] TypeName(ctrl)
        [color=darkblue]Case[/color] "TextBox"
            ctrl.Text = ""
        [color=darkblue]Case[/color] "ComboBox"
            ctrl.ListIndex = -1
        [color=darkblue]Case[/color] "OptionButton", "CheckBox"
            ctrl.Value = [color=darkblue]False[/color]
        [color=darkblue]Case[/color] "ListBox"
            [color=darkblue]For[/color] i = 0 [color=darkblue]To[/color] ctrl.ListCount - 1
                [color=darkblue]If[/color] ctrl.Selected(i) [color=darkblue]Then[/color]
                    ctrl.Selected(i) = [color=darkblue]False[/color]
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]Next[/color] i
    [color=darkblue]End[/color] [color=darkblue]Select[/color]
[color=darkblue]Next[/color] x


[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
AlphaFrog said:
Try this (not tested):

Code: 
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Clear_Prog()


[COLOR=darkblue]Dim[/COLOR] x [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
[COLOR=darkblue]For[/COLOR] x = 11 [COLOR=darkblue]To[/COLOR] 20
[B]    [COLOR=darkblue]Set[/COLOR] ctrl = Me.Controls(x)[/B]
    [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] TypeName(ctrl)
        [COLOR=darkblue]Case[/COLOR] "TextBox"
            ctrl.Text = ""
        [COLOR=darkblue]Case[/COLOR] "ComboBox"
            ctrl.ListIndex = -1
        [COLOR=darkblue]Case[/COLOR] "OptionButton", "CheckBox"
            ctrl.Value = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]Case[/COLOR] "ListBox"
            [COLOR=darkblue]For[/COLOR] i = 0 [COLOR=darkblue]To[/COLOR] ctrl.ListCount - 1
                [COLOR=darkblue]If[/COLOR] ctrl.Selected(i) [COLOR=darkblue]Then[/COLOR]
                    ctrl.Selected(i) = [COLOR=darkblue]False[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]Next[/COLOR] i
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
[COLOR=darkblue]Next[/COLOR] x


[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Click to expand...


Thanks AlphaFrog for your prompt response!
It works, but there is one small bug.

I actually have some other data entered in another Excel spreadsheet using the same userform.
Your edited codes reset controls 11 to 20 for this other spreadsheet instead of the spreadsheet for which I enter the 'static' and 'varying' data.
Is there a way to specify the spreadsheet I want to reset?

Apologies I did not share this earlier as I wanted to simplify my question.

Thank you very much again in advanced!
 
Upvote 0
It sounds like you pasted the code in the wrong workbook. Put the code in the same code module of the userform you want to reset.
 
Upvote 0
AlphaFrog said:
It sounds like you pasted the code in the wrong workbook. Put the code in the same code module of the userform you want to reset.
Click to expand...

Thanks again AlphaFrog for your prompt response!

The code was pasted in the correct workbook.

To clarify, the data I would like to enter actually have three levels: company details, individual "static" details and "varying" details.

  • I want to have the company and individual details in two separate spreadsheets.
  • So I designed two separate subs / toggle buttons to transfer the data to their respective spreadsheets from the same user form.

This is the sub for company details
Code: 
Private Sub btnOK_Company_Click()

Dim ws As Worksheet
Set ws = Worksheets("CompanyDetails")

Dim newRow As Long
newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1

ws.Cells(newRow, 1).Value = Me.txt.........

End Sub


This is the sub for individual details

Code: 
 Private Sub btnOK_Individual_Click()

Dim ws As Worksheet
Set ws = Worksheets("IndividualDetails")

Dim newRow As Long
newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1

ws.Cells(newRow, 1).Value = Me.txt.........

End Sub

The codes you shared reset controls 11 to 20 from the company details rather than the individual details.
Is it possible to specify that I only want to reset controls from the sub for the individual details?
Tried to search on Google but to no avail.

If it is not possible, I can think of two alternatives currently:
  • To have all of them under the same sub / spreadsheet, but this would mean multiple repetitions of company details and individual "static" details which would be quite messy.
  • To enter the company and individual data with two separate userforms.

Please advise.

Thank you very much in advanced! =)
 
Upvote 0
Replace Me with the worksheet reference.

Code: 
Set ctrl = [B]Worksheets("IndividualDetails")[/B].Controls(x)
 
Last edited:
Upvote 0
AlphaFrog said:
Replace Me with the worksheet reference.

Code: 
Set ctrl = [B]Worksheets("IndividualDetails")[/B].Controls(x)
Click to expand...


Thanks AlphaFrog!

Tried this but it gave "Run-time Error 438. Object doesn't support this property or method".
Debug command highlights the following codes:

Code: 
Set ctrl = Worksheets("IndividualDetails").Controls(x)

The following also gave the same errors:
Code: 
Set ctrl = Worksheets(1).Controls(x)

Code: 
Set ctrl = Sheets("IndividualDetails").Controls(x)

Code: 
Set ctrl = Sheets(1).Controls(x)


Also tried the following; no error but did not remove controls 11 to 20 from the correct worksheet.

Code: 
Private Sub Clear_Prog()

Worksheets("IndividualDetails").Select

Dim x As Integer
For x = 11 To 20
    Set ctrl = Me.Controls(x)
    Select Case TypeName(ctrl)
        Case "TextBox"
            ctrl.Text = ""
        Case "ComboBox"
            ctrl.ListIndex = -1
        Case "OptionButton", "CheckBox"
            ctrl.Value = False
        Case "ListBox"
            For i = 0 To ctrl.ListCount - 1
                If ctrl.Selected(i) Then
                    ctrl.Selected(i) = False
                End If
            Next i
    End Select
Next x

End Sub

Do you have any other thoughts?
 
Upvote 0
If you want to upload an example workbook to a file share site and post the link here, I'll take a look at it.
 
Upvote 0
AlphaFrog said:
If you want to upload an example workbook to a file share site and post the link here, I'll take a look at it.
Click to expand...

Thanks AlphaFrog for offering!
I can't share the whole document in public, so I have simplified the userform, which you can find here:
https://drive.google.com/open?id=1okhwS4EX_Vtst_nVggE3PzYA9JwOcA5v

Somehow after I simplified it, Clear_Prog (now renamed Clear_Ind_Var) no longer resets controls from the other worksheet.
But it still doesn't reset the controls from the intended worksheet and still gives the error if I use the codes you suggested earlier:
Code: 
Set ctrl = Worksheets("IndividualDetails").Controls(x)
 
Upvote 0
This loops through all the controls on the second page (page(1)) of a Multipage object.

Code: 
[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Clear_Ind_Var()
    
    [color=darkblue]Dim[/color] ctrl [color=darkblue]As[/color] MSForms.Control
    
[B]    [color=darkblue]For[/color] [color=darkblue]Each[/color] ctrl [color=darkblue]In[/color] Me.MultiPage1.Pages(1).Controls[/B]
        [color=darkblue]Select[/color] [color=darkblue]Case[/color] TypeName(ctrl)
            [color=darkblue]Case[/color] "TextBox"
                ctrl.Text = ""
            [color=darkblue]Case[/color] "ComboBox"
                ctrl.ListIndex = -1
            [color=darkblue]Case[/color] "OptionButton", "CheckBox"
                ctrl.Value = [color=darkblue]False[/color]
            [color=darkblue]Case[/color] "ListBox"
                For i = 0 [color=darkblue]To[/color] ctrl.ListCount - 1
                    [color=darkblue]If[/color] ctrl.Selected(i) [color=darkblue]Then[/color]
                        ctrl.Selected(i) = [color=darkblue]False[/color]
                    [color=darkblue]End[/color] [color=darkblue]If[/color]
                [color=darkblue]Next[/color] i
        [color=darkblue]End[/color] [color=darkblue]Select[/color]
    [color=darkblue]Next[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Frame scrollbar resets when clicking on/in frame
Replies
5
Views
366
Micron
M
D
  • Question Question
VBA Basic Userform - add -update -delete
Replies
3
Views
685
Drexi
D
P
  • Solved
Userform textbox populated with listbox data how to edit
Replies
6
Views
1K
pure vito
P
P
  • Question Question
How to VBA code to drop data from UserForm to multiple worksheets
2
Replies
17
Views
623
dmt32
D
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
390
Jagadeshrao
J

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top