Combo Box fill

[Hal]

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.
--
Regards,
Tom Ogilvy


"Hal" <fsuTonkNoSpam@netscape.net> wrote in message
news:8316111A-DB03-4428-B3D5-AE60B44C89CC@microsoft.com...
> 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

[Hal]

Thanks once again Tom,

Your expertise is greatly appreciated!

Hal

"Tom Ogilvy" wrote:

> 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.
> --
> Regards,
> Tom Ogilvy
>
>
> "Hal" <fsuTonkNoSpam@netscape.net> wrote in message
> news:8316111A-DB03-4428-B3D5-AE60B44C89CC@microsoft.com...
> > 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
>
>
>