help adjusting error transfer data from userform to worksheet

[abdelfattah]

i appreciate if anybody can help me i have error" compile error" , can't find project or library i have sheet contains data from a2:at1000 and userform contains many textbox
it gives me highlight in this line
If Len(Trim(Me.Controls(Ad).Value)) = 0 Then
especially this word "Trim"

Private Sub ButtonNew_Click()
Dim Ad As String
Dim c As Integer
Dim xx As Long
''''''''''''''''''''
For c = 1 To ContColmn
    Ad = Cells(1, c).Address(0, 0)
    If Len(Trim(Me.Controls(Ad).Value)) = 0 Then
       MsgBox "address: " & Me.Controls("xx" & c).Caption & " empty", vbCritical + vbMsgBoxRight + vbMsgBoxRtlReading, "empty cell"
       Me.Controls(Ad).SetFocus
        Exit Sub
  End If
Next
''''''''''''''''''''''
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Sheet2
    Lr = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    For c = 1 To ContColmn
        Ad = Cells(1, c).Address(0, 0)
        .Cells(Lr, c).Value = Me.Controls(Ad).Value
    Next
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
A1.Value = Application.WorksheetFunction.Max(Sheets(2).Range("A2:A5000")) + 1
MsgBox "ok"
End Sub

 

[bobsan42]

I guess it is an ActiveX button on a worksheet rather than a control on a form.
So you cannot refer to Controls collection on a worksheet. This is the reason for the error.
Check this: Using ActiveX Controls on Sheets

 

[abdelfattah]

i search this error in internet and suggestion is unchecked this tool from reference "missing type library then it changes the error message "could not find the specified object"