UserForm code help needed.

[madbloke]

Thanks to all who helped me yesterday. You were so helpful I thought I'
throw my other VBA problem into the ring.

What I've got is a Userform for users to input address details, whic
then puts those details into an excel sheet when a button is clicked
(The intention is eventually to mailmerge the details and print a shee
of address labels)

Unfortunately, it keeps putting the address details on the same line
until you try and close the form, when it gets stuck in a loop.

Hope someone can help. Here's the userform code



Public Sub InputButton_Click()
'set up variables
Dim Check

Dim Activerow As Variant
Dim StartCellRow As Integer
Dim StartCellColumn As Integer

Dim Printbutt As Integer

Dim Title As String
Dim FirstName As String
Dim Surname As String
Dim Address1 As String
Dim Address2 As String
Dim Address3 As String
Dim Address4 As String
Dim Postcode As String
Dim Todaydate As String
Dim ID As String
Dim form As String

'fill variables with info from form
Check = True
Title = Titlebox
FirstName = TxtFirstname
Surname = TxtSurname
Address1 = TxtAddress1
Address2 = TxtAddress2
Address3 = TxtAddress3
Address4 = TxtAddress4
Postcode = TxtPostcode
Todaydate = TxtDate
ID = TxtLoginID
form = Formbox
Today = Format(Date, "d/mm/yyyy")
StartCellRow = "1"
StartCellColumn = "1"
Printbutt = "1"
'check this works
Do
ActiveCell(StartCellRow, StartCellColumn) = Title
StartCellColumn = StartCellColumn + 1
ActiveCell(StartCellRow, StartCellColumn) = FirstName
StartCellColumn = StartCellColumn + 1
ActiveCell(StartCellRow, StartCellColumn) = Surname
StartCellColumn = StartCellColumn + 1
ActiveCell(StartCellRow, StartCellColumn) = Address1
StartCellColumn = StartCellColumn + 1
ActiveCell(StartCellRow, StartCellColumn) = Address2
StartCellColumn = StartCellColumn + 1
ActiveCell(StartCellRow, StartCellColumn) = Address3
StartCellColumn = StartCellColumn + 1
ActiveCell(StartCellRow, StartCellColumn) = Address4
StartCellColumn = StartCellColumn + 1
ActiveCell(StartCellRow, StartCellColumn) = Postcode
StartCellColumn = StartCellColumn + 1
ActiveCell(StartCellRow, StartCellColumn) = Today
StartCellColumn = StartCellColumn + 1
ActiveCell(StartCellRow, StartCellColumn) = ID
StartCellColumn = StartCellColumn + 1
ActiveCell(StartCellRow, StartCellColumn) = form
StartCellRow = StartCellRow + 1
StartCellColumn = "1"

'this bit is to try and clear the form ready for the next input
Unload UserForm1
UserForm1.Show

Loop Until Check = False


End Sub

'when this button is clicked, check should equal false
'and the form should be closed

Public Sub PrintButton_Click()
Check = False
End Su

 

[Alan Hutchins]

There are a few things that would help:

1) you could specify the cell where you want to start
your process, and then select it as the activecell. Then
use Activecell.Offset(0,1).value = x

where x is the value you wish to assign.

You just increase the offset number by 1 to move across
the columns, and it would be better to specifically code
these, rather than increment them.

2) you seem to be relying on Excel determining the active
row to produce the next empty row - this is not a
foolproof method. You would be better off determining the
last used cell in the sheet where you are writing the
details, and then obtaining the rwo, incrementing by one
and selecting the first cell on taht row - the function
below always works.

Call the function like this:

Call Goto_LAst("a1",1,1)

it will return the last cell address and the numeric
column and the number of the row also.

Function Goto_Last(strrange, int_col, int_row)
On Error Resume Next
application.ScreenUpdating = False
Cells(Cells.Find("*", Range("A1"), , , xlByRows,
xlPrevious).Row, _
Cells.Find("*", Range("A1"), , , xlByColumns,
xlPrevious).Column).Select
If Err.Number <> 0 Then MsgBox "No data in sheet"
application.ScreenUpdating = True
strrange = ActiveCell.AddressLocal
int_col = ActiveCell.Column
int_row = ActiveCell.Row
End Function


Hope this is of use

Alan

 

[madbloke]

Thanks, Alan. Unfortunately, I'm an absolute beginner at all this.
I've done what you suggested at point 1 (I think), but don't know how
to specify the cell I want to start at. I've tried this -

ActiveCell("A2") = Title
ActiveCell.Offset(0, 1).Value = FirstName
ActiveCell.Offset(0, 2).Value = Surname
ActiveCell.Offset(0, 3).Value = Address1
ActiveCell.Offset(0, 4).Value = Address2
ActiveCell.Offset(0, 5).Value = Address3
ActiveCell.Offset(0, 6).Value = Address4
ActiveCell.Offset(0, 7).Value = Postcode
ActiveCell.Offset(0, 8).Value = Today
ActiveCell.Offset(0, 9).Value = ID
ActiveCell.Offset(0, 10).Value = form

And as for point 2, (and this is probably a dumb question), where do I
put that code? Do I put it as a seperate module?

And if I'm specifying a start cell, surely each time the program loops,
it'll start at the same place, overwriting the original data? (That's
the problem I was having with my original code.)

There are a few things that would help:

1) you could specify the cell where you want to start
your process, and then select it as the activecell. Then
use Activecell.Offset(0,1).value = x

where x is the value you wish to assign.

You just increase the offset number by 1 to move across
the columns, and it would be better to specifically code
these, rather than increment them.

