Combobox Click event working at Userform Initialise

[stirlingmw1]

Morning All, Hope you are all staying safe.
I have had a problem with a Userform for a while now and have narrowed the problem down to a section of code, can anyone help.

Problem - I have a Userform that when Initialised shows the data from a worksheet in a number of TextBoxes and ComboBoxes within pages of a Mulitipage Control. the majority of the data is displayed correctly in the correct TextBox/ComboBox apart from one ComboBox (ComboBox49). This ComboBox displays the data from Sheets("Data").Range("CQ3"), and based on this data makes other TextBoxes/Labels and MultiPages visible or Hidden. it takes its rowsource from variables set when data in ComboBox5 is selected "Diver", "ROV". "Towed Sonar" or "UUV" can be selected. So for example if "ROV" is selected in ComboBox5 then the RowSource for ComboBox49 will be "Vehicle_Type", giving "SELECT, I Round, T Round and C Round" as selectable variables. If ComboBox5 is subsequently changed to another variable another RowSource is selected and the previous data in ComboBox49 is removed and the displayed data at ListIndex 0 "SELECT" of the new RowSource is displayed. The code works correct when the Userform is running, but the problem is that when the Userform is initialised the data in ComboBox49 is removed and ListIndex 0 of the appropriate RowSource is displayed.
I have narrowed this problem down to ComboBox5_Click() event which seems to run at initialise. Have I not added some code to the Click event, or should this actually be another event. I have tried the same code in ComboBox5_AfterUpdate() event but it doesnt change the RowSource in ComboBox49 until another TextBox/ComboBox is selected, so I assume the Click event is the right one.

Please forgive me for my messy coding, I am still learning.

Private Sub ComboBox5_Click()
Application.ScreenUpdating = False

Sheets("Data").Visible = True

Select Case Me.ComboBox5
    Case Is = "Diver"
    Me.Combobox49.Visible = False
    Me.TextBox69.Visible = False
    Me.Label107.Visible = False
    Me.Label108.Visible = False
    Me.Label351.Visible = False
    Me.MultiPage1.Pages("Page1").Visible = True
    Me.MultiPage1.Pages("Page2").Visible = True
    Me.MultiPage1.Pages("Page3").Visible = False
    Me.MultiPage1.Pages("Page4").Visible = True
    Me.MultiPage1.Pages("Page5").Visible = True
    Me.MultiPage1.Pages("Page6").Visible = False
    Me.MultiPage1.Pages("Page7").Visible = False
    Me.MultiPage1.Pages("Page8").Visible = False
    Me.MultiPage1.Pages("Page9").Visible = False
    Sheets("Data").Range("CQ3") = vbNullString

    Case Is = "ROV"
    Me.Combobox49.RowSource = "Vehicle_Type"
    Me.Combobox49.ListIndex = 0
    Me.Frame9.Caption = "ROV Details"
    Me.MultiPage1.Pages("Page7").Caption = "ROV Details"
    Me.Combobox49.Visible = True
    Me.ComboBox3.Visible = True
    Me.Label107.Visible = True
    Me.Label11.Visible = True
    Me.Label351.Visible = True
        Me.MultiPage1.Pages("Page1").Visible = True     'General Details
        Me.MultiPage1.Pages("Page2").Visible = True     'Ship and Crew/Team Details
        Me.MultiPage1.Pages("Page3").Visible = False    'Vehicle Details
        Me.MultiPage1.Pages("Page4").Visible = True     'Environmentals
        Me.MultiPage1.Pages("Page5").Visible = True     'Target Details
        Me.MultiPage1.Pages("Page6").Visible = False    'Seafox QLA
        Me.MultiPage1.Pages("Page7").Visible = False    'REMUS 100 details
        Me.MultiPage1.Pages("Page8").Visible = False    'Additional Details
        Me.MultiPage1.Pages("Page9").Visible = False    'REMUS 600 Details
    Me.Label7.Caption = "OOW"
    Me.TextBox4.Text = "Must be completed"
    Me.Label8.Caption = "MWO"
    Me.TextBox5.Text = "Must be completed"
    TextBox6.Text = "Must be completed"
    Me.Label9.Visible = True
    Me.TextBox6.Visible = True
    Me.Combobox49.Text = Sheets("Data").Range("CQ3").Text

