Problem with Userfom Values :(

[d34n0]

Good Morning all,

I have a set of userform controls that store their values on a worksheet so when the workbook is saved and re opened the userform controls retain the values from when they were last used by.

I have for example a set of controls for recording a person’s contact details (name tel, email etc). The name field is a combobox and the telephone and email fields are textbox types. The system works fine where I just type all the information in but I was trying to be clever and introduce a lookup table for the name field. If the name is already in the database the rest of the information will populate itself. This also works fine but for the following problem...

Where the person’s name is in the database but the email address is wrong the userform populates itself when I choose the person’s name from the combobox but obviously the email address field is wrong. When I change the email address the change is recorded on the worksheet that saves the information but when the workbook is closed and re opened the userform changes the email address again to the email on the database during the userform initialise process.


Private Sub UserForm_Initialize()
cboCustNam.Value = Sheets("Data").Range("D9").Value
txtCustTel.Value = Sheets("Data").Range("D10").Value
txtCustMob.Value = Sheets("Data").Range("D11").Value
txtCustEmail.Value = Sheets("Data").Range("D12").Value
End Sub


Private Sub cboCustSecContNam_Change()
Dim CSName As String
Dim CSTel As String
Dim CSMob As String
Dim CSEml As String
On Error Resume Next
CSName = cboCustSecContNam.Value
CSTel = Application.WorksheetFunction.VLookup(CSName, Sheets("Contacts").Range("A2:E25"), 3, 0)
CSMob = Application.WorksheetFunction.VLookup(CSName, Sheets("Contacts").Range("A2:E25"), 4, 0)
CSEml = Application.WorksheetFunction.VLookup(CSName, Sheets("Contacts").Range("A2:E25"), 5, 0)

Sheets("Data").Range("D9").Value = CSName
Sheets("Data").Range("D10").Value = CSTel
Sheets("Data").Range("D11").Value = CSMob
Sheets("Data").Range("D12").Value = CSEml
txtCustSecContTel.Value = CSTel
txtCustSecContMob.Value = CSMob
txtCustSecContEmail.Value = CSEml
On Error GoTo 0
End Sub

Problem seems to be that when the userform is initialised and the values on the spreadsheet are given to the text and combo boxes this triggers them to carry out the code associated with a change and then the loop begins.

Any ideas how I can overcome this?

Many Thanks
D34no

 

[Andrew Poulsom]

Look here:

http://www.cpearson.com/Excel/SuppressChangeInForms.htm

 

[d34n0]

Thank you Andrew