User Defined Type not Defined

[Carrie_Loos via OfficeKB.com]

Here is the code I got from the Microsoft site for "How to Use a UserForm for
Entering Data" But I cannot get the code to work. I get a compile error, User
Defined Type not Defined. Seems to be having trouble with the oject and I
don't know why. Can anyone see it?


Private Sub CommandButton1_Click()
Dim LastRow As Object

Set LastRow = Keys.Range("b65530").End(x1Up)

LastRow.Offset(1, 0).Value = TestBox1.Text

MsgBox "Record written to Training List"

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

If response = vbYes Then
TextBox1.Text = " "

TextBox1.SetFocus

Else
Unload Me
End If

End Sub

 

[Jim Thomlinson]

Try this... Change the sheet reference as necessary.

Private Sub CommandButton1_Click()

sheets("Sheet1").cells(rows.count, "B").End(xlUp).offset(1,0).value =
TestBox1.Text

MsgBox "Record written to Training List"

If MsgBox("Do you want to enter another record?", vbYesNo)= vbYes Then
TextBox1.Text = ""
TextBox1.SetFocus
Else
Unload Me
End If

End Sub

 

[eliano]

Hi Carri.

Probably:

Set LastRow = Keys.Range("b65530").End(x1Up)

Set LastRow = Keys.Range("b65530").End(xLUp) <-------

Regards
Eliano

 

[Carrie_Loos via OfficeKB.com]

Hmmm - Now I am getting a runtime errror '424' "Object required"

Try this... Change the sheet reference as necessary.

Private Sub CommandButton1_Click()

sheets("Sheet1").cells(rows.count, "B").End(xlUp).offset(1,0).value =
TestBox1.Text

MsgBox "Record written to Training List"

If MsgBox("Do you want to enter another record?", vbYesNo)= vbYes Then
TextBox1.Text = ""
TextBox1.SetFocus
Else
Unload Me
End If

End Sub

Here is the code I got from the Microsoft site for "How to Use a UserForm for
Entering Data" But I cannot get the code to work. I get a compile error, User

[quoted text clipped - 22 lines]

End Sub

 

[Carrie_Loos via OfficeKB.com]

Thanks Eliano -

I didn't catch the typo but it didn't fix the issue either.

Hi Carri.

Probably:

Set LastRow = Keys.Range("b65530").End(x1Up)

Set LastRow = Keys.Range("b65530").End(xLUp) <-------
Regards
Eliano

Here is the code I got from the Microsoft site for "How to Use a UserForm for
Entering Data" But I cannot get the code to work. I get a compile error, User

[quoted text clipped - 25 lines]

 

[Carrie_Loos via OfficeKB.com]

Thanks Eliano -

I didn't catch the typo but it didn't fix the issue either.

Hi Carri.

Probably:

Set LastRow = Keys.Range("b65530").End(x1Up)

Set LastRow = Keys.Range("b65530").End(xLUp) <-------
Regards
Eliano

Here is the code I got from the Microsoft site for "How to Use a UserForm for
Entering Data" But I cannot get the code to work. I get a compile error, User

[quoted text clipped - 25 lines]

 

[Peter T]

TestBox1 ?
or should that be TextBox1

Regards
Peter T

Hmmm - Now I am getting a runtime errror '424' "Object required"

Try this... Change the sheet reference as necessary.

Private Sub CommandButton1_Click()

sheets("Sheet1").cells(rows.count, "B").End(xlUp).offset(1,0).value =
TestBox1.Text

MsgBox "Record written to Training List"

If MsgBox("Do you want to enter another record?", vbYesNo)= vbYes Then
TextBox1.Text = ""
TextBox1.SetFocus
Else
Unload Me
End If

End Sub

Here is the code I got from the Microsoft site for "How to Use a UserForm for
Entering Data" But I cannot get the code to work. I get a compile

error, User
[quoted text clipped - 22 lines]

End Sub

 

[Dave Peterson]

Add this line to the top of your userform module:

Option Explicit

This will make you declare your variables. It would have caught the typo (one
vs ell) and the typo with TestBox vs TextBox.

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

Set LastRow = Keys.Range("b65536").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text

MsgBox "Record written to Training List"

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

If Response = vbYes Then
TextBox1.Text = ""
TextBox1.SetFocus
Else
Unload Me
End If
End Sub

 

[Carrie_Loos via OfficeKB.com]

Thanks Dave - once again. the Option Explicit work wonders. Apparently I was
way too tiered for this yesterday and should have just left it alone. Many
cudos to you for all your assistance with this project.

Carrie

Add this line to the top of your userform module:

Option Explicit

This will make you declare your variables. It would have caught the typo (one
vs ell) and the typo with TestBox vs TextBox.

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

Set LastRow = Keys.Range("b65536").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text

MsgBox "Record written to Training List"

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

If Response = vbYes Then
TextBox1.Text = ""
TextBox1.SetFocus
Else
Unload Me
End If
End Sub

Here is the code I got from the Microsoft site for "How to Use a UserForm for
Entering Data" But I cannot get the code to work. I get a compile error, User

[quoted text clipped - 26 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200802/1

 

[Dave Peterson]

It may seem like more work to have to declare your variables. But I bet you
spent more time searching for the problem than you would have spent declaring
the variables.

If you want to make "Option Explicit" show up in every new module, you can use:
Open the VBE
Tools|Options|Check Require Variable Declaration

Thanks Dave - once again. the Option Explicit work wonders. Apparently I was
way too tiered for this yesterday and should have just left it alone. Many
cudos to you for all your assistance with this project.

Carrie

Add this line to the top of your userform module:

Option Explicit

This will make you declare your variables. It would have caught the typo (one
vs ell) and the typo with TestBox vs TextBox.

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

Set LastRow = Keys.Range("b65536").End(xlUp)

LastRow.Offset(1, 0).Value = TextBox1.Text

MsgBox "Record written to Training List"

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

If Response = vbYes Then
TextBox1.Text = ""
TextBox1.SetFocus
Else
Unload Me
End If
End Sub

Here is the code I got from the Microsoft site for "How to Use a UserForm for
Entering Data" But I cannot get the code to work. I get a compile error, User

[quoted text clipped - 26 lines]

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200802/1