selective form refresh

[Showard27]

Hi There, I am struggling with a form action. In principlal I have a simple form with four combo boxes. The contents of these are populated when the form is initialised, by routines in code, and the default value is defined. (they are all dates or derivatives)

I then want the routine to go off and refresh a chart everytime the user changes a value in one of the comboboxes, which is fine with a _change sub.

However the _change routine is triggered when the initialisation routine runs, and this causes the proceedure to fail as not all the values are populated.

I have tried to fix this with an "if not" statement which you can see in the routine for combobox1, when this is triggered by the initialisation routine me.combobox4.value = "", but that is not nothing !!!! I get a "object required error.

So in simple terms how do I ensure the change routines only run after the intialisation (population) has taken place. I hope this makes sence. The form code is as below

Private Sub ComboBox1_Change()
If Not Me.ComboBox4.Value Is Nothing Then
    ShowCountComp
Else: End If

End Sub
Private Sub ComboBox2_Change()
    ShowCountComp
End Sub

Private Sub ComboBox3_Change()
    ShowCountComp
End Sub

Private Sub ComboBox4_Change()
    ShowCountComp
End Sub

Private Sub UserForm_Initialize()
PopYearlist
'Define the years displayed on the chart
        Me.ComboBox1.List = SYearArray
        Me.ComboBox2.List = SYearArray
                Me.ComboBox1.Text = Me.ComboBox1.List(2)
                Me.ComboBox2.Text = Me.ComboBox2.List(3)
PopMYlist
    Me.ComboBox3.List = MonthArray
    Me.ComboBox4.List = MonthArray
        For i = 0 To Me.ComboBox4.ListCount - 1
            Me.ComboBox3.List(i) = Format(DateValue(Me.ComboBox3.List(i)), "mmm-yy")
            Me.ComboBox4.List(i) = Format(DateValue(Me.ComboBox4.List(i)), "mmm-yy")
        Next i
            Me.ComboBox3.Value = Format(DateValue(MonthArray(UBound(MonthArray))), "mmm-yy")
            Me.ComboBox4.Value = Format(DateValue(MonthArray(UBound(MonthArray))), "mmm-yy")
    ShowCountComp
End Sub

Private Sub ShowCountComp()
    Rg1Yr = Me.ComboBox1.Value
    Rg2Yr = Me.ComboBox2.Value
        ViewOn1 = DateValue("1 " & Me.ComboBox3.Value)
        ViewOn2 = DateValue("1 " & Me.ComboBox4.Value)
            CountCompY
End Sub

All help or hints gratefully received

Simon

 

[Showard27]

Hi all

Still having issues, however have tied the problem down to one area of code.

Private Sub ComboBox1_Change()
If Not Me.ComboBox4.Value Is Null Then
    ShowCountComp
Else: End If
End Sub

this is a simple requirmemnt, run the sub routine is there is a value in combobox4, if combobox4 hasnt yet had a value assigned, then dont run the sub routine.
However the line of code: "If Not Me.ComboBox4.Value Is Null Then" is giving me a 424 runtime error, object required. Any idea what object is required?
Thanks, and still confused

Simon