help adjusting error transfer data from userform to worksheet

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
923
Office Version
  1. 2010
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"
VBA Code:
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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,349
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

Well-known Member
Joined
May 3, 2019
Messages
923
Office Version
  1. 2010
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,119,302
Messages
5,577,277
Members
412,779
Latest member
Kings11Q
Top