-
combobox update
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
[VBA]
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
[/VBA]
This is my add senario
[VBA]
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
[/VBA]
-
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 like[VBA]dim msgAns as string
msgAns = msgbox ("continue",vbyesnocalcle)
if msgAns = vbyes then...
if msgbox= vbno then...[/VBA]
have a good day.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules