PDA

View Full Version : Solved: Listbox Value Writing to Form



John_Mc
05-22-2006, 11:44 PM
Hi All,

Basically, I have a form that users enter client data with. On this form are list boxes. They choose the Title field and select "Mr". VBA then writes what they enter onto the form, onto a worksheet.

Later, they can search using the form and recall the information currently held on the worksheet, back into a form. The list boxes (using code that Jindon very kindly supplied) autopopulate. So on the form the list box when it displays automatically shows "Mr" in the title field because that is what is currently held on the worksheet.

The user will then change, say, the telephone number and click save.

Now, what is should do is write all the same data to the same place in the worksheet. Except that only some of the list boxes are being seen by the VBA as having a value (others are seen as empty) so the empty ones are having values on the worksheet overwritten with blanks.

When I step through the code below, some of the listboxes show the value, while others are empty. The thing is, all the code looks the same as for other fields that are working. For example, Status has a value (occupied), but then using the same code, Tenure is empty (even though a value is on the sheet.:hairpull:


I know the code is untidy and less than efficient, but i'm reasonably new at this and I really need to get this sorted so if anyone can help, I'd really, really appreciate it.


Private Sub cmdGetData_Click()
'assign datatypes to specific codewords
Dim ChosenLocation As String, ChosenUnit As String, CombinedChoice As String
Dim Status As String, Tenure As String, Title1 As String, Suburb1 As String
Dim State1 As String, Title2 As String, State2 As String
Dim r As Range

CombinedChoice = lbUnit.Value & lbLocation.Value
'On Error Resume Next
With Sheets("Portfolio")
With .Range("d2", .Range("d" & Rows.Count).End(xlUp))
Set r = .Find(CombinedChoice, , , xlWhole)
Me.txtPostCode2.Text = r.Offset(, 29).Value
With Application
If r.Offset(, 4).Value = Empty Then
MsgBox "Please Update The Status Field"
GoTo Line1
Else
Me.lbStatus.ListIndex = .Match(r.Offset(, 4).Value, _
.Index(Me.lbStatus.List, 0, 1), 0) - 1
End If

Line1:
If r.Offset(, 5).Value = Empty Then
MsgBox "Please Update The TENURE Field"
GoTo Line2
Else
Me.lbTenure.ListIndex = .Match(r.Offset(, 5).Value, _
.Index(Me.lbTenure.List, 0, 1), 0) - 1
End If

Line2: If r.Offset(, 10).Value = Empty Then
MsgBox "Please Update The TITLE Field For Resident 1"
GoTo Line3
Else
Me.lbTitle1.ListIndex = .Match(r.Offset(, 10).Value, _
.Index(Me.lbTitle1.List, 0, 1), 0) - 1
End If

Line3:
If r.Offset(, 18).Value = Empty Then
MsgBox "Please update the STATE field for resident 1"
GoTo Line4
Else
Me.lbState1.ListIndex = .Match(r.Offset(, 18).Value, _
.Index(Me.lbState1.List, 0, 1), 0) - 1
End If

Line4:
If r.Offset(, 20).Value = Empty Then
MsgBox "Please update the TITLE field for resident 2"
GoTo Line5
Else
Me.lbTitle2.ListIndex = .Match(r.Offset(, 20).Value, _
.Index(Me.lbTitle2.List, 0, 1), 0) - 1
End If

Line5:
If r.Offset(, 28).Value = Empty Then
MsgBox "Please update the STATE field for resident 2"
GoTo Line6:
Else
Me.lbState2.ListIndex = .Match(r.Offset(, 28).Value, _
.Index(Me.lbState2.List, 0, 1), 0) - 1
End If

End With
End With
End With
Line6:
Exit Sub
End Sub

Private Sub UserForm_activate()
lbLocation.RowSource = "KeyVariables!A2:A40"
lbUnit.RowSource = "KeyVariables!B2:B100"
lbStatus.List = Array("Awaiting Upgrade", "Being Upgraded", "Demolition", _
"Early Occupied", "For Sale", "Invoiced", "Occupied", "Vacant", "On Hold", "Rented", _
"Settled", "WIP", "N/A", "")
lbTenure.List = Array("EC", "LTR", "PERM", "STR", "WW-LTR", "N/A")
lbTitle1.List = Array("Mr", "Mrs", "Miss", "Ms", "N/A")
lbTitle2.List = Array("Mr", "Mrs", "Miss", "Ms", "N/A")
lbState1.List = Array("SA", "QLD", "NSW", "VIC", "TAS", "NT", "WA", "N/A")
lbState2.List = Array("SA", "QLD", "NSW", "VIC", "TAS", "NT", "WA", "N/A")
End Sub



Edit by Lucas: line breaks added to keep code from running off page

John_Mc
05-22-2006, 11:47 PM
Oh, sorry, and this bit saves it to the worksheet
:blush



Private Sub cmdSave_Click()
Dim ChosenLocation As String, ChosenUnit As String, CombinedChoice As String
Dim Status As String, Tenure As String, Contribution As String
Dim TerminationDate As Date, EntryDate As Date, ContractType As Integer
Dim Title1 As String, Surname1 As String, FirstName1 As String
Dim DateofBirth1 As Date, ContactNo1_1 As String, ContactNo2_1 As String
Dim StreetAddress1 As String, Suburb1 As String, State1 As String
Dim Postcode1 As String, Title2 As String, Surname2 As String
Dim FirstName2 As String, DateofBirth2 As Date, ContactNo1_2 As String
Dim ContactNo2_2 As String, StreetAddress2 As String, Suburb2 As String
Dim State2 As String, Postcode2 As String, CurrentCondition As String
Dim CurrentValue As String, estimatedUpgradeCost As String, RepairStartDate As Date
Dim EstimatedDuration As Integer, ExpectedEndDate As String, ActualUpgradeCost As String
'set the name of the unit to find
CombinedChoice = lbUnit.Value & lbLocation.Value
Sheets("Portfolio").Activate
Range("d2").Activate

'start in the cell, if found set values, if not offset down and repeat
For i = 1 To 777
If ActiveCell.Value = CombinedChoice Then
'assing current spreadsheet values to a variable
Cells(i + 1, 8).Value = lbStatus.Value '*This will have "Occupied"
Cells(i + 1, 9).Value = lbTenure.Value '* This will be empty
Cells(i + 1, 14).Value = lbTitle1.Value
Cells(i + 1, 22).Value = lbState1.Value
Cells(i + 1, 24).Value = lbTitle2.Value
Cells(i + 1, 32).Value = lbState2.Value
i = 1001

Else: ActiveCell.Offset(1, 0).Activate
End If

Next i
End Sub

jindon
05-23-2006, 02:17 AM
hi

can you post your file with detailed instructions?

John_Mc
05-23-2006, 04:43 PM
Hi Jindon,

Please find an example attached.

On opening, a form appears. Choose 'Database Update'.

At the top of the next form:
Click 'Braemore Street' so that it is highlighted with a blue background.
Click 'Unit 2' so that it is highlighted with a blue background. You must choose unit 2, as i know the error occurs here as i just tested it.

Now click 'Get Data'. The information contained in the worksheet is pulled into the form. Notice that each of the list boxes has a value displayed and selected.

Now pull the form down slightly so that you can see row 3 on the worksheet behind the form. In column H is the status field (currently showing 'Occupied'). Without changing anything on the form, press save. You will notice that on the worksheet, the value for tenure (which was occupied) has now been overwritten with a blank cell - even though occupied was shown as highlighted on the form.

I'm not sure what the problem is as this doesn't happen everytime, just sometimes. Thanks for your help and if you need any more info, please just let me know.

Cheers,
John Mc

jindon
05-23-2006, 05:35 PM
John,

I've just got the file and did as instructed but all I've got is the series of message boxes saying "Update blah bla"....

Just make usre that I selected 'Braemore Street' and 2 from the combobox on top right, is it correct?

John_Mc
05-23-2006, 05:54 PM
Hi Jindon,

that is correct.

The error messages show the user that the form tried to get something from the worksheet and put it in the listbox - but that as nothing was in the cell, it couldn't put anything as the selected item from the listbox.

It was intended to ensure the user selects an option when none was previously entered.

However, as you can see on the worksheet, all the cells have a value selected, so the vba is not pulling the value into the listbox, which is causing the message.

If you select unit 2, Breamore street and click get data - you will get all those messages (though i didn't while i was testing it for upload to this site). Move the form and you'll see column H which has a value (e.g. occupied).

Now, after all the message boxes have disappeared, click save. you'll notice that the values in the cells have been overwritten with blanks - although it is highlighted on the form.

Cheers,
John

jindon
05-23-2006, 06:57 PM
OK, I see what you mean

I must do something else at the moment, so I'll come back to you this afternoon.

It is 11:00 a.m here in Tokyo

John_Mc
05-23-2006, 07:20 PM
Hi Jindon,

no worries, i'm just glad you're looking at it. It is 11 am also here in Adelaide, so i will be around to answer anything else you may need to know.

Cheers, John Mc

jindon
05-23-2006, 08:50 PM
I haven't throughtly watched the code yet though

try and see how it goes

Private Sub cmdSave_Click()
Dim ChosenLocation As String
Dim ChosenUnit As String
Dim CombinedChoice As String
Dim Status As String
Dim Tenure As String
Dim Contribution As String
Dim TerminationDate As Date
Dim EntryDate As Date
Dim ContractType As Integer
Dim Title1 As String
Dim Surname1 As String
Dim FirstName1 As String
Dim DateofBirth1 As Date
Dim ContactNo1_1 As String
Dim ContactNo2_1 As String
Dim StreetAddress1 As String
Dim Suburb1 As String
Dim State1 As String
Dim Postcode1 As String
Dim Title2 As String
Dim Surname2 As String
Dim FirstName2 As String
Dim DateofBirth2 As Date
Dim ContactNo1_2 As String
Dim ContactNo2_2 As String
Dim StreetAddress2 As String
Dim Suburb2 As String
Dim State2 As String
Dim Postcode2 As String
Dim CurrentCondition As String
Dim CurrentValue As String
Dim estimatedUpgradeCost As String
Dim RepairStartDate As Date
Dim EstimatedDuration As Integer
Dim r As Range
Dim ExpectedEndDate As String
Dim ActualUpgradeCost As String

'Contribution Field
If IsNumeric(txtContribution.Text) = False Then
If txtContribution <> "" Then
MsgBox "The Contribution Field Should Only Contain Numbers"
Exit Sub
Else
End If
Else
End If
'Contract Type Field
If IsNumeric(txtContractType.Text) = False Then
If txtContractType <> "" Then
MsgBox "The Contract Type Field Should Only Contain Numbers"
Exit Sub
Else
End If
Else
End If
'Entry Date Field
If IsDate(txtEntryDate.Text) = False Then
If txtEntryDate <> "" Then
MsgBox "The Entry Date Must Be In Format DD/MM/YYYY"
Exit Sub
Else
End If
Else
End If
'Termination Date Field
If IsDate(txtTerminationDate.Text) = False Then
If txtTerminationDate <> "" Then
MsgBox "The Termination Date Must Be In Format DD/MM/YYYY"
Exit Sub
Else
End If
Else
End If
'Date of Birth 1 Field
If IsDate(txtDateOfBirth1.Text) = False Then
If txtDateofbirth <> "" Then
MsgBox "The Date Of Birth (Person 1) Must Be In Format DD/MM/YYYY"
Exit Sub
Else
End If
Else
End If
'Date of Birth 2 Field
If IsDate(txtDateOfBirth2.Text) = False Then
If txtDateOfBirth2 <> "" Then
MsgBox "The Date Of Birth (Person 2) Must Be In Format DD/MM/YYYY"
Exit Sub
Else
End If
Else
End If
CombinedChoice = lbUnit.Value & lbLocation.Value
With Sheets("Portfolio")
Set r = .Range("d:d").Find(CombinedChoice, , , xlWhole)
If Not r Is Nothing Then
r.EntireRow.Copy _
Sheets("ChangesArchive").Range("a" & Rows.Count).End(xlUp).Offset(1)
If r.Row = 64999 Then _
MsgBox ("After this update, you need to transfer the data on the Changes" & _
"Archive sheet of date as the worksheet is getting full. Then clear the data" _
& "DO NOT DELETE THE HEADINGS")

'assing current spreadsheet values to a variable
x = r.Row
.Cells(x, 8).Value = Me.lbStatus.Value
.Cells(x, 9).Value = Me.lbTenure.Value
.Cells(x, 10).Value = Me.txtContribution.Value
.Cells(x, 11).Value = Me.txtEntryDate.Value
.Cells(x, 12).Value = Me.txtContractType.Value
.Cells(x, 13).Value = Me.txtTerminationDate.Value
.Cells(x, 14).Value = Me.lbTitle1.Value
.Cells(x, 16).Value = Me.txtSurname1.Value
.Cells(x, 15).Value = Me.txtFirstName1.Value
.Cells(x, 17).Value = Me.txtDateOfBirth1.Value
.Cells(x, 18).Value = Me.txtContactNo1_1.Value
.Cells(x, 19).Value = Me.txtContactNo2_1.Value
.Cells(x, 20).Value = Me.txtStreetAddress1.Value
.Cells(x, 21).Value = Me.txtSuburb1.Value
.Cells(x, 22).Value = Me.lbState1.Value
.Cells(x, 23).Value = Me.txtPostCode1.Value
.Cells(x, 24).Value = Me.lbTitle2.Value
.Cells(x, 26).Value = Me.txtSurname2.Value
.Cells(x, 25).Value = Me.txtFirstName2.Value
.Cells(x, 27).Value = Me.txtDateOfBirth2.Value
.Cells(x, 28).Value = Me.me.txtContactNo1_2.Value
.Cells(x, 29).Value = Me.txtContactNo2_2.Value
.Cells(x, 30).Value = Me.txtStreetAddress2.Value
.Cells(x, 31).Value = Me.txtSuburb2.Value
.Cells(x, 32).Value = Me.lbState2.Value
.Cells(x, 33).Value = Me.txtPostCode2.Value
End If
End With
'ActiveWorkbook.Save
MsgBox "Record Saved"

End Sub

John_Mc
05-23-2006, 09:42 PM
Hi Jindon,

I'm afraid that doesn't seem to work. It still populates the cell with a blank value.

Is it the way that excel is populating the listbox in the first place? It seems that when the listbox is populated correctly, the save bit works, but getting it to select the the value and put it in the list could be the issue.

I use the cmdGetData_click sub for that.

I think the saving part works well enough - although it is kind of embarasing what takes me 20 lines of code with loops in it, you do in 2 lines! :bow:

Cheers, John

jindon
05-23-2006, 10:33 PM
I think I found it.

Don't use userform_activate event since you are opening the form modeless.

use userform_initialize event...

will post the code after clean up....

Private Sub UserForm_Initialize()
lbLocation.RowSource = "KeyVariables!A2:A40"
lbUnit.RowSource = "KeyVariables!B2:B100"
lbStatus.List = Array("Awaiting Upgrade", "Being Upgraded", "Demolition", "Early Occupied", "For Sale", "Invoiced", "Occupied", "Vacant", "On Hold", "Rented", "Settled", "WIP", "N/A", "")
lbTenure.List = Array("EC", "LTR", "PERM", "STR", "WW-LTR", "N/A")
lbTitle1.List = Array("Mr", "Mrs", "Miss", "Ms", "N/A")
lbTitle2.List = Array("Mr", "Mrs", "Miss", "Ms", "N/A")
lbState1.List = Array("SA", "QLD", "NSW", "VIC", "TAS", "NT", "WA", "N/A")
lbState2.List = Array("SA", "QLD", "NSW", "VIC", "TAS", "NT", "WA", "N/A")
End Sub

Dim myTB
myTB = Array("txtContribution", "txtContractType", "txtEntryDate", _
"txtTerminationDate", "txtDateOfBirth1", "txtDateOfBirth2")
For Each e In myTB
If e = "txtContribution" Or e = "textContractType" Then
If Not IsNumeric(e) Then _
Msg = Msg & Replace(e,"txt","") & " Field Should Only Contain Numbers" & vbLf
Else
If Not IsDate(e) Then _
Msg = Msg & Replace(e, "txt", "") & " Must Be In Format DD/MM/YYYY" & vbLf
End If
Next
If Len(Msg) Then MsgBox Msg: Exit Sub

the code should be replaced with number of IF statement for errors...

John_Mc
05-23-2006, 11:16 PM
I'm not sure i fully understand - is this the 'cleaned up' code that i need to use, or are you still working on some part?

I know where to place the userform_initialise event code, but where do i place the other code (the one with the if statements)

Does it go within the cmdGetDate routine, or is it a seperate routine?

Cheers,
John

jindon
05-23-2006, 11:23 PM
No

It substitute for this part

'Contribution Field
If IsNumeric(txtContribution.Text) = False Then
If txtContribution <> "" Then
MsgBox "The Contribution Field Should Only Contain Numbers"
Exit Sub
Else
End If
Else
End If
'Contract Type Field
If IsNumeric(txtContractType.Text) = False Then
If txtContractType <> "" Then MsgBox "The Contract Type Field Should Only Contain Numbers" Exit Sub
Else
End If
Else
End If
'Entry Date Field
If IsDate(txtEntryDate.Text) = False Then
If txtEntryDate <> "" Then
MsgBox "The Entry Date Must Be In Format DD/MM/YYYY"
Exit Sub
Else
End If
Else
End If
'Termination Date Field
If IsDate(txtTerminationDate.Text) = False Then
If txtTerminationDate <> "" Then
MsgBox "The Termination Date Must Be In Format DD/MM/YYYY"
Exit Sub
Else
End If
Else
End If
'Date of Birth 1 Field
If IsDate(txtDateOfBirth1.Text) = False Then
If txtDateofbirth <> "" Then
MsgBox "The Date Of Birth (Person 1) Must Be In Format DD/MM/YYYY"
Exit Sub
Else
End If
Else
End If
'Date of Birth 2 Field
If IsDate(txtDateOfBirth2.Text) = False Then
If txtDateOfBirth2 <> "" Then
MsgBox "The Date Of Birth (Person 2) Must Be In Format DD/MM/YYYY"
Exit Sub
Else
End If
Else
End If

John_Mc
05-24-2006, 12:36 AM
Hi Jindon,

I've tested it quite a lot now and think your point about the initialise might be on the right track.

When the form first loads, and i click get data, then immediately click save, it still overwrites it with a blank.

BUT, once I have individually clicked in each listbox (even though it is already highlighted) it works. Futher, when i then click get data for another unit and immediately click save (without clicking in the boxes for a second time) it works.

It seems like it wants each listbox to be clicked once, after the form has been loaded, before it will work. If a user doesn't remember to click each box at the start, however, then it will overwrite with blanks.

Does this make any sense to you? Or help at all?

John Mc

jindon
05-24-2006, 12:38 AM
OK

That make sanse to me and I'll look at the code.....

jindon
05-24-2006, 12:58 AM
since you are using listbox, change every line for getdata event

If r.Offset(, 4).Value = Empty Then
MsgBox "Please Update The Status Field"
GoTo Line1
Else
Me.lbStatus.Value = r.Offset(, 4).Value
End If

John_Mc
05-24-2006, 04:17 PM
Hi Jindon,

it is still overwriting with blanks. Thinking about it last night, it seems that the same fields are always the problem - tenure, state1, state2.

The other fields, Status,Title1,Title2 seem to work. I couldn't see anything common to them, but it might be a clue. Is there anything else I could try?

Cheers,
John

jindon
05-24-2006, 05:16 PM
Good morning

I haven't got enought time yesterday to look at your entire code.

I'll have a good look and come back to you.

jindon
05-24-2006, 07:58 PM
I don't think this is the best way, but try
add one line on the top of the form module

Private x as long


Private Sub cmdSave_Click()
Dim myCtl, myMsg
myCtl = Array("txtContribution", "txtContractType", "txtEntryDate", "txtTerminationDate", _
"txtDateOfBirth1", "txtDateOfBirth2")
For i = 0 To UBound(myCtl)
If i < 2 Then
If Not IsNumeric(Me.Controls(myCtl(i))) Then
MsgBox "The " & Replace(myCtl(i), "txt", "") & " Field Should Only Contain Numbers"
Exit Sub
End If
Else
If Not IsDate(Me.Controls(myCtl(i))) Then
MsgBox "The " & Replace(myCtl(i), "txt", "") & " Must Be In Format DD/MM/YYYY"
Exit Sub
End If
End If
Next
Dim ctrl As Control
For Each ctrl In Me.Controls
If TypeName(ctrl) = "ListBox" Then
ctrl.SetFocus
SendKeys "{Enter}"
DoEvents
End If
Next
With Sheets("Portfolio")
.Rows(x).Copy Sheets("ChangesArchive").Range("a" & Rows.Count).End(xlUp).Offset(1)

If Sheets("ChangesArchive").Range("a" & Rows.Count).End(xlUp).Row = 64999 Then
MsgBox ("After this update, you need to transfer the data on the Changes Archive sheet of date as the worksheet is getting full. Then clear the data DO NOT DELETE THE HEADINGS")
End If

'Now search for the chosen location and unit number and then
'put the current values in the userform onto the RFU Portfolio sheet
'assing current spreadsheet values to a variable
.Cells(x, 8).Value = lbStatus.Value
MsgBox Me.lbTenure.Value & " : " & Me.lbState1.Value
.Cells(x, 9).Value = lbTenure.Value
.Cells(x, 10).Value = txtContribution.Value
.Cells(x, 11).Value = txtEntryDate.Value
.Cells(x, 12).Value = txtContractType.Value
.Cells(x, 13).Value = txtTerminationDate.Value
.Cells(x, 14).Value = lbTitle1.Value
.Cells(x, 16).Value = txtSurname1.Value
.Cells(x, 15).Value = txtFirstName1.Value
.Cells(x, 17).Value = txtDateOfBirth1.Value
.Cells(x, 18).Value = txtContactNo1_1.Value
.Cells(x, 19).Value = txtContactNo2_1.Value
.Cells(x, 20).Value = txtStreetAddress1.Value
.Cells(x, 21).Value = txtSuburb1.Value
.Cells(x, 22).Value = lbState1.Value
.Cells(x, 23).Value = txtPostCode1.Value
.Cells(x, 24).Value = lbTitle2.Value
.Cells(x, 26).Value = txtSurname2.Value
.Cells(x, 25).Value = txtFirstName2.Value
.Cells(x, 27).Value = txtDateOfBirth2.Value
.Cells(x, 28).Value = txtContactNo1_2.Value
.Cells(x, 29).Value = txtContactNo2_2.Value
.Cells(x, 30).Value = txtStreetAddress2.Value
.Cells(x, 31).Value = txtSuburb2.Value
.Cells(x, 32).Value = lbState2.Value
.Cells(x, 33).Value = txtPostCode2.Value
End With
'ActiveWorkbook.Save
MsgBox "Record Saved"

End Sub

Private Sub cmdGetData_Click()
Dim CombinedChoice As String
Dim r As Range
CombinedChoice = lbUnit.Value & lbLocation.Value
With Sheets("Portfolio")
With .Range("d2", .Range("d" & Rows.Count).End(xlUp))
Set r = .Find(CombinedChoice, , , xlWhole)
If r Is Nothing Then Exit Sub
End With
x = r.Row
Me.txtContribution.Text = Format(.Cells(x, "j").Value, "$#,##0")
Me.txtEntryDate.Text = .Cells(x, "k").Value
Me.txtContractType.Text = .Cells(x, "l").Value
Me.txtTerminationDate.Text = .Cells(x, "m").Value
Me.txtFirstName1.Text = .Cells(x, "o").Value
Me.txtSurname1.Text = .Cells(x, "p").Value
Me.txtDateOfBirth1.Text = .Cells(x, "q").Value
Me.txtContactNo1_1.Text = r.Offset(, 14).Value
Me.txtContactNo2_1.Text = r.Offset(, 15).Value
Me.txtStreetAddress1.Text = r.Offset(, 16).Value
Me.txtSuburb1.Text = .Cells(x, "r").Value
Me.txtPostCode1.Text = .Cells(x, "s").Value
Me.txtFirstName2.Text = .Cells(x, "y").Value
Me.txtSurname2.Text = .Cells(x, "z").Value
Me.txtDateOfBirth2.Text = .Cells(x, "aa").Value
Me.txtContactNo1_2.Text = .Cells(x, "ab").Value
Me.txtContactNo2_2.Text = .Cells(x, "ac").Value
Me.txtStreetAddress2.Text = .Cells(x, "ad").Value
Me.txtSuburb2.Text = .Cells(x, "ae").Value
Me.txtPostCode2.Text = .Cells(x, "ag").Value
Dim myCol, myMsg, myCtl
myCol = Array("h", "i", "n", "v", "x", "af")
myMsg = Array("Status Field", "TENURE Field", "Title field for Rsident 1", _
"State Field for Resident 1", "Title field for Resident 2", _
"State Field for Resident 2")
myCtl = Array("lbStatus", "lbTENURE", "lbTitle1", "lbState1", "lbTitle2", "lbState2")
For i = 0 To UBound(myCol)
If IsEmpty(.Cells(x, myCol(i))) Then
MsgBox "Please Update The " & myMsg(i)
Else
Me.Controls(myCtl(i)).Value = .Cells(x, myCol(i)).Value
End If
Next
End With
End Sub

You need to adjust other subs like Exit event
You need to remember textbox value ia always String type so,
if you format the box after exit like $1,000, it is not a numeric value....

John_Mc
05-24-2006, 10:55 PM
Hi Jindon,

I've copied and pasted your code and tested it a fair bit and .....drum roll please.....so far it is looking great.

Sincere thanks for all the time you spent on it. This project has quite a lot of other related things tied to it, so it is actually more important than perhaps it might seem at first.

If I ever make it to Tokyo, which i've been trying to do for years, I owe you a couple of these...:beerchug:

John