Compile Error: Method or data member not found (Userform)

janicefool

New Member
Joined
Sep 23, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
HI i want to transfer my data from userform to excel template for data entry.

Here's my code for a command button. Not sure why but i got this Compile Error message. Please advise.



Private Sub CommandButton1_Click()

Dim ws As Worksheet

Dim iRow As Long



Set ws = Worksheets("Formulate Data")



'find first empty row in database

iRow = ws.Cells.Find(what:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1



With ws

.Cells(iRow, 1).Value = Me.Label1.Value

.Cells(iRow, 2).Value = Me.Label2.Value

.Cells(iRow, 3).Value = Me.Label3.Value

.Cells(iRow, 4).Value = Me.Label4.Value

End With







'clear the data

Me.Label1.Value = " "

Me.Label2.Value = " "

Me.Label3.Value = " "

Me.Label4.Value = " "



End Sub
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,177
Office Version
  1. 2019
Platform
  1. Windows
Hi,
your code references UserForm Lables which do not have the Value property - You use Caption to return a Labels contents

However, I wonder if you mean for your code to referencing TextBoxes in your userform?

VBA Code:
Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim iRow As Long
    
    Set ws = Worksheets("Formulate Data")
    
    'find first empty row in database
    
    iRow = ws.Cells.Find(what:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
    With ws
        .Cells(iRow, 1).Value = Me.TextBox1.Value
        .Cells(iRow, 2).Value = Me.TextBox2.Value
        .Cells(iRow, 3).Value = Me.TextBox3.Value
        .Cells(iRow, 4).Value = Me.TextBox4.Value
    End With
    
    
    'clear the data
    
    Me.TextBox1.Value = " "
    Me.TextBox2.Value = " "
    Me.TextBox3.Value = " "
    Me.TextBox4.Value = " "
End Sub

Dave
 

janicefool

New Member
Joined
Sep 23, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hi Dave,

Yes, thanks! I realised it too!

Janice
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,177
Office Version
  1. 2019
Platform
  1. Windows
Hi Dave,

Yes, thanks! I realised it too!

Janice

No worries, discovering for yourself is very satisfying.

as an aside, you can shorten the code a little

VBA Code:
Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim iRow As Long
    Dim c As Integer
    
    Set ws = Worksheets("Formulate Data")
    
    'find first empty row in database
    
    iRow = ws.Cells.Find(what:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
    For c = 1 To 4
        With Me.Controls("TextBox" & c)
        'add record to worksheet
            ws.Cells(iRow, c).Value = .Value
        'clear control
            .Value = ""
        End With
    Next c
    

End Sub


BTW for to say welcome to forum

Dave
 

janicefool

New Member
Joined
Sep 23, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks Dave!

I would like to make a textbox visible only when the checkbox is ticked.

Not sure why my code isn't working:

Private Sub CheckBox1_Click()
If UserForm1.CheckBox1.Value = True Then
tbVI.Enabled = True
End If

End sub
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,177
Office Version
  1. 2019
Platform
  1. Windows
Hi,
Your code Enables the control (allows you to enter data) If you want to control the visibility then

VBA Code:
Private Sub CheckBox1_Click()
    Me.tbVI.Visible = Me.CheckBox1.Value
End Sub

should do what you want.

Note I have used the Me keyword - Me is your userform & its use negates need to keep typing the forms full name.

Dave
 

janicefool

New Member
Joined
Sep 23, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks so much Dave. You saved my day.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,177
Office Version
  1. 2019
Platform
  1. Windows
Most welcome - appreciate feedback

Dave
 

janicefool

New Member
Joined
Sep 23, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hi Dave,

Would like to ask if there's a way to extract data from MS Word to Excel?

I have a WORD file with the format set. I want to extract the data from Word to Excel for every new Word doc.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,019
Messages
5,545,523
Members
410,689
Latest member
ConfuzzledThomas
Top