VB Write User Form Data to Excel

[Shazz]

Hi All,

I have a code below to write the data completed in a user form to excel but it is not working, can anyone see what the problem is with it at all. Also doe anyone know how I can link to a PDF document through the userform?

Private Sub CmdEnter_Click()
    Dim RowCount As Long
    Dim ctl As Control
' Check User Input
If Me.TxtLocation = "" Then
    MsgBox "Please enter a Location."
    Me.TxtLocation.SetFocus
    Exit Sub
  End If
If Me.TextBox2 = "" Then
    MsgBox "Please enter Address."
    Me.TextBox2.SetFocus
    Exit Sub
  End If
If Me.TextBox3 = "" Then
    MsgBox "Please enter a Town."
    Me.TextBox3.SetFocus
    Exit Sub
  End If
If Me.TextBox4 = "" Then
    MsgBox "Please enter County."
    Me.TextBox4.SetFocus
    Exit Sub
  End If
If Me.TextBox5 = "" Then
    MsgBox "Please enter Postcode."
    Me.TextBox5.SetFocus
    Exit Sub
   End If
If Me.TextBox6 = "" Then
    MsgBox "Please enter Site Telephone Number."
    Me.TextBox6.SetFocus
    Exit Sub
  End If
'Write Data to Worksheet
RowCount = .Parent.Cells(.Parent.Rows.Count, 1).End(xlUp).Row - .Row + 1
With Worksheets("Sheet1").Range("A1")
        .Offset(RowCount, 0).Value = Me.TextBox1.Value
        .Offset(RowCount, 1).Value = Me.TextBox2.Value
        .Offset(RowCount, 2).Value = Me.TextBox3.Value
        .Offset(RowCount, 3).Value = Me.TextBox4.Value
        .Offset(RowCount, 4).Value = Me.TextBox5.Value
        .Offset(RowCount, 5).Value = Me.TextBox6.Value
        .Offset(RowCount, 6).Value = Me.ComboBox1.Value
        .Offset(RowCount, 7).Value = Me.ComboBox2.Value
        .Offset(RowCount, 8).Value = Me.TextBox13.Value
            
    End With
' Clear the form
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            ctl.Value = ""
    ElseIf TypeName(ctl) = "CheckBox" Then
        ctl.Value = False
        End If
    Next ctl
End Sub

I would apprcaite anyones help.

Shazz

[Kyle123]

IN this line, .Parent is unqualified, you haven't set the range

.Parent.Cells(.Parent.Rows.Count, 1).End(xlUp).Row - .Row + 1

[Shazz]

Sorry I may sound a bit thick, but not sure what I need to do, I am using this code in another sheet and it works fine??

[OnErrorGoto0]

In the other sheet you must have a With range(...) type statement which is not present here as Kyle said. Change this

RowCount = .Parent.Cells(.Parent.Rows.Count, 1).End(xlUp).Row - .Row + 1
With Worksheets("Sheet1").Range("A1")

to this

With Worksheets("Sheet1").Range("A1")
RowCount = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

[Shazz]

It is still not working unfortunately, I am still getting an error, I have attached my workbook, could you have a look for me and see where I am going wrong.

Thanks

[OnErrorGoto0]

You don't have a sheet called Sheet1!

[Shazz]

Ops, sorry your right, how did I miss that lol.

A couple of questions for you, can you use links within a User Form, i want to be able to click on a link in the userform that will go direct to a PDF Form outside of excel, also is there a way of viewing the data within the userform as well as on the Excel Sheet, and have Find record, First, Last, Next Record like in Access??

Thanks for your help.

Shazz

[Shazz]

When I add data to the form it keeps overiding the same row all the time, do i need to add to the code so that each new data go into the next row?

Shazz

[OnErrorGoto0]

Try

With Worksheets("Sheet1")
        RowCount = .Cells(.Rows.Count, 1).End(xlUp).Row - .Row + 1
        .Cells(RowCount, 1).Value = Me.TextBox1.Value
        .Cells(RowCount, 2).Value = Me.TextBox2.Value
        .Cells(RowCount, 3).Value = Me.TextBox3.Value
        .Cells(RowCount, 4).Value = Me.TextBox4.Value
        .Cells(RowCount, 5).Value = Me.TextBox5.Value
        .Cells(RowCount, 6).Value = Me.TextBox6.Value
        .Cells(RowCount, 7).Value = Me.ComboBox1.Value
        .Cells(RowCount, 8).Value = Me.ComboBox2.Value
        .Cells(RowCount, 9).Value = Me.TextBox13.Value
            
    End With

[Shazz]

No this now flags up an error on the "RowCount = .Cells(.Rows.Count, 1).End(xlUp).Row - .Row + 1"

Private Sub CmdEnter_Click()
    Dim RowCount As Long
    Dim ctl As Control
