Help with Code

[gheyman]

I moved the referenced table on the sheet from cell A3 to B3 (moved it over one column). Now this code does not work. I though I adjusted it correctly but apparently I did not. Nothing happens - no errors either

Sub SelectProposal()

'*Clear Table: Selected_Proposal

    With Worksheets("Selected Proposal").ListObjects("Selected_Proposal")
    .Range.AutoFilter
    On Error Resume Next
    .DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
    
        If .ListColumns.Count > 1 Then
            .DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
        Else
    
            With .DataBodyRange.Cells(1)
        If Not .HasFormula Then .ClearContents
        End With
    
        End If
    
    End With
    
'*Move the Selected Proposal Data to the Selected Proposal Table

    Dim sh As Worksheet
    Dim tblPropList As ListObject
    Dim lastRow As ListRow
    Dim SCR As Variant
    Dim fnd As Variant
    Dim Lr As Long
    
    Set sh = Sheet1
    Set tblPropList = sh.ListObjects("Proposal_List")

    Lr = Cells(Rows.Count, "B").End(xlUp).Row

If Intersect(ActiveCell, Range("B3:M" & Lr)) Is Nothing Then
    MsgBox "You must select a cell within the applicable Proposal Row.  Select a valid Cell"
    Exit Sub
End If


Sheets("Proposal Selection").Range("B" & ActiveCell.Row & ":" & "M" & ActiveCell.Row).Copy
Sheets("Selected Proposal").Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Application.CutCopyMode = False

UserFormSP.Show

End Sub

What did I miss?

 

[gheyman]

It appears its an ActiveX issue. I put a Text Box on my sheet. Once I removed the TextBox the code worked again.

 

[Logit]

As you discovered, ActiveX controls are finicky. They have the ability to "look pretty" with the varying properties but ... they can sometimes kill a project.
I found out first hand a few weeks ago and had to manually re-code the entire project. No small feat.

Best to stay away from ActiveX