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.
And here's the code for the Select Customer dialog box
Any insight would be appreciated!
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.
Code:
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
Code:
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!