Subroutine will not work

[MrBaldGuy]

I downloaded an excel file that demonstrated the use of a userform inserting, deleting, and editing records in a database. When I activated the code in the file that I downloaded, all worked well. I had another project that I am working on and this looked like the perfect code that I needed to perform the above in my file. After copying the code from the downloaded file to my module (and not having it to work) and then typing in the code, and still not having it to work, I decided it was time I seeked help from the masters. The following is the subroutine I have been trying to call.

Sub GetData()
With Me
.TextA = Range("Database").Cells(iRowNumber, 1)
.TextB = Range("Database").Cells(iRowNumber, 2)
.TextC = Range("Database").Cells(iRowNumber, 3)
.TextD = Range("Database").Cells(iRowNumber, 4)
End With
End Sub

When the above sub is called, I always receive the error: Run-time error '1004'
Application-defined or object-defined error.

Oh yes, by the way, my module begins with...
Option Explicit
Dim iRowNumber As Integer.

I have checked spelling, and everything seems to correspond with the downloaded file, but I keep getting the error.

Any help would be greatly appreciated.
MrBaldGuy

[gplhl]

Do you have a named range setup called Database?

_________________
Regards,

Gary Hewitt-Long

[ This Message was edited by: gplhl on 2002-03-08 16:06 ]

[Russell Hauf]

You're going to have to give more information. Start by posting the whole Subroutine, not just part of it. For example, where is iRowNumber being set? If it's not, that's a big problem.

Also, are there text boxes in your form named TextA, TextB, TextC, and TextD? Is there a range in your spreadsheet called "Database"?

Thank you,

-rh

[Ivan F Moala]

On 2002-03-08 16:00, MrBaldGuy wrote:
I downloaded an excel file that demonstrated the use of a userform inserting, deleting, and editing records in a database. When I activated the code in the file that I downloaded, all worked well. I had another project that I am working on and this looked like the perfect code that I needed to perform the above in my file. After copying the code from the downloaded file to my module (and not having it to work) and then typing in the code, and still not having it to work, I decided it was time I seeked help from the masters. The following is the subroutine I have been trying to call.

Sub GetData()
With Me
.TextA = Range("Database").Cells(iRowNumber, 1)
.TextB = Range("Database").Cells(iRowNumber, 2)
.TextC = Range("Database").Cells(iRowNumber, 3)
.TextD = Range("Database").Cells(iRowNumber, 4)
End With
End Sub

1) Have you defined the Named range "DataBase" ?
2) What about your Textboxs have these been
named as TextA, TextB etc


Ivan

[MrBaldGuy]

Thanks guys, the following is the code I am working with. I do have named ranges in a worksheet for all references to ranges in the code. Thanks again.

Option Explicit
Dim iRowNumber As Integer

Private Sub btnRestore_Click()
Call GetData
btnRestore.Enabled = False
End Sub

Private Sub CommandButton1_Click() 'new record
Call PutData
Range("Database").Resize(Range("Database").Rows.Count + 1).Name = "Database"
iRowNumber = Range("Database").Rows.Count
Call GetData
TextA.SetFocus
ScrollBar1.Max = Range("Database").Rows.Count
ScrollBar1.Value = iRowNumber
btnRestore.Enabled = False
lblRecNumber.Caption = iRowNumber - 1
End Sub

Private Sub CommandButton2_Click()
If Range("Database").Rows.Count = 2 Then
MsgBox "You cannot delete the last record", vbExclamation
Exit Sub
End If
If MsgBox("Are you sure you want to delete this record?", vbQuestion + vbOKCancel) = vbCancel Then
Exit Sub
Range("Database").Rows(iRowNumber).EntireRow.Delete
If iRowNumber > Range("Database").Rows.Count Then
iRowNumber = Range("Database").Rows.Count
End If
Call GetData
ScrollBar1.Enabled = False
ScrollBar1.Value = iRowNumber
ScrollBar1.Max = Range("Database").Rows.Count
lblRecNumber.Caption = iRowNumber - 1
End Sub

Private Sub CommandButton4_Click()
Unload Me
End Sub

Private Sub ScrollBar1_Change()
Call PutData
iRowNumber = ScrollBar1.Value
btnRestore.Enabled = False
Call GetData
TextA.SetFocus
lblRecNumber.Caption = iRowNumber - 1
End Sub

Private Sub TextA_Change()
Me.EditEntry
End Sub

Private Sub TextB_Change()
Me.EditEntry
End Sub

Private Sub TextC_Change()
Me.EditEntry
End Sub

Private Sub TextD_Change()
Me.EditEntry
End Sub

Private Sub UserForm_Activate()
iRowNumber = Range("lastRecordNumber")
If iRowNumber > Range("Database").Rows.Count Then
iRowNumber = Range("Database").Rows.Count
End If
Call GetData
ScrollBar1.Value = iRowNumber
ScrollBar1.Max = Thanks Guys, the following is the complete code I am working with. By the way, I have named ranges for those refered to in the code. Thanks again.


Range("Database").Rows.Count
btnRestore.Enabled = False
End Sub

Sub EditEntry()
btnRestore.Enabled = True
End Sub

Sub GetData()
With Me
.TextA = Range("Database").Cells(iRowNumber, 1)
.TextB = Range("Database").Cells(iRowNumber, 2)
.TextC = Range("Database").Cells(iRowNumber, 3)
.TextD = Range("Database").Cells(iRowNumber, 4)
End With
End Sub

Sub PutData()
With Me
Range("Database").Cells(iRowNumber, 1) = .TextA
Range("Database").Cells(iRowNumber, 2) = .TextB
Range("Database").Cells(iRowNumber, 3) = .TextC
Range("Database").Cells(iRowNumber, 4) = .TextD
End With
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Call PutData
Range("lastRecordNumber") = iRowNumber
End Sub


[ This Message was edited by: MrBaldGuy on 2002-03-08 21:02 ]

[Russell Hauf]

Can you tell us where exactly the error is occuring? (when the error occurs, click the Debug button).

Thank you,

rh

[MrBaldGuy]

The error occurs at "Call GetData" when the Private Sub UserForm_Activate routine is running. When I click on the Debug button in the dialog box, the line of code beginning with ".TextA" is highlighted. Hope this helps.

[Juan Pablo González]

Try it like this (The Me part is not needed IF THE CODE is in the userform module)

Sub GetData() 

TextA = Range("Database").Cells(iRowNumber, 1) 

TextB = Range("Database").Cells(iRowNumber, 2) 

TextC = Range("Database").Cells(iRowNumber, 3) 

TextD = Range("Database").Cells(iRowNumber, 4) 

End Sub 

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting

[ This Message was edited by: Juan Pablo G. on 2002-03-11 16:57 ]

[Russell Hauf]

On 2002-03-11 16:54, MrBaldGuy wrote:
The error occurs at "Call GetData" when the Private Sub UserForm_Activate routine is running. When I click on the Debug button in the dialog box, the line of code beginning with ".TextA" is highlighted. Hope this helps.

I think I know your problem. Try putting the sheet name in front of Range(...

Such as:

Sheets("Sheet1").Range("Database").Cells(iRowNumber, 1)

or

ActiveSheet.Range("Database").Cells(iRowNumber, 1)

if the active sheet is the one you're working with. The first option is probably better.

Hope this helps,

Russell

[MrBaldGuy]

Hi again guys,
tried both solutions but neither worked. i still received the "1004" error. any other ideas? thanks again for your assistance.