Combo Box fill

[Guest]

Hello all,

The UserForm_Initialize sub populates the first combo box. The
cbSPCsheets_Change sub fills the second combo box.

I would like to populate this combo box with all but the sheet seleted in
the first combo box. Is this accomplished by use of the items index number?
If so, would it be better to delete that item in the second combo box or can
it be passed by at fill time?

I guess another option would be to add a check to determine if the same item
is selected in both combo boxes and prompt to make another selection in box
two.

You thoughts and solutions. . .

Hal



Option Explicit

Private Sub UserForm_Initialize()
' Fill cbSPCsheets
Dim cSheets As Integer
Dim i As Integer
cSheets = Sheets.Count
cbSPCsheets.Clear
For i = 1 To cSheets
cbSPCsheets.AddItem Sheets(i).Name
Next
End Sub

Private Sub cbSPCsheets_Change()
' Fill cbDIRsheets
Dim cSheets As Integer
Dim i As Integer
lblDIRlisting.Enabled = True
cbDIRsheets.Enabled = True
cSheets = Sheets.Count
cbDIRsheets.Clear
For i = 1 To cSheets
cbDIRsheets.AddItem Sheets(i).Name
Next
End Sub

Private Sub cbDIRsheets_Change()
cmbCompare.Enabled = True
End Sub

Private Sub cmbCancel_Click()
Unload frmCompareSPCtoDIR
End Sub

 

[Tom Ogilvy]

For i = 1 To cSheets
if Sheets(i).Name <> cbSPCsheets.Value then
cbDIRsheets.AddItem Sheets(i).Name
end if
Next

If the sheet isn't in the choices, then you don't need to worry about a
conflict.

 

[Guest]

Thanks once again Tom,

Your expertise is greatly appreciated!

Hal