Object required Error

[jsambrook]

Good Morning all!
I am trying to launch a form, which I had working successfully and cannot work out for the life of me what I have done to break it. I cant even get it to launch the form, it comes up with Object Required and it highlights the row NewStaffForm.Show of the macro

Private Sub CommandButton1_Click()
NewStaffForm.Show
End Sub

My Form is definitely called NewStaffForm
Anybody any ides what I'm doing wrong?

 

[Ruddles]

Where is CommandButton1? On your worksheet? On another form?

Do you have anything in the form's _Initialize or _Activate event handlers?

What did you change since it last ran successfully?

Can you Run it directly in VBA? (Select the form and press F5.)

If you can, place this in the form's code module and Run it again:-

Private Sub UserForm_Initialize()
MsgBox "My name is """ & Me.Name & """ (" & Len(Me.Name) & " characters)"
End Sub

Is everything as expected?

 

[jsambrook]

Code is in the form area. the button is on the worksheet "Summary"

I have this in initialize.

Private Sub UserForm_Initialize()
SName.Value = ""
With CmbLocation
.AddItem "Baildon"
.AddItem "Rawdon"
.AddItem "Site"
End With
With CmbType
.AddItem "Permanent"
.AddItem "Agency"
.AddItem "Sub Contract"
End With
End Sub


If I could remember what I did to it I could reverse it! I'm pretty sure I just changed some of the protection. Wouldnt have thought it would impact it like this.

Cant get it to run from VBA, it just keeps saying runtime error 424 object required

 

[Ruddles]

And you're sure you have objects called SName, CmbLocation and CmbType on your form?

Your code works fine here.

 

[jsambrook]

Yeah, I've checked at least a hundred times!
It keeps crashing on the form.show. Any ideas what could cause that?

 

[Norie]

That happens when there's a problem in the Initialize event.

If you step through with F8 you should find the specific line if code where the error occurs.

If that doesn't work goto Tools>Options and on the General tab select Break On All Errors.

Now when you show the form debug should goto the 'real' line of code causing the error.

 

[jsambrook]

I've managed to get it past the initial call of the form, and insert data but its crashing on the point shown below saying the same error.


Private Sub ClickOK_Click()
'copy template worksheet
Sheets("Template").Select
Sheets("Template").Copy Before:=Sheets(4)
Sheets("Template (2)").Select
'insert location and type
Range("B4").Select
Active.Cell = CmbSType.Value <======== Crashes here
Range("B2").Select
Active.Cell = CmbSLocation.Value
'Rename and protect
Sheets("Template (2)").Name = SName.Value
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="Janni"

Sheets("Summary").Select
ActiveSheet.Unprotect Password:="Janni"
Rows("8:9").Select
Selection.Copy
Range("A65536").End(xlUp).Select
ActiveCell.Offset(2, 0).Select
ActiveSheet.Paste
Selection.EntireRow.Hidden = False
ActiveCell.FormulaR1C1 = SName
Range("C8:F9").Select
Rows("8:9").Select
ActiveSheet.Protect Password:="Janni"

End Sub

 

[Norie]

There's a . between Active and Cell on that line of code, simply remove it.

If you add Option Explicit at the top of all your modules you can help prevent something like that happening again.

 

[Ruddles]

If you add Option Explicit at the top of all your modules
you can help prevent something like that happening again.

Just in case anyone else misses this piece of excellent advice!

 

[jsambrook]

Sorry for being a total dunce, but how do I add option explicit? simply write it?