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

janicefool

New Member
Joined
Sep 23, 2020
Messages
9
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Most welcome - appreciate feedback

Dave
 
Upvote 0
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.
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top