form macro

[jamie85]

I have been trying to create a macro so when played, it will open
form. This however doesnt work. Is there a way of doing this?

 

[Tom Ogilvy]

Depends on what you mean by form. If you mean userform:

Sub showForm()
userform1.show
End Sub

 

[jamie85]

Ok i have created a form and the user can enter two peices of data
'Item No.' and 'No. of items'. After the user has entered these it ask
if you would like to enter another record. When i click yes and ente
another record it enters the information in the same place as th
previous record i entered. I want it to move down a row everytime
enter a new record. Is there any code available for this? THank

 

[Tom Ogilvy]

Assume your Item no data is in column 1 of sheet1

Dim rng as Range
set rng = Worksheets("sheet1").Cells(rows.count,1).end(xlup)(2)
rng.Value = Textbox1.Text
rng.offset(0,1).Value = Textbox2.Text

 

[jamie85]

This is what i currently have:

Private Sub CommandButton1_Click()
<<< Dim LastRow As Object

Set LastRow = Sheet3.Range("a65536").End(xlUp)

LastRow.Offset(-19, 0).Value = TextBox1.Text
LastRow.Offset(-19, 1).Value = TextBox2.Text >>>

MsgBox "One record written to Sheet1"

response = MsgBox("Do you want to enter another record?", _
vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""

TextBox1.SetFocus

Else
Unload Me
End If

End Sub

i assume i replace the code i have put in < > with the code you added?
I did this and tried changing your code so it reads

Dim rng as Range
set rng = Worksheets("sheet3").Cells(rows.count,6).end(xlup)(2)
rng.Value = Textbox1.Text
rng.offset(0,1).Value = Textbox2.Text

but it comes up with an error and highlights the second line. Any idea
why?

 

[Tom Ogilvy]

Your code finds the last row and goes back 19 rows. So my code certainly
isn't equivalent. You also write in columns a and b, but changed mine to
write in F and G

the only thing that would cause this line
set rng = Worksheets("sheet3").Cells(rows.count,6).end(xlup)(2)

to error would be if you don't have a sheet with a tab name of sheet3. Then
you would get a subscript out of range error.

So, without knowing more about your layout and where you want to write your
data, it would be hard to say what you should do.