' Check User Input
If Me.TxtLocation = "" Then
    MsgBox "Please enter a Location."
    Me.TxtLocation.SetFocus
    Exit Sub
  End If
If Me.TextBox2 = "" Then
    MsgBox "Please enter Address."
    Me.TextBox2.SetFocus
    Exit Sub
  End If
If Me.TextBox3 = "" Then
    MsgBox "Please enter a Town."
    Me.TextBox3.SetFocus
    Exit Sub
  End If
If Me.TextBox4 = "" Then
    MsgBox "Please enter County."
    Me.TextBox4.SetFocus
    Exit Sub
  End If
If Me.TextBox5 = "" Then
    MsgBox "Please enter Postcode."
    Me.TextBox5.SetFocus
    Exit Sub
   End If
If Me.TextBox6 = "" Then
    MsgBox "Please enter Site Telephone Number."
    Me.TextBox6.SetFocus
    Exit Sub
  End If
'Write Data to Worksheet
 With Worksheets("Data Form")
 RowCount = .Cells(.Rows.Count, 1).End(xlUp).Row - .Row + 1
        .Offset(RowCount, 0).Value = Me.TextBox1.Value
        .Offset(RowCount, 1).Value = Me.TextBox2.Value
        .Offset(RowCount, 2).Value = Me.TextBox3.Value
        .Offset(RowCount, 3).Value = Me.TextBox4.Value
        .Offset(RowCount, 4).Value = Me.TextBox5.Value
        .Offset(RowCount, 5).Value = Me.TextBox6.Value
        .Offset(RowCount, 6).Value = Me.TextBox7.Value
        .Offset(RowCount, 7).Value = Me.ComboBox1.Value
        .Offset(RowCount, 8).Value = Me.ComboBox2.Value
        .Offset(RowCount, 9).Value = Me.TextBox13.Value
        .Offset(RowCount, 10).Value = Me.TextBox14.Value
        .Offset(RowCount, 11).Value = Me.TextBox15.Value
        .Offset(RowCount, 12).Value = Me.TextBox16.Value
        .Offset(RowCount, 12).Value = Me.TextBox17.Value
    End With
' Clear the form
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            ctl.Value = ""
    ElseIf TypeName(ctl) = "CheckBox" Then
        ctl.Value = False
        End If
    Next ctl
End Sub

[OnErrorGoto0]

My mistake - remove the

- .Row

part.

[Shazz]

Ok done that, now the error comes up at .Offset(RowCount, 0).Value = Me.TextBox1.Value

Shazz

[OnErrorGoto0]

That is not in the code I posted?

[Shazz]

It is still showing an error

With Worksheets("Data Form")
        RowCount = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Cells(RowCount, 1).Value = Me.TextBox1.Value        
        .Cells(RowCount, 2).Value = Me.TextBox2.Value
        .Cells(RowCount, 3).Value = Me.TextBox3.Value
        .Cells(RowCount, 4).Value = Me.TextBox4.Value
        .Cells(RowCount, 5).Value = Me.TextBox5.Value
        .Cells(RowCount, 6).Value = Me.TextBox6.Value
        .Cells(RowCount, 7).Value = Me.ComboBox1.Value
        .Cells(RowCount, 8).Value = Me.ComboBox2.Value
        .Cells(RowCount, 9).Value = Me.TextBox13.Value
        .Cells(RowCount, 10).Value = Me.TextBox13.Value
        .Cells(RowCount, 11).Value = Me.TextBox14.Value
        .Cells(RowCount, 12).Value = Me.TextBox15.Value
        .Cells(RowCount, 13).Value = Me.TextBox16.Value
        .Cells(RowCount, 14).Value = Me.TextBox17.Value
    End With

[OnErrorGoto0]

What error?

[Shazz]

I have now managed to sort it out, sorry for all the bother.

Could you advise if I am able to use the user form like excel and view the records from the userform and not the spreadsheet, with buttons like next Record, first, search fro Record etc.

Thanks you in advance xx

Shazz

[OnErrorGoto0]

Yes but only if you add those buttons and the appropriate code. You may want to look at the built-in data form (if you have not already) or John Walkenbach's Data Form add-in.

[Kyle123]

Do you have a "Textbox1" looks like it should be:

Me.TxtLocation.value

[Shazz]

Sorry Kyle not sure what you mean by this?

Do you have a "Textbox1" looks like it should be:
Code:
Me.TxtLocation.value

I am very new to User Forms, does anyone have any examples that can show how to go about using it for view Data as well as entering data, I also need to know how I can enter a hyperlink in a user form too.

Thanks
Shazz

[Shazz]

Sorry Kyle not sure what you mean by this?

Do you have a "Textbox1" looks like it should be:
Code:
Me.TxtLocation.value

I am very new to User Forms, does anyone have any examples that can show how to go about using it for view Data as well as entering data, I also need to know how I can enter a hyperlink in a user form too.

How do you look at the Built in Data Form

Thanks
Shazz