VB Write User Form Data to Excel

**Shazz** · 01-25-2012, 11:32 AM

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** · 01-25-2012, 11:34 AM

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

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

**Shazz** · 01-25-2012, 11:40 AM

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** · 01-25-2012, 11:50 AM

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** · 01-25-2012, 12:45 PM

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** · 01-25-2012, 12:58 PM

You don't have a sheet called Sheet1!

**Shazz** · 01-25-2012, 01:14 PM

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** · 01-25-2012, 01:51 PM

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** · 01-25-2012, 02:59 PM

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** · 01-26-2012, 04:30 AM

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** · 01-26-2012, 05:23 AM

My mistake - remove the

- .Row

part.

**Shazz** · 01-26-2012, 05:26 AM

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

Shazz

**OnErrorGoto0** · 01-26-2012, 05:33 AM

That is not in the code I posted?

**Shazz** · 01-26-2012, 05:50 AM

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** · 01-26-2012, 06:13 AM

What error?

Thread: VB Write User Form Data to Excel

Thread Tools

Display

VB Write User Form Data to Excel

Re: VB Write User Form Data to Excel

Re: VB Write User Form Data to Excel

Re: VB Write User Form Data to Excel

Re: VB Write User Form Data to Excel

Re: VB Write User Form Data to Excel

Re: VB Write User Form Data to Excel

Re: VB Write User Form Data to Excel

Re: VB Write User Form Data to Excel

Re: VB Write User Form Data to Excel

Re: VB Write User Form Data to Excel

Re: VB Write User Form Data to Excel

Re: VB Write User Form Data to Excel

Re: VB Write User Form Data to Excel

Re: VB Write User Form Data to Excel

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions