Page 1 of 2 12 LastLast
Results 1 to 10 of 20
Like Tree4Likes

VBA Object Required Error

This is a discussion on VBA Object Required Error within the Excel Questions forums, part of the Question Forums category; I finally got my code to compile, but can't run it due to an 'Object Required' Error. Runtime 424 Error ...

  1. #1
    Board Regular dmqueen's Avatar
    Join Date
    Aug 2014
    Posts
    53

    Question VBA Object Required Error

    I finally got my code to compile, but can't run it due to an 'Object Required' Error. Runtime 424 Error from the worksheet button.
    I think it's because I need to us a SET statement when assigning a value to an object, but since I'm not using an array I'm not sure where it needs it! code below:

    Sub fInputPart()
    'col A
    'add a new row
    'get last PartNo.
    'add 1
    'generate new part no.
    CurrentWorksheet.Range("a5").Select
    ' goto the top entry ready to insert the new entry
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(2, 0).Active
    If ActiveCell.Value = "=" Then
    Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    Dim NewRowNum As Integer
    NewRowNum = ActiveCell.Row
    Range("A" & NewRowNum).Select
    Else
    ActiveCell.Offset(1, 0).Activate
    Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A" & ActiveCell.Row).Select
    End If

    'Goto last part number entry
    ActiveCell.Offset(1, 0).Activate
    Dim LastPartNO As Integer
    'Get the last part number here!!
    Selection.End(xlUp).Select

    Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A" & ActiveCell.Row).Select
    ''Put the new part number here!!
    ActiveCell.Value = fGenerateNextPartNumber(ActiveCell.Value)
    'go to next col
    'col B
    Range("B" & ActiveCell.Row).Select
    'verify entry was made
    'go to next col
    'while there are columns for data entry (has column title) verify entry was made in last column,
    'error msg if not,
    'go to next column if made
    While (NewRowNum & ActiveCell.Column <> "")
    'while there are columns left for entry: there is a column header
    'go to the next column for entry that is active and has a width not equal to 1
    'verify entry was made in last column: holler and stop if not: continue if made
    If ActiveCell.Offset(0, -1) = Null Then
    Call MsgBox("Please enter/select a value in the previous column! ", vbExclamation, Application.Name)

    Else
    'if inactive(width=1, jump it, else stop for entry
    If ActiveCell.Offset(0, 1).ColumnWidth = 1 Then
    ActiveCell.Offset(0, 2).Activate
    Else: ActiveCell.Offset(0, 1).Activate
    End If
    End If
    Wend
    Call MsgBox("Entry Complete, thank you! Don't forget to save when done! ", vbInformation, Application.Name)
    End Sub
    Public Function fGenerateNextPartNumber(LastPartIn As String) As String
    Dim LastPartNO As String
    LastPartNO = LastPartIn
    'LastPartNo = ActiveCell.Value
    Dim NewStrPartNo As String
    Dim strPartNo As String
    strPartNo = ActiveSheet.Name()
    Dim strSeperator As String
    Dim strLastSeqPartNo As String
    strLastPartNo = (Right(LastPartIn, 4))
    'debugging
    Call MsgBox(LastPartNO)

    Dim strNewSeqPartNo As String
    Dim intNewSeqNo As Integer

    Dim intLastSeqNo As Integer
    'handle special case separators HERE!
    intLastSeqNo = CInt(strLastPartNo)
    intNewSeqNo = LastSeqNo + 1
    'debugging
    Call MsgBox(strPartNo)
    If strPartNo = "180" Or strPartNo = "300" Or strPartNo = "310" Or strPartNo = "320" Or strPartNo = "330" Or strPartNo = "970" Or strPartNo = "681" Or strPartNo = "981" Then
    StrSeparator = "-1-"

    Else: StrSeparator = "-0-"
    End If

    NewStrPartNo = strPartNo + StrSeparator + CStr(intNewSeqNo)
    fGenerateNextPartNo = NewStrPartNo

    End Function


    Any help greatly appreciated!

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    70,707

    Default Re: VBA Object Required Error

    Where/when do you get the error?
    dmqueen likes this.
    If posting code please use code tags.

  3. #3
    Board Regular dmqueen's Avatar
    Join Date
    Aug 2014
    Posts
    53

    Default Re: VBA Object Required Error

    When I try and run the code/ push the button. I don't know where in the code, how can I tell. I can't seem to step into it. It just dies. I haven't used VBA a lot. Or it's compiler. :O
    I've tried explicitly stating Application. on the worksheet references but it still wasn't enough. To make things worse now my compiler thinks my function return statement is a variable! Argh!
    Last edited by dmqueen; Aug 12th, 2014 at 01:29 PM.

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    70,707

    Default Re: VBA Object Required Error

    Surely when you get the 'Object required...' error something is highlighted.

    Perhaps this line.
    Code:
    CurrentWorksheet.Range("a5").Select
    dmqueen likes this.
    If posting code please use code tags.

  5. #5
    Board Regular
    Join Date
    Dec 2008
    Posts
    131

    Default Re: VBA Object Required Error

    In the VBA editor, place your cursor at the start of the sub and hit F8 to begin stepping into the code. Each time you hit F8 it should execute one line, until you hit the error....
    dmqueen likes this.

  6. #6
    Board Regular dmqueen's Avatar
    Join Date
    Aug 2014
    Posts
    53

    Default Re: VBA Object Required Error

    If I try to run it from the code window, yes the first line referencing the current worksheet is highlighted. I can't tell now what line it is when I run it from the worksheet button. Now It's hung up on my function return statement as an undeclared variable. Maybe I should stick to C?

  7. #7
    Board Regular dmqueen's Avatar
    Join Date
    Aug 2014
    Posts
    53

    Default Re: VBA Object Required Error

    Yes, that's the line. What is wrong with it?
    If I preface it with Application. it will compile and then gives me the "Object doesn't support this property or method" error 438.
    Last edited by dmqueen; Aug 12th, 2014 at 03:49 PM.

  8. #8
    Board Regular
    Join Date
    Dec 2008
    Posts
    131

    Default Re: VBA Object Required Error

    Once you fixed the return statement what happens?

  9. #9
    Board Regular dmqueen's Avatar
    Join Date
    Aug 2014
    Posts
    53

    Default Re: VBA Object Required Error

    It still doesn't like the CurrentWorksheet.Range("a5").Select line. From the Code window I get an Object Required Error; from the Worksheet button I get an Object Doesn't Support This Method Error.

  10. #10
    Board Regular
    Join Date
    Dec 2008
    Posts
    131

    Default Re: VBA Object Required Error

    As you plan to use this, will the worksheet you're operating on stay fixed? If so, instead of CurrentWorksheet.Range("a5").Select use sheets("Worksheet Name").range("A5").select. If it's going to change, make sure you've activated the appropriate sheet and then use ActiveSheet.Range(.....
    Last edited by jerH; Aug 12th, 2014 at 04:08 PM.

Page 1 of 2 12 LastLast

Tags for this Thread

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
  •  


DMCA.com