PDA

View Full Version : combobox update



Cam
09-06-2009, 01:00 AM
I have userform with 5 comboboxes that are linked to rows 14-18 on multiple worksheets with values "Yes" and "No".
If the user selects the first combobox for instance and choose "Yes" for instance, then decides they made a mistake after clicking the Add button, from which a vbYesNoCancel dialog appears and the "No" is clicked, it returns the combobox to "", which I want, but it doubles and depending on the amount of times you repeat the senario, triples the "Yes" "No" values in the combobox... How do I get it to refresh back to original?

This is my Activate senario

Private Sub UserForm_Activate()
' Read initial values from Row 2:
With cboTester1
.AddItem "Yes"
.AddItem "No"
End With
cboTester1.Value = cboTester1.Value
With cboTester2
.AddItem "Yes"
.AddItem "No"
End With
cboTester2.Value = cboTester2.Value
With cboTester3
.AddItem "Yes"
.AddItem "No"
End With
cboTester3.Value = cboTester3.Value
With cboTester4
.AddItem "Yes"
.AddItem "No"
End With
cboTester4.Value = cboTester4.Value
With cboTester5
.AddItem "Yes"
.AddItem "No"
End With
cboTester5.Value = cboTester5.Value
lCurrentRow = 2
LoadRow
ScrollBar1_Change
End Sub


This is my add senario

Private Sub cmdAdd_Click()
' Call the do you really want to message
YesNoCancelMsg

' Save form contents before changing rows:

SaveRow
' Set current row to first empty row, i.e. one row after
' the last row currently in use:
If Cells(1, 1).Value = "" Then
lCurrentRow = lCurrentRow ' (list is empty - start in row 2)
Else
lCurrentRow = ActiveSheet.UsedRange.Rows.Count + 1
End If
Cells(lCurrentRow, 14).Value = cboTester1.Value
Cells(lCurrentRow, 15).Value = cboTester2.Value
Cells(lCurrentRow, 16).Value = cboTester3.Value
Cells(lCurrentRow, 17).Value = cboTester4.Value
Cells(lCurrentRow, 18).Value = cboTester5.Value
LoadRow
SaveRow
UserForm_Activate
End Sub

D_Rennie
09-06-2009, 08:54 AM
Hello Cam.

The problem is with UserForm_Activate. There is no need to run that event again. When you run this it puts the extra yes/no into the combobox's.

also the msgbox.
probley better of using somethink likedim msgAns as string
msgAns = msgbox ("continue",vbyesnocalcle)
if msgAns = vbyes then...
if msgbox= vbno then...

have a good day.