Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Subroutine will not work

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,609
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,609
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Thank you,

    rh

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,609
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  10. #10
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •