Still problems with userform

[Marcus Pedersén]

Hi!
I use the following code but I get an error(424) Object required and I can´t
figure it out. Please help!! Error line marked with *

Option Explicit
Private Sub CommandButton1_Click()
Dim LastRow As Range
Dim response As Long

* Set LastRow = Sheet1.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.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 = ""
TextBox3.Text = ""
TextBox1.SetFocus
Else
Unload Me
End If
End Sub


Many thanks in advance!!
Marcus

 

[Harald Staff]

Hej Marcus

You're in Sweden ? My guess is that your Excel doesn't codename the sheets
Sheet1, Sheet2, ... .Your direct adressing coding style requires the sheet
object name that's displayed in the VBE's project explorer, not what the
sheet tabs contain.

Try replacing "Sheet1" with "Ark1" or whatever it says in swedish, or change
to one of these styles

Sheets(1).Range("a65536").End(xlUp)
will use the first sheet in the workbook, no matter what it's named. Will
err if it's a chart sheet.

Sheets("Sheet1").Range("a65536").End(xlUp)
will use the one with the sheet tab saying "Sheet1", no matter where it's
placed. Will err if no such name exists.

HTH. Best wishes Harald