ComboBox Change Event interferes

[SkipVought]

On Sheet1 I have a Combox. I make it Visible or not on certain Worksheet1 Selection Change events.

On Sheet2 I have several lists that are assigned to the ComboBox.ListFillRange on the Worksheet_SelectionChange event for particular columns.

I have a procedure that works for Sheet2 Named Ranges (that are used for the ComboBox.ListFillRange) to rename and sort each one…

Sub ReformatVariablesLists()
    Dim Name1
    Application.DisplayAlerts = False
    For Each Name1 In Names
        With Name1
            Select Case .RefersToRange.Worksheet.Name
                Case "Variables"
                'rename each range and sort
                    Sheets(.RefersToRange.Worksheet.Name).Activate
                    Range(.Name).CurrentRegion.Select
                    With Selection
                        .CreateNames _
                            Top:=True, Left:=False, Bottom:=False, Right:=False
                        .Sort Key1:=Range(Name1.Name), Order1:=xlAscending, Header:=xlYes, _
                            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
                    End With
            End Select
        End With
    Next
End Sub

Here's what is happening. If any of my lists change, the…

Private Sub ComboBox1_Change()
    With ActiveCell
        .Value = ComboBox1.Text
    End With
    ThisWorkbook.ObjectsInvisible
End Sub

is executing and since the ActiveCell is the Heading Value on my second list, the value in ComboBox.Text is writtin into that cell.
The only thing that I can figure out is that, changing the Range Sheet2, is triggering the Private Sub ComboBox_Change event on Sheet1. I have tried to set the ComboBox.ListFillRange property to Empty or "", but That gives me a Run Time error.

Any ideas on how to decouple this Sheet1 event from a Sheet2 event?
Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

I can trap the error with the following added Exit Sub...

BUT, what if my Sheet name changes

Private Sub ComboBox1_Change()
    With ActiveCell

If .Worksheet.Name <> &quot;Sheet1&quot; Then Exit Sub

        .Value = ComboBox1.Text
    End With
    ThisWorkbook.ObjectsInvisible
End Sub

:-( Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

I think that I got an answer. I used this code since my &quot;Variables&quot; sheet name will not change...

Private Sub ComboBox1_Change()
    With ActiveCell
        If .Worksheet.Name = "Variables" Then Exit Sub
        .Value = ComboBox1.Text
    End With
    ThisWorkbook.ObjectsInvisible
End Sub

Thanx to anyone who gave it some thought Skip,
metzgsk@voughtaircraft.com