'================================================================
'    Case "Sonar"
'    Me.ComboBox49.RowSource = "Sonar_Type"
'    ComboBox49.ListIndex = 0
'    Me.ComboBox49.Visible = True
'    Me.Label107.Visible = True
'    Label351.Visible = true
'    MultiPage1.Pages("Page1").Visible = True
'    MultiPage1.Pages("Page2").Visible = True
'    MultiPage1.Pages("Page3").Visible = False
'    MultiPage1.Pages("Page4").Visible = True
'    MultiPage1.Pages("Page5").Visible = False
'    MultiPage1.Pages("Page6").Visible = False
'    MultiPage1.Pages("Page7").Visible = False
'    MultiPage1.Pages("Page8").Visible = False
'    MultiPage1.Pages("Page9").Visible = False
'================================================================
' Selects the Multipages for Towed Sonar
    Case Is = "Towed Sonar"
    Me.Combobox49.RowSource = "Towed_Type"
    Me.Combobox49.ListIndex = 0
    Me.Frame9.Caption = "Towed Sonar Mission Details"
    Me.Combobox49.Visible = True
    Me.Label107.Visible = True
    Me.Label351.Visible = True
        Me.MultiPage1.Pages("Page1").Visible = True    'General Details
        Me.MultiPage1.Pages("Page2").Visible = True    'Ship and Crew Details
        Me.MultiPage1.Pages("Page3").Visible = False   'Vehicle Details
        Me.MultiPage1.Pages("Page4").Visible = True    'Environmentals
        Me.MultiPage1.Pages("Page5").Visible = False   'Target Details
        Me.MultiPage1.Pages("Page6").Visible = False   'Seafox QLA
        Me.MultiPage1.Pages("Page7").Visible = True    'R100 Details
        Me.MultiPage1.Pages("Page8").Visible = True    'Additional Annotations
        Me.MultiPage1.Pages("Page9").Visible = False   'R600 Details
    Me.Label7.Caption = "Team Leader"
    Me.TextBox4.Text = "Must be complete"
    Me.Label8.Caption = "Mission Programmer"
    Me.Label9.Visible = False
    Me.TextBox6.Visible = False
'================================================================
' Selects the Multipages for UUV
    Case Is = "UUV"
    Me.Combobox49.RowSource = "UUV_Type"
    Me.Combobox49.ListIndex = 0
    Me.Combobox49.Visible = True
    Me.Label107.Visible = True
    Me.Label351.Visible = True
        Me.MultiPage1.Pages("Page1").Visible = True
        Me.MultiPage1.Pages("Page2").Visible = True
        Me.MultiPage1.Pages("Page3").Visible = False
        Me.MultiPage1.Pages("Page4").Visible = True
        Me.MultiPage1.Pages("Page5").Visible = False
        Me.MultiPage1.Pages("Page6").Visible = False
        Me.MultiPage1.Pages("Page7").Visible = False
        Me.MultiPage1.Pages("Page8").Visible = False
        Me.MultiPage1.Pages("Page9").Visible = False

  End Select

Sheets("Data").Range("V3") = ComboBox5.Text
TextBox68 = ComboBox5.Text

Sheets("Data").Visible = False
Application.ScreenUpdating = True
End Sub

Thanks

Steve

 

[dmt32]

Hi,
if you think that you have narrowed your issue down to the UserForm_Intialize event in way you describe then try adding following line of code at top of the procedure causing the problem & see if helps

Private Sub ComboBox5_Click()
    If Not Me.Visible Then Exit Sub
    
    'rest of code

End Sub

Dave

 

[stirlingmw1]

Hi,
if you think that you have narrowed your issue down to the UserForm_Intialize event in way you describe then try adding following line of code at top of the procedure causing the problem & see if helps

Private Sub ComboBox5_Click()
    If Not Me.Visible Then Exit Sub
   
    'rest of code

End Sub

Dave

Thank you so much, works perfect.

Thanks for your help.

Steve

 

[dmt32]

Thank you so much, works perfect.

Thanks for your help.

Steve

Glad resolved for you

Dave