Subroutine will not work

MrBaldGuy

New Member
Joined
Mar 7, 2002
Messages
4
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Can you tell us where exactly the error is occuring? (when the error occurs, click the Debug button).

Thank you,

rh
 
Upvote 0
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.
 
Upvote 0
Try it like this (The Me part is not needed IF THE CODE is in the userform module)<pre>
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</pre>

_________________
Regards,

Juan Pablo G.
MrExcel.com Consulting
This message was edited by Juan Pablo G. on 2002-03-11 16:57
 
Upvote 0
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
 
Upvote 0
Hi again guys,
tried both solutions but neither worked. i still received the "1004" error. any other ideas? thanks again for your assistance.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top