Combobox userform help please

C

Chris

I have a userform for entering and editing data that has a combox on it
so the user can jump to a specific record to edit. I also have a "next"
button to go to the next record and a "previous" button to back a
record. If I leave the combobox blank in it's inital state I can change
data in the userform, hit the "next button" and the changes show up
correctly to the worksheet. However, once I use the combobox to jump to
a record and try make a change and hit the next button, instead of the
change showing up correctly on the worksheet, my record gets
overwritten and it messes up my worksheet.

Here is my code if anyone can advise on how to correct the above
problem.


Private Sub cmdNext_Click()
' Save form contents before changing rows:
SaveRow
' Increment row number:
lCurrentRow = lCurrentRow + 1
' Show contents of row in the form:
LoadRow
End Sub

Private Sub ComboBox1_Change()
Set rng = Range(ComboBox1.RowSource)
txtReqNum.Text = rng(ComboBox1.ListIndex + 1)(1, 1)
txtDateOpen.Text = rng(ComboBox1.ListIndex + 1)(1, 2)
txtType.Text = rng(ComboBox1.ListIndex + 1)(1, 4)
txtPriority.Text = rng(ComboBox1.ListIndex + 1)(1, 5)
txtTitle.Text = rng(ComboBox1.ListIndex + 1)(1, 6)
txtGrd.Text = rng(ComboBox1.ListIndex + 1)(1, 7)
txtRange.Text = rng(ComboBox1.ListIndex + 1)(1, 8)
txtExpected.Text = rng(ComboBox1.ListIndex + 1)(1, 9)
txtNR.Text = rng(ComboBox1.ListIndex + 1)(1, 11)
txtManager.Text = rng(ComboBox1.ListIndex + 1)(1, 12)
txtRecr.Text = rng(ComboBox1.ListIndex + 1)(1, 13)
txtStatus.Text = rng(ComboBox1.ListIndex + 1)(1, 14)
txtCandidate.Text = rng(ComboBox1.ListIndex + 1)(1, 15)
End Sub

Private Sub UserForm_Activate()
' Read initial values from Row 1:
lCurrentRow = Range("My_Range").Cells(4, 1).Row
LoadRow
ComboBox1.RowSource = Range(Range("My_Range").Offset(4), _
Range("My_Range").Offset(4).End(xlDown)).Address

End Sub
 
C

Charles Chickering

Chris, please post the LoadRow and SaveRow subroutines, I believe they are
the answer.
 
C

Chris

Private Sub LoadRow()
txtReqNum.Text = Cells(lCurrentRow, 1).Value
txtDateOpen.Text = Cells(lCurrentRow, 2).Value
txtType.Text = Cells(lCurrentRow, 4).Value
txtPriority.Text = Cells(lCurrentRow, 5).Value
txtTitle.Text = Cells(lCurrentRow, 6).Value
txtGrd.Text = Cells(lCurrentRow, 7).Value
txtRange.Text = Cells(lCurrentRow, 8).Value
txtExpected.Text = Cells(lCurrentRow, 9).Value
txtNR.Text = Cells(lCurrentRow, 11).Value
txtManager.Text = Cells(lCurrentRow, 12).Value
txtRecr.Text = Cells(lCurrentRow, 13).Value
txtStatus.Text = Cells(lCurrentRow, 14).Value
txtCandidate.Text = Cells(lCurrentRow, 15).Value
End Sub

Private Sub SaveRow()
Cells(lCurrentRow, 1).Value = txtReqNum.Text
Cells(lCurrentRow, 2).Value = txtDateOpen.Text
Cells(lCurrentRow, 4).Value = txtType.Text
Cells(lCurrentRow, 5).Value = txtPriority.Text
Cells(lCurrentRow, 6).Value = txtTitle.Text
Cells(lCurrentRow, 7).Value = txtGrd.Text
Cells(lCurrentRow, 8).Value = txtRange.Text
Cells(lCurrentRow, 9).Value = txtExpected.Text
Cells(lCurrentRow, 11).Value = txtNR.Text
Cells(lCurrentRow, 12).Value = txtManager.Text
Cells(lCurrentRow, 13).Value = txtRecr.Text
Cells(lCurrentRow, 14).Value = txtStatus.Text
Cells(lCurrentRow, 15).Value = txtCandidate.Text
End Sub
 
C

Charles Chickering

I'm not seeing it right off, if you would like you can send me a copy of your
workbook to (e-mail address removed) and I'll see if I can debug it.
 
C

Chris

I sent it from an RHI email address.

Charles said:
I'm not seeing it right off, if you would like you can send me a copy of your
workbook to (e-mail address removed) and I'll see if I can debug it.
 
C

Charles Chickering

It's fixed and sent back to you, I described the problems in detail in the
email.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top