Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Lounger
    Join Date
    Jun 2015
    Posts
    23
    Thanks
    3
    Thanked 3 Times in 3 Posts

    Run Time Error Method 'Value' of object 'Range' failed

    Hi All

    hope you all are ding well !

    I need help to resolve the problem which i am facing.

    i am creating a database to keep record of vendor invoices and used VBA userform to input data into a table "Inv_Data" and sheet name is "Data" and use following code to find last empty row to input new record into table
    "Inv_Data".

    Source!F2=
    COUNTA(Inv_Data[ID]) .........."ID is first column of table and giving number to new records"
    erow = Sheets("Source").Range("F2").Value + 1

    and occasionally i am getting "run time error 2147417848 Method 'value' of object 'range' failed", after closing and reopening the file error does not appear. i don't know the reason.

    please guide me how to get rid of this error.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,355
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi erfan

    To find the next available row on sheet [Data], you could use something like..

    erow = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row + 1

    ..where "A" is a data-column on sheet [Data] that has non-blank entries e.g. INVOICE NUMBER
    (change "A" to any data-column letter as required)

    zeddy

  3. #3
    New Lounger
    Join Date
    Jun 2015
    Posts
    23
    Thanks
    3
    Thanked 3 Times in 3 Posts
    Thanks Zeddy

    Data is being input into a table name "Inv_Data" and i changed my erow code as following. but still facing the same error. i also tried to change code for text box which are putting data into the table from user form. copied below, i am attaching my file please take a look and help me to resolve the problem. Data_Entry form is causing the problem.

    erow = Worksheets("Data").Range("A1").CurrentRegion.Rows. Count

    code for data input from userform into data sheet.

    Sheet3.Cells(erow, 1) = erow
    Sheet3.Cells(erow, 2) = Me.Txt_PO
    Sheet3.Cells(erow, 3) = Me.Txt_Name
    Sheet3.Cells(erow, 4) = Me.Txt_EPI
    Sheet3.Cells(erow, 5) = Me.Txt_Date
    Sheet3.Cells(erow, 6) = Me.Txt_Inv_Amount
    Sheet3.Cells(erow, 7) = Me.Txt_Dscp
    Sheet3.Cells(erow, 8) = Me.Combo_Team
    Attached Files Attached Files

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,436
    Thanks
    3
    Thanked 259 Times in 237 Posts
    Since you have got a Table, you might as well use it properly:

    Code:
    Private Sub CommandButton1_Click()
    'When we click the "continue/save" button
        Dim newrow                As ListRow    'variable for position control
        Dim UserMessage           As String    'Variable to configure usermessage at the end
        Dim RecNo                 As Long
        Dim Rec                   As String
        
        With Worksheets("Data").ListObjects(1)
            RecNo = Application.Max(.ListColumns("ID").Range) + 1
            Set newrow = .ListRows.Add
        End With
        UserMessage = " has been added to the database"    'Configure user message for new entry
        Rec = "Record No " & RecNo    'Configure user message for new entry
    
        newrow.Range(1, 1).Resize(, 8).Value = Array(RecNo, Me.Txt_PO, Me.Txt_Name, Me.Txt_EPI, _
                                        CDate(Me.Txt_Date), Me.Txt_Inv_Amount, Me.Txt_Dscp, Me.Combo_Team)
        MsgBox Rec & UserMessage, 0, "Complete"
        Unload Me
    
    End Sub
    Regards,
    Rory

  5. #5
    New Lounger
    Join Date
    Jun 2015
    Posts
    23
    Thanks
    3
    Thanked 3 Times in 3 Posts
    Sorry, i got same error and on debug following line of code highlighted.

    Set newrow = .ListRows.Add

    file attached with new code as suggested by Rory.
    Attached Files Attached Files

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,436
    Thanks
    3
    Thanked 259 Times in 237 Posts
    I don't see how you could get a Value error on that line, since it doesn't access any values.

    The form works for me - how exactly are you running it? (precise steps please)
    Regards,
    Rory

  7. #7
    New Lounger
    Join Date
    Jun 2015
    Posts
    23
    Thanks
    3
    Thanked 3 Times in 3 Posts
    Opps Sorry, this time error is Method "Add" of object "ListRows" failed.

    error snap shot attached.
    Attached Images Attached Images

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,436
    Thanks
    3
    Thanked 259 Times in 237 Posts
    And my other question? What are the exact steps you take to produce this error with the sample workbook (from start to finish, please)?
    Regards,
    Rory

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,355
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi erfan

    Looking at your file, what happens if a data record gets deleted????
    (For example, the same data record might be accidentally entered more than once).

    Deleting any records would mess up you data-retrieval and update process, since you are relying on the ID number to correlate exactly with the row-offset from the heading-row.

    (You could identify and update/retrieve the correct row number for any given record by using MATCH/INDEX etc etc etc)

    However, in the meantime, may I suggest that you amend the formulas of these cells on your sheet [Source]

    [F1] = MAX(Data!A:A)+1

    [F2] =COUNTA(Data!A:A)+1

    zeddy

  10. #10
    New Lounger
    Join Date
    Jun 2015
    Posts
    23
    Thanks
    3
    Thanked 3 Times in 3 Posts
    Dear Rory !

    I have replaced my code with the code you suggested for Data_Entry form and then in Excel i filled the form and hit the button Continue/Save on Data_Entry form to check whether code is working or not and it gives the error Method "Add" of object "ListRows" failed and on debug Excel crashed (Excel also crashed with previous code). File attached for your review.

    Thanks Zeddy for your suggestion.

    To prevent a record get deleting, i was planning to protect my sheet with password (Not sure about this might not work) and also planning to set Date sheet property to very hidden so users cannot access this sheet.
    Duplicate record means same ID for two separate records or different ID but associated with same data (PO No, Vendor Name etc etc)?

    For TargetRow to update/edit a record i tried the following code but it does not work so i changed the code.

    TargetRow = Application.WorksheetFunction.Match(Column_ID, Sheets("Data").Range("Dyn_ID"), 0) 'Failed Attempt

    then i revised my code as under

    TargetRow = Sheets("Source").Range("H1").Value ' Set Row number to edit

    Combo Box name "Column_ID" properties "control source" set to Source!H1

    please guide for further improving.
    Attached Files Attached Files

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,355
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi erfan

    You didn't put my suggested formulas into your last attached file.
    There is a reason for using my formulas.

    Your formula in cell [F1] currently gives the ID number for New Entry as 23.
    But if you look at your sheet [Data], you will see that this ID number is already used.

    Your formula in cell [F2] currently gives the Next Available ROW to input New data as 22.
    But if you look at your sheet [Data], you will see that the next available row is row 24.

    If you used my suggested formulas, you would get the correct values.

    zeddy

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,355
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi erfan

    Perhaps you could make use of Excel's built-in DataForm.

    In the attached file I have added code to hide all data records when you switch to the [Data] sheet.
    In the top-left corner are two padlocks which can be clicked to unlock the sheet (displaying all records for Developer use), and re-lock the sheet (which hides the records).

    There are some buttons which allow basic operations.
    I will explain later how to search records using the [Criteria] button on the Data form.

    Notice that the [Review/Search records] button does not allow the User to make any changes to the records.

    The [Edit Existing Record] button could have a password associated with it.

    Notice that the User cannot edit the ID number (it is locked) and cannot edit the Cheque details (perhaps you might want only certain Users to do this).

    The [Add New Record] function should work.

    A separate button for [Edit Cheque details] has been added.

    The vba code for all this is not too complicated, and is in module named [modData]

    I made some minor changes to your code for the [Data_Entry] form.
    Also, i added a named cell [nextID] for the next ID number.

    Try this out and let me know how you get on.

    zeddy
    Attached Files Attached Files

  13. #13
    New Lounger
    Join Date
    Jun 2015
    Posts
    23
    Thanks
    3
    Thanked 3 Times in 3 Posts
    Thanks Zeddy !

    i have reviewed your file excellent work, a little modification required.

    Display hidden data should be password protected.

    The [Edit Existing Record] and [Edit Cheque details] buttons may have associated password to update/edit records.

    For edit existing record i also locked the last 03 cloumns of Data (Dispatch No; Dispatch Date and Return Remarks) as these 03 columns will be updated by cheque dispatcher by using the button [Edit Cheque details].

    Can we put these buttons in separate sheet (other than data) like Switchboard sheet?
    Can we customize built-in DataForm to increase the size of form and font size for a better view like UserForm?
    Attached Files Attached Files

  14. #14
    New Lounger
    Join Date
    Jun 2015
    Posts
    23
    Thanks
    3
    Thanked 3 Times in 3 Posts
    Dear Zeddy !

    please also guide how to have values in amount column "F" as new records from user form are appearing as text.

  15. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,355
    Thanks
    168
    Thanked 633 Times in 602 Posts
    Hi erfan

    if you add a zero it will 'coerce' a textbox entry to be numeric..
    e.g. Me.Txt_Inv_Amount + 0

    I have included this in the attached updated example file.
    Can we put these buttons in separate sheet (other than data) like Switchboard sheet?
    I have placed buttons on the [SwitchBoard] sheet.

    Can we customize built-in DataForm to increase the size of form and font size for a better view like UserForm?
    No, you don't have much choice with the Excel built-in DataForm (it has a max of 32 data fields).
    But you have discovered that you can 'disable' some of the fields displayed in the DataForm i.e. prevent changes in certain fields, by 'locking' the relevant corresponding data columns and protecting the data-records-worksheet.

    The big advantage of using the Excel built-in DataForm is that it comes with a very good 'search' mode where you can use wildcard characters in the DataForm's fields..
    e.g. sm?th finds "smith" and "smyth", *east finds "Northeast" and "Southeast", * finds records where that field is non-blank etc etc etc

    NOTES:
    These new buttons will require a password to be entered.
    Each button uses a different password.

    Rather than put the actual passwords directly into the vba code, I have placed them in the cells underneath the buttons i.e. in cells [G10], [G12] and [G15], and use vba to 'fetch' these cell values.

    The prompt for a password uses a new UserForm I have added (rather than using a simple InputBox method).
    This allows us to hide the characters entered by the User, by using the Userform's textbox PasswordChar property (which I have set to an asterisk * )

    I have commented the vba code so you can follow what is going on.

    Now, remember, we are here to help you and answer your questions, but are not supposed to do all the work for you!

    ..the password for [Edit Existing Records] is zzzDATA
    ..the password for [ADD New Record] is zzzADD
    ..the password for [Edit Cheque Details] is zzzAHMAD

    zeddy
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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