Cleaning Up Repetitive Code

[Ark68]

I have a userform with the following initialization code. The initialization code sets the controls within the form to their defaults. As you can see by the code, it's very repetitive, and the controls all basically follow the same name nomenclature. For the most part, the control names differ only by one digit.

Although I've Googled a solution, I haven't really come across anythink that I think would apply to situation. (Nor do I know the best search terms). I read alot about arrays, but not sure how, if at all, I could use that concept here. Is anyone able to suggest a way to tighten this code up? Or is this the most efficient means to do what I wish to do?

ate Sub UserForm_Initialize()
    Dim frm_service As Object
    
    Set frm_service = frm_tservices
    
    With frm_services
        'frame8
        Me.cbx_s8_rln.Value = False
        Me.cbx_s8_rln.Locked = False
        Me.cbx_s8_chg.Value = False
        Me.cbx_s8_chg.Locked = False
        Me.tb_s8_lwr.Value = ""
        Me.tb_s8_lwr.Enabled = False
        Me.tb_s8_upr.Value = ""
        Me.tb_s8_upr.Enabled = False
        Me.cb_s8_crew = ""
        Me.tb_s8_crew.Enabled = False
        Me.cb_s8_div.Value = ""
        Me.cb_s8_div.Enabled = False
        Me.cb_s8_pitch.Value = ""
        Me.cb_s8_pitch.Enabled = False
        Me.cb_s8_base.Value = ""
        Me.cb_s8_base.Enabled = ""
        Me.cbt_s8_del.Enabled = False
        Me.cbt_s8_add.Enabled = False
        Me.frm_service8.Visible = False
    
        'frame7
        Me.cbx_s7_rln.Value = False
        Me.cbx_s7_rln.Locked = False
        Me.cbx_s7_chg.Value = False
        Me.cbx_s7_chg.Locked = False
        Me.tb_s7_lwr.Value = ""
        Me.tb_s7_lwr.Enabled = False
        Me.tb_s7_upr.Value = ""
        Me.tb_s7_upr.Enabled = False
        Me.cb_s7_crew = ""
        Me.tb_s7_crew.Enabled = False
        Me.cb_s7_div.Value = ""
        Me.cb_s7_div.Enabled = False
        Me.cb_s7_pitch.Value = ""
        Me.cb_s7_pitch.Enabled = False
        Me.cb_s7_base.Value = ""
        Me.cb_s7_base.Enabled = ""
        Me.cbt_s7_del.Enabled = False
        Me.cbt_s7_add.Enabled = False
        Me.frm_service7.Visible = False
    
        'frame6
        Me.cbx_s6_rln.Value = False
        Me.cbx_s6_rln.Locked = False
        Me.cbx_s6_chg.Value = False
        Me.cbx_s6_chg.Locked = False
        Me.tb_s6_lwr.Value = ""
        Me.tb_s6_lwr.Enabled = False
        Me.tb_s6_upr.Value = ""
        Me.tb_s6_upr.Enabled = False
        Me.cb_s6_crew = ""
        Me.tb_s6_crew.Enabled = False
        Me.cb_s6_div.Value = ""
        Me.cb_s6_div.Enabled = False
        Me.cb_s6_pitch.Value = ""
        Me.cb_s6_pitch.Enabled = False
        Me.cb_s6_base.Value = ""
        Me.cb_s6_base.Enabled = ""
        Me.cbt_s6_del.Enabled = False
        Me.cbt_s6_add.Enabled = False
        Me.frm_service6.Visible = False
    
        'frame5
        Me.cbx_s5_rln.Value = False
        Me.cbx_s5_rln.Locked = False
        Me.cbx_s5_chg.Value = False
        Me.cbx_s5_chg.Locked = False
        Me.tb_s5_lwr.Value = ""
        Me.tb_s5_lwr.Enabled = False
        Me.tb_s5_upr.Value = ""
        Me.tb_s5_upr.Enabled = False
        Me.cb_s5_crew = ""
        Me.tb_s5_crew.Enabled = False
        Me.cb_s5_div.Value = ""
        Me.cb_s5_div.Enabled = False
        Me.cb_s5_pitch.Value = ""
        Me.cb_s5_pitch.Enabled = False
        Me.cb_s5_base.Value = ""
        Me.cb_s5_base.Enabled = ""
        Me.cbt_s5_del.Enabled = False
        Me.cbt_s5_add.Enabled = False
        Me.frm_service5.Visible = False
    
        'frame4
        Me.cbx_s4_rln.Value = False
        Me.cbx_s4_rln.Locked = False
        Me.cbx_s4_chg.Value = False
        Me.cbx_s4_chg.Locked = False
        Me.tb_s4_lwr.Value = ""
        Me.tb_s4_lwr.Enabled = False
        Me.tb_s4_upr.Value = ""
        Me.tb_s4_upr.Enabled = False
        Me.cb_s4_crew = ""
        Me.tb_s4_crew.Enabled = False
        Me.cb_s4_div.Value = ""
        Me.cb_s4_div.Enabled = False
        Me.cb_s4_pitch.Value = ""
        Me.cb_s4_pitch.Enabled = False
        Me.cb_s4_base.Value = ""
        Me.cb_s4_base.Enabled = ""
        Me.cbt_s4_del.Enabled = False
        Me.cbt_s4_add.Enabled = False
        Me.frm_service4.Visible = False
    
        'frame3
        Me.cbx_s3_rln.Value = False
        Me.cbx_s3_rln.Locked = False
        Me.cbx_s3_chg.Value = False
        Me.cbx_s3_chg.Locked = False
        Me.tb_s3_lwr.Value = ""
        Me.tb_s3_lwr.Enabled = False
        Me.tb_s3_upr.Value = ""
        Me.tb_s3_upr.Enabled = False
        Me.cb_s3_crew = ""
        Me.tb_s3_crew.Enabled = False
        Me.cb_s3_div.Value = ""
        Me.cb_s3_div.Enabled = False
        Me.cb_s3_pitch.Value = ""
        Me.cb_s3_pitch.Enabled = False
        Me.cb_s3_base.Value = ""
        Me.cb_s3_base.Enabled = ""
        Me.cbt_s3_del.Enabled = False
        Me.cbt_s3_add.Enabled = False
        Me.frm_service3.Visible = False
        
        'frame2
        Me.cbx_s2_rln.Value = False
        Me.cbx_s2_rln.Locked = False
        Me.cbx_s2_chg.Value = False
        Me.cbx_s2_chg.Locked = False
        Me.tb_s2_lwr.Value = ""
        Me.tb_s2_lwr.Enabled = False
        Me.tb_s2_upr.Value = ""
        Me.tb_s2_upr.Enabled = False
        Me.cb_s2_crew = ""
        Me.tb_s2_crew.Enabled = False
        Me.cb_s2_div.Value = ""
        Me.cb_s2_div.Enabled = False
        Me.cb_s2_pitch.Value = ""
        Me.cb_s2_pitch.Enabled = False
        Me.cb_s2_base.Value = ""
        Me.cb_s2_base.Enabled = ""
        Me.cbt_s2_del.Enabled = False
        Me.cbt_s2_add.Enabled = False
        Me.frm_service2.Visible = False
    
        'frame1
        Me.cbx_s1_rln.Value = False
        Me.cbx_s1_rln.Locked = False
        Me.cbx_s1_chg.Value = False
        Me.cbx_s1_chg.Locked = False
        Me.tb_s1_lwr.Value = ""
        Me.tb_s1_lwr.Enabled = False
        Me.tb_s1_upr.Value = ""
        Me.tb_s1_upr.Enabled = False
        Me.cb_s1_crew = ""
        Me.tb_s1_crew.Enabled = False
        Me.cb_s1_div.Value = ""
        Me.cb_s1_div.Enabled = False
        Me.cb_s1_pitch.Value = ""
        Me.cb_s1_pitch.Enabled = False
        Me.cb_s1_base.Value = ""
        Me.cb_s1_base.Enabled = ""
        Me.cbt_s1_del.Enabled = False
        Me.cbt_s1_add.Enabled = False
        Me.frm_service1.Visible = True
        
        Me.Width = 366
        Me.Height = 288
        
        Me.frmbtn_cancel.Top = 288
        Me.frmbtn_cancel.Left = 276
    
    End With
    
    
End Sub

 

[RoryA]

Why would you need to do that in the Initialize event? If those are the defaults, they should already be set when the form loads.

 

[Ark68]

Hmmmm ... good point.
Not sure where I was going with this. Thanks Rory!

But is there a way, just out of curiosity to do a "mass" type change control properties like this?

 

[RoryA]

Generally, I'd use a loop and check each control type.

 

[Ark68]

OK ... I think I understand the concept. Will see what I can do if I get to having to do that.

 

[Norie]

While I agree with Rory that you should set the defaults for the controls at design time that is a lot of controls and there's a good chance you might miss a few.

To do it in the initialize event you could try something like this.

Private Sub UserForm_Initialize()
Dim frm_service As Object
Dim idx As Long

    Set frm_service = frm_tservices

    For idx = 1 To 8 Step -1
        Me.Controls("cbx_s" & idx & "_rln").Value = False
        Me.Controls("cbx_s" & idx & "_rln").Locked = False
        Me.Controls("cbx_s" & idx & "_chg").Value = False
        Me.Controls("cbx_s" & idx & "_chg").Locked = False
        Me.Controls("tb_s" & idx & "_lwr").Value = ""
        Me.Controls("tb_s" & idx & "_lwr").Enabled = False
        Me.Controls("tb_s" & idx & "_upr").Value = ""
        Me.Controls("tb_s" & idx & "_upr").Enabled = False
        Me.Controls("cb_s" & idx & "_crew").Value = ""
        Me.Controls("tb_s" & idx & "_crew").Enabled = False
        Me.Controls("cb_s" & idx & "_div").Value = ""
        Me.Controls("cb_s" & idx & "_div").Enabled = False
        Me.Controls("cb_s" & idx & "_pitch").Value = ""
        Me.Controls("cb_s" & idx & "_pitch").Enabled = False
        Me.Controls("cb_s" & idx & "_base").Value = ""
        Me.Controls("cb_s" & idx & "_base").Enabled = ""
        Me.Controls("cbt_s" & idx & "_del").Enabled = False
        Me.Controls("cbt_s" & idx & "_add").Enabled = False
        Me.Controls("frm_service" & idx).Visible = False
    Next idx

    Me.Width = 366
    Me.Height = 288

    Me.frmbtn_cancel.Top = 288
    Me.frmbtn_cancel.Left = 276

End Sub