Exiting form removes all items from my Combo Box?

**Wongadob** · 07-26-2011, 06:33 AM

Hi All,

I am having a bit of an odd issue. When I click the Red X box at the top of my user form (which only contains 1 combobox). It clears all of the items out of my Combo Box the next time I trigger the form. When I select a valid value it autocloses fine and my code hides the form and uses the data require fine. Next time I select it works fine all the items are still in my combo box, but when I quit via (red X - Sorry can't thing of the right term for this) and re-open the form all the data is gone. The code is in 3 parts so I have included it all.

The form is triggered(shown) when I click on a cell. That all seems to work fine. So the user would click on the cell. Select a value from the drop down or exit Using form close(Red X). The user can then re-select this whenever they like. it is supposed to work like an Autofilter because I cannot use the autofilter as all items on this page are locked and protected. So I created a form to in effect emulate it.

Code Part 1 - Creating the Combo Box data (adding items) - This is called only once before control is passed to a new sheet

    If FM_FilterForm.ComboBox1.ListCount <> 0 Then
        For x = 1 To FM_FilterForm.ComboBox1.ListCount
            FM_FilterForm.ComboBox1.RemoveItem 0
        Next x
    End If
    
    FM_FilterForm.ComboBox1.AddItem "All"
        
    rw = Range("Site_Data").Row
    col = Range("Site_Data").Column
    
    While Sheets("Formula Page").Cells(rw, col) <> ""
        If Sheets("Formula Page").Cells(rw, col + 3) = FM_Required Then
            FM_FilterForm.ComboBox1.AddItem Sheets("Formula Page").Cells(rw, col).Text
        End If
        rw = rw + 1
    Wend

The second bit of code is the code that triggers the loading of the user form when a cell is clicked on (I removed the DIM's for size but all variables have been dimensioned

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Clicked = Target.Address(False, False, xlA1, False, "A1")
    
    If Clicked = "C4" And Range("A2") = "Edit" Then
        WasItCancelled = True
        FM_FilterForm.Left = Target.Left
        FM_FilterForm.Top = Target.Top
        FM_FilterForm.Show
        If WasItCancelled = True Then
'            FM_FilterForm.Hide                               'disabled this as I thought that might remove items but no change
            Exit Sub
        Else
           ' Does code that applies various filters to sheet data (No futher operations on the form

End Sub

And Finally the code that is actually associated with the form and/or Combo Box

Private Sub ComboBox1_Click()
    WasItCancelled = False
    ActiveSheet.Unprotect Password:=Sheets("Formula Page").Range("R1")
    Range("C2") = ComboBox1.Value
    ActiveSheet.Protect Password:=Sheets("Formula Page").Range("R1"), DrawingObjects:=True, Contents:=True, Scenarios:=True
    FM_FilterForm.Hide
End Sub

Private Sub UserForm_Activate()
    ComboBox1.Object.Value = ""          ' So no value is selected from last time
End Sub

Private Sub UserForm_Terminate()
    WasItCancelled = True
    Sheets("Original Information").Range("A3").Select       'So I can re-select the cell immediately as focus is changed
End Sub

I just cannot understand why this is removing the items from the list. The combo box displays but there are no items within it?

Please help before I become totally bald!!

Thanks

**romperstomper** · 07-26-2011, 07:04 AM

Where and how do you call that first bit of code?
FYI, this:

 If FM_FilterForm.ComboBox1.ListCount <> 0 Then
        For x = 1 To FM_FilterForm.ComboBox1.ListCount
            FM_FilterForm.ComboBox1.RemoveItem 0
        Next x
    End If

is equivalent to:

 FM_FilterForm.ComboBox1.Clear

**romperstomper** · 07-26-2011, 07:04 AM

Where and how do you call that first bit of code?
FYI, this:

 If FM_FilterForm.ComboBox1.ListCount <> 0 Then
        For x = 1 To FM_FilterForm.ComboBox1.ListCount
            FM_FilterForm.ComboBox1.RemoveItem 0
        Next x
    End If

is equivalent to:

 FM_FilterForm.ComboBox1.Clear

**Wongadob** · 07-26-2011, 07:29 AM

I have a master database of information which contains ALL Data. This data is displayed and edited through a series of filters. I have numerous Sheets which act as data input sheets. The data is input then dependant on the users requirements or security levels the data is filtered and the various cells/columns are locked for input etc. For this particular example only one column of data is editabled yet I need to filter on another column (so they do not have to look at all data in one go) They do not have access to ALL data in the sheet there are many sites which they may only be able to look at a sub-set of those sites. So on the data set up sheet I go through all the sites they are allowed to see and create a combobox list. It clears it then adds each site they are allowed to see. Thanks for the quick clear. When I searched the net it suggested that method, so I thought clear did something else.

I then show the master data sheet with the relevant filter in place. They can then (using the form with a combo box) select the sub-filter they wish to see, which is all of their sites or any one individual site.

So in answer to your question. The setup which generates the list is called from the first sheet. It sets up the Combo Box. Then moves to the new sheet. The combo Box is not 'visbile' at this point and only becomes visible if they click on a cell (C4) as in above code. This then shows the form with the combo box on. When an item is selected or the form is cancelled (Red X) then the form is hidden. If a value was entered (i.e. it was not cancelled) then a filter is performed.

It all works fine and I can do multiple filters no problem. The issue only occurs when I cancel the form using (red x) Then next time I bring up the Combo box. The list is empty?

It does not go back to the set up code at any point during this time. Only if I exit this sheet and return to the main menu (Excel Sheet) and then select the set up sheet again does it go through this code again.

The code above is ALL the code that accesses the combobox or the form it is on.

**romperstomper** · 07-26-2011, 07:35 AM

Your problem is the fact that the form is being unloaded, not hidden, and your setup code is never run again to put the values back. To ensure it gets hidden use:


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
   If CloseMode = vbFormControlMenu Then
      Cancel = True
      Me.Hide
   End If
End Sub