2) you seem to be relying on Excel determining the active
row to produce the next empty row - this is not a
foolproof method. You would be better off determining the
last used cell in the sheet where you are writing the
details, and then obtaining the rwo, incrementing by one
and selecting the first cell on taht row - the function
below always works.

Call the function like this:

Call Goto_LAst("a1",1,1)

it will return the last cell address and the numeric
column and the number of the row also.

Function Goto_Last(strrange, int_col, int_row)
On Error Resume Next
application.ScreenUpdating = False
Cells(Cells.Find("*", Range("A1"), , , xlByRows,
xlPrevious).Row, _
Cells.Find("*", Range("A1"), , , xlByColumns,
xlPrevious).Column).Select
If Err.Number <> 0 Then MsgBox "No data in sheet"
application.ScreenUpdating = True
strrange = ActiveCell.AddressLocal
int_col = ActiveCell.Column
int_row = ActiveCell.Row
End Function


Hope this is of use

Alan

 

[Guest]

THIS IS A CUT DOWN VERSION OF THE CODE YOU HAD AMENDED
USING THE SUGGESTIONS (Note the Function is included but
not shown in the code here):

Try this in a new workbook with 1 userform with 3
textboxes, and two buttons one to add teh value and 1 to
cancel the process.

You'll notice that it adds your data in each line going
down one after the other. The easiest way to prove it is
to run it (don't forget to include the Function code), by
just entering the letters of teh alphabet in order into
the first textbox only

'++++++++++++++++++++++++++++++++++++++++
Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub Button_cancel_Click()
Unload Me
End Sub

Public Sub InputButton_Click()
'set up variables

Dim x As Integer
Dim y As String
Dim int_col As Integer
Dim int_row As Integer

Dim Title As String
Dim Firstname As String
Dim Surname As String

'fill variables with info from form

Title = TitleBox
Firstname = TxtFirstname
Surname = TxtSurname

Worksheets("sheet1").Select

int_col = 1
int_row = 1
Call Goto_Last("a1", int_col, int_row)

x = int_row + 1
y = "A" & x

Range(y).Select

Range(y).Value = Title
Range(y).Offset(0, 1).Value = Firstname
Range(y).Offset(0.2).Value = Surname


'this bit is to try and clear the form ready for the next
input
Unload UserForm1
UserForm1.Show


End Sub

-----Original Message-----
Thanks, Alan. Unfortunately, I'm an absolute beginner at all this.
I've done what you suggested at point 1 (I think), but don't know how
to specify the cell I want to start at. I've tried this -

ActiveCell("A2") = Title
ActiveCell.Offset(0, 1).Value = FirstName
ActiveCell.Offset(0, 2).Value = Surname
ActiveCell.Offset(0, 3).Value = Address1
ActiveCell.Offset(0, 4).Value = Address2
ActiveCell.Offset(0, 5).Value = Address3
ActiveCell.Offset(0, 6).Value = Address4
ActiveCell.Offset(0, 7).Value = Postcode
ActiveCell.Offset(0, 8).Value = Today
ActiveCell.Offset(0, 9).Value = ID
ActiveCell.Offset(0, 10).Value = form

And as for point 2, (and this is probably a dumb question), where do I
put that code? Do I put it as a seperate module?

And if I'm specifying a start cell, surely each time the program loops,
it'll start at the same place, overwriting the original data? (That's
the problem I was having with my original code.)

There are a few things that would help:

1) you could specify the cell where you want to start
your process, and then select it as the activecell. Then
use Activecell.Offset(0,1).value = x

where x is the value you wish to assign.

You just increase the offset number by 1 to move across
the columns, and it would be better to specifically code
these, rather than increment them.

2) you seem to be relying on Excel determining the active
row to produce the next empty row - this is not a
foolproof method. You would be better off determining the
last used cell in the sheet where you are writing the
details, and then obtaining the rwo, incrementing by one
and selecting the first cell on taht row - the function
below always works.

Call the function like this:

Call Goto_LAst("a1",1,1)

it will return the last cell address and the numeric
column and the number of the row also.

Function Goto_Last(strrange, int_col, int_row)
On Error Resume Next
application.ScreenUpdating = False
Cells(Cells.Find("*", Range("A1"), , , xlByRows,
xlPrevious).Row, _
Cells.Find("*", Range("A1"), , , xlByColumns,
xlPrevious).Column).Select
If Err.Number <> 0 Then MsgBox "No data in sheet"
application.ScreenUpdating = True
strrange = ActiveCell.AddressLocal
int_col = ActiveCell.Column
int_row = ActiveCell.Row
End Function


Hope this is of use

Alan

 

[dude]

I assume that you are trying to add data from your form in
to database format? If so, you could just simply read the
user input from the form into a simple array and then add
data to your table this way.
something like this may work:

Private Sub CommandButton1_Click()

ReDim mydata(4)

For na = 0 To 3
mydata(na) = Controls(na).Text
Controls(na).Text = ""
Next

Dim NewRecordRng As Object
Set NewRecordRng = Worksheets("sheet1").Cells(2,
1).CurrentRegion
'Put new values into worksheet just below those rows.
newrow = NewRecordRng.Rows.Count + 1

With NewRecordRng
For na = 0 To 3
.Cells(newrow, na + 1).Value = mydata(na)
Next
End With
TextBox2.SetFocus
End Sub

I have only shown 4 textboxes but you can modify to meet
your needs. To see if this works for you. create a form &
add 4 textboxes then add a button(in that order) Double
click the button and past above code. You need a sheet
named sheet1. Run the form & enter data. It should add
data to sheet1 & clear textboxes for next entry. further
entry should be placed on next line.

Hope helpful