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
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