VBA Object Required Error

[dmqueen]

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:
<code>
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

</code>
Any help greatly appreciated!

 

[Norie]

Where/when do you get the error?

 

[dmqueen]

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!

 

[Norie]

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

Perhaps this line.

CurrentWorksheet.Range("a5").Select

 

[jerH]

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]

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?

 

[dmqueen]

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.

 

[jerH]

Once you fixed the return statement what happens?

 

[dmqueen]

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.

 

[jerH]

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<WORKSHEET name>").range("A5").select. If it's going to change, make sure you've activated the appropriate sheet and then use ActiveSheet.Range(.....