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

[janicefool]

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

 

[dmt32]

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?

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]

Hi Dave,

Yes, thanks! I realised it too!

Janice

 

[dmt32]

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

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]

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]

Hi,
Your code Enables the control (allows you to enter data) If you want to control the visibility then

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]

Thanks so much Dave. You saved my day.

 

[dmt32]

Most welcome - appreciate feedback

Dave

 

[janicefool]

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.

 

[dmt32]

Hi,
short answer yes & here is link to one such article on the subject that may help you :Excel VBA to copy data from Word document to Excel Workbook - LearnExcelMacro.com
However, as this is a completely new question, can I suggest you start a new thread where you are likely to get many more responses from those that have a greater understanding of this requirement.

Dave