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!

 

[dmqueen]

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(.....

I like your idea, but as the file has 57 sheets in it and I want to use the same code regardless of which sheet they open, I don't think it'll work. Thanks for trying though!
</worksheet>

 

[jerH]

You could do something like

dim wks as worksheet

application.screenupdating = false

for each wks in activeworkook
     if instr(1, wks.name, "some text that identifies the data sheets")
         wks.activate
         activesheet.range etc.
    end if
next wks

That would loop through all the sheets and do what you want on any sheet whose name matched your criteria

 

[jerH]

Alternatively, if the user is going to select a single sheet and run the macro only for that sheet then the activesheet.range approach should work

 

[Norie]

Change CurrentWorksheet to ActiveSheet.

 

[jerH]

For some reason I can't edit my posts...

The line of code above that reads

for each wks in activeworkook

should be

for each wks in activeworkook.sheets

 

[dmqueen]

Alternatively, if the user is going to select a single sheet and run the macro only for that sheet then the activsheet.range approach should work

I have copied the button onto every sheet and assigned the same macro to it as each sheet essentially does the same thing.

I tried replacing CurrentWorksheet with ActiveSheet.range and it gave me the Object does not support this property or method Runtime error 438 when I ran and it jumped to
a code window with
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Apparently expecting me to write the subroutine.
Isn't there a way to explicitly write in the path to the Range object? Even though I should'nt have to.
I have Option Explicit at the top. It's acting like I'm missing an Include file, if this was another language I'd be sure that was it.

 

[dmqueen]

Good idea Norie, but it didn't work. TY though.

 

[jerH]

Weird. SelectionChange is an event handler that gets called every time you make a new selection. It's perfectly okay for it to be blank if you don't want anything special to happen every time you select a new cell/range. Can you post your current code (the code giving you this error) and wrap it in code tags?

 

[dmqueen]

Found it! I had Active instead of Activate on a line. It would have been so nice if the compiler had told me the correct line, but we can't have it all, right? Thanks everyone for all your help!

 

[Norie]

When you run code and get a run-time error then the offending line should be highlighted.

When you compile code and get a compile error the offending line should get highlighted.