Excel VBAListbox

[GRCArizona]

Hi - I'm populating a ListBox with the names of all Visible Sheets. I've got everything working fine except for 1 thing. I'd like to EXCLUDE the Activesheet from the list that populates the ListBox.

Any suggestions?

thx,

GRC

 

[shg]

What's the code you have so far?

 

[GRCArizona]

Private Sub PlanType_Change()

Dim AllTabs As Worksheet, firsttab As Worksheet

AvailableList.Clear '''''AvailableList is the 1st ListBox

Set firsttab = ActiveSheet

For Each AllTabs In ThisWorkbook.Sheets

If AllTabs.Visible = xlSheetVisible Then

AllTabs.Activate
If ActiveSheet.Range("A2").Value = PlanType.Value Then
AvailableList.AddItem AllTabs.Name
End If
End If
Next
firsttab.Activate

 

[shg]

Maybe ...

Private Sub PlanType_Change()
    Dim wks         As Worksheet
 
    AvailableList.Clear
 
    For Each wks In ThisWorkbook.Worksheets
        If Not wks Is Me Then
            If wks.Visible = xlSheetVisible Then
                If wks.Range("A2").Value = PlanType.Value Then
                    AvailableList.AddItem wks.Name
                End If
            End If
        End If
    Next
End Sub

 

[GRCArizona]

Thx for the code. It errored out and I saw that it needed an extra "End If" before the "Next", but it is still populating with the Active Sheet.

I was thinking about trying to use sth like:

If(And....xlSheetVisible AND Not Activesheet Then.... AddItem.

Not sure how this code would look though.

 

[shg]

I just created the sheet (again) and ran the code exactly as written, and it worked fine. I assumed that PlanType and AvailableList are ActiveX controls on the same sheet and that the code in in the worksheet module.

 

[GRCArizona]

I'm using a Command Button on a worksheet that opens up a UserForm. The 'Plan Type:' is a Combobox that sends the available worksheets over to the ListBox I'm trying to populate.

sorry for the confusion.

 

[shg]

EDIT: Change Me to ActiveSheet.

 

[GRCArizona]

In the User form as well.

 

[shg]

Change Me to ActiveSheet.