VBA Project Indicator w/ Method 'List' of object '_CommandBarComboBox' failed question ...

[Pinaceous]

Hi All,

I'm trying to use:

Private Sub CommandButton1_Click()

              UserForm1.Show

End Sub

with my already existing sheet with these codes on them:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
 
    On Error GoTo Whoa
    
    Call PreserveFormats(Target)
    Call CapitalizeColumnC(Target)
    Call CapitalizeColumnD(Target)
    Call CapitalizeColumnE(Target)
                
LetsContinue:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
    
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
        
End Sub
Private Sub PreserveFormats(ByVal Target As Range)
    Dim UndoList As String
 
    '~~> Get the undo List to capture the last action performed by user
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
 
    '~~> Check if the last action was not a paste nor an autofill
    If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" Then Exit Sub
 
    '~~> Undo the paste that the user did but we are not clearing the
    '~~> clipboard so the copied data is still in memory
    Application.Undo
 
    If UndoList = "Auto Fill" Then Selection.Copy
 
    '~~> Do a pastespecial to preserve formats
    On Error Resume Next
    '~~> Handle text data copied from a website
    Target.Select
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, _
    DisplayAsIcon:=False
 
    Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    On Error GoTo 0
 
    '~~> Retain selection of the pasted data
    Union(Target, Selection).Select
 End Sub
Private Sub CapitalizeColumnC(ByVal Target As Range)
    Dim rInt As Range
    Dim rCell As Range
    Dim tCell As Range
    Dim C As Range
    
    If Not Intersect(Target, Range("C11:C180")) Is Nothing Then
        For Each C In Intersect(Target, Range("C11:C180"))
            C.Value = UCase(C.Value)
        Next C
          
    End If
    
End Sub
Private Sub CapitalizeColumnD(ByVal Target As Range)
    Dim rInt As Range
    Dim rCell As Range
    Dim tCell As Range
    Dim C As Range
    If Not Intersect(Target, Range("D11:D180")) Is Nothing Then
        For Each C In Intersect(Target, Range("D11:D180"))
            C.Value = UCase(C.Value)
        Next C
          
    End If
    
End Sub
Private Sub CapitalizeColumnE(ByVal Target As Range)
    Dim rInt As Range
    Dim rCell As Range
    Dim tCell As Range
    Dim C As Range
    If Not Intersect(Target, Range("E11:E180")) Is Nothing Then
        For Each C In Intersect(Target, Range("E11:E180"))
            C.Value = UCase(C.Value)
        Next C
          
    End If
    
End Sub
Private Sub Worksheet_Changed(ByVal Target As Range)
If Selection.Count > 1 Then Exit Sub
Cells((Target.Row) + 1, 6).Select
End Sub

Where I'm getting a

Method 'List' of object '_CommandBarComboBox' failed

Does any one know how I can write this into my existing code without this 'List of object' popping up??

I'm totally stumped beyond belief and I have a feeling the answer is right in front of me on this one!


Many thanks in advance!

Pinaceous

 

[AlphaFrog]

The code you posted is in your Worksheet code module. You get the error when you load the UserForm. I think the problem is with the UserForm's code that is located in its' code module.