VBA Combo Box Initialization Quirk

[GregN]

I'm sure the answer to this is simple, but for the life of me, I can't figure it out...

I'm calling a userform (called "ContactInfo") from another user form (called "SelectCustomer"). The idea is that the operator selects a customer from a list on the first userform and then clicks on the 'contact' button that brings up the second box where the operator can select (eventually I'm going to add functions to add, remove or edit) a contact from the list. My way of doing this has been to define a named ranged, called "Contacts".

So, when the operator clicks on the 'Contact' button, the macro dumps the index of the customer into cell AA1, on the worksheet "Lookups". AB1 and AC1 have formulas that pull out the start and end points of the 'contact' that are associated with this customer. It then uses those two numbers to change the named range to those numbers.

This stuff works great.... sort of...

The problem is that when the 'Select Contact' dialog box pops up, it retains the original list of contacts. When that is closed, and then opened again, the combo box updates.

Anyways, here's the code...
This is the code behind the 'ContactName' button.

Private Sub ContactName_Click()
    'clicking this will bring up the contact select/edit dialog box
    'the operator is presented with a list of contacts and asked
    'to select one.  That selection is transfered to the main data
    'base.  Five buttons, 'Select', 'Edit', 'Delete', 'Add' and 'Cancel'
    
    Dim x As Integer
    Dim y As Integer
    
    'check to see if a customer is selected
    If Customers.ListIndex >= 0 Then
        'put the current customer index into Lookups!AA1
        ThisWorkbook.Sheets("Lookups").Range("aa1").Value = Customers.ListIndex + 1
        
        ContactInfo.Show
        Unload ContactInfo
    End If
End Sub

And here's the code for the Select Customer dialog box

Private Sub Userform_Activate()
    Call InitializeForm
    
End Sub

Private Sub CancelAddContact_Click()
    ContactInfo.Hide
    Unload ContactInfo
    
End Sub

Private Sub InitializeForm()
    Dim x As Integer
    Dim y As Integer
    
    x = ThisWorkbook.Sheets("Lookups").Range("ab1").Value
    y = ThisWorkbook.Sheets("Lookups").Range("ac1").Value
    
    'MsgBox ("c28r" & x & ":c28r" & y)
    
    With ActiveWorkbook.Names("Contacts")
        .Name = "Contacts"
        .RefersToR1C1 = "=Lookups!R" & x & "C28:R" & y & "C28"
        .Comment = ""
    End With

End Sub

Any insight would be appreciated!

 

[GregN]

I think I may have solved my own problem... I think the act of writing the problem down really focuses the mind...

Anyways, for anybody interested... here's what I change;

From this...

Private Sub ContactName_Click()
    'clicking this will bring up the contact select/edit dialog box
    'the operator is presented with a list of contacts and asked
    'to select one.  That selection is transfered to the main data
    'base.  Five buttons, 'Select', 'Edit', 'Delete', 'Add' and 'Cancel'
    
    Dim x As Integer
    Dim y As Integer
    
    'check to see if a customer is selected
    If Customers.ListIndex >= 0 Then
        'put the current customer index into Lookups!AA1
        ThisWorkbook.Sheets("Lookups").Range("aa1").Value = Customers.ListIndex + 1
        
        ContactInfo.Show
        Unload ContactInfo
    End If
End Sub

to this...

Private Sub ContactName_Click()
    'clicking this will bring up the contact select/edit dialog box
    'the operator is presented with a list of contacts and asked
    'to select one.  That selection is transfered to the main data
    'base.  Five buttons, 'Select', 'Edit', 'Delete', 'Add' and 'Cancel'
    
    Dim x As Integer
    Dim y As Integer
    
    'check to see if a customer is selected
    If Customers.ListIndex >= 0 Then
        'put the current customer index into Lookups!AA1
        ThisWorkbook.Sheets("Lookups").Range("aa1").Value = Customers.ListIndex + 1
        
        Unload ContactInfo
        ContactInfo.Show
    End If
End Sub