Run Time Error 424 Object Required- VBA Start Stop

Question

I have a start stop time button excel 2010 sheet to keep track of how much I spend on tasks at work. It was working fine until this morning and I am getting a Run-time Error 424 Message. The code is below. Any help you can give will be greatly appreciated!!

Option Explicit

Private Sub btnStart_Click()
ActiveSheet.Unprotect
    Cells(Rows.Count, 5).End(xlUp).Offset(1) = Date
    Cells(Rows.Count, 6).End(xlUp).Offset(1) = Now
    Cells(Rows.Count, 7).End(xlUp).NumberFormat = "hh:mm"
    Cells(Rows.Count, 8).End(xlUp).Offset(1) = Environ("username")
    Me.btnStart.Enabled = False
    Me.btnStop.Enabled = True

End Sub

Private Sub btnStop_Click()
ActiveSheet.Unprotect
    Cells(Rows.Count, 7).End(xlUp).Offset(1) = Now
    Cells(Rows.Count, 7).End(xlUp).NumberFormat = "hh:mm"
    Me.btnStart.Enabled = True
    Me.btnStop.Enabled = False
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Public RunWhen As Double
Public Const cRunIntervalSeconds = 10 ' 10 seconds
Public Const cRunWhat = "The_Sub"  ' the name of the procedure to run


Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
End Sub
Sub The_Sub()
 [a1] = Now
   ' Call StartTimer to schedule the procedure again
   StartTimer
End Sub

When I press the button it says cannot run the macro"btnStart; The macro may not be available in this workbook or all macros may be disabled. — Laura Walker, Dec 22 '14 at 18:16
without knowing what line you receive the error on all I can suggest is that you qualify your Cell Range objects, use Activesheet.Cells — Sorceri, Dec 22 '14 at 18:22
Is your button a Form button or an ActiveX button? If it is a Form button, what macro is assigned to it? — TheEngineer, Dec 22 '14 at 18:27
The macro `btnStart` indeed doesn't exist. `btnStart_Click` does. I think that's the problem. — Matteo NNZ, Dec 22 '14 at 18:29
@LauraWalker Could this be the same problem as discussed here? (with fix provided): [Microsoft Excel Active-X Controls disabled](http://stackoverflow.com/questions/27411399/microsoft-excel-activex-controls-disabled) — Ron Rosenfeld, Dec 22 '14 at 18:34
With apologies I'll state that the excel sheet i'm having issues with has the code from the FIRST snippet and it never allowed me to click the buttons — Laura Walker, Dec 22 '14 at 18:45

score 0 · Answer 1 · answered Dec 22 '14 at 19:13

0

As noted in the comments to your question, since your button is a Form button, I suspect that the macro that is assigned to it is not correct or not able to be accessed. If you are trying to run btnStart_Click() from your Form control, you will need to remove the Private designation from the Private Sub btnStart_Click() line. The same goes for the btnStop_Click() sub.

answered Dec 22 '14 at 19:13

TheEngineer

1,185
1
10
18

Thank you for your help but I removed it and it didn't work :-( – Laura Walker Dec 22 '14 at 19:15
The code now reads: Sub btnStart() ActiveSheet.Unprotect Cells(Rows.Count, 5).End(xlUp).Offset(1) = Date Cells(Rows.Count, 6).End(xlUp).Offset(1) = Now Cells(Rows.Count, 7).End(xlUp).NumberFormat = "hh:mm" Cells(Rows.Count, 8).End(xlUp).Offset(1) = Environ("username") Me.btnStart.Enabled = False Me.btnStop.Enabled = True End Sub Sub btnStop() ActiveSheet.Unprotect Cells(Rows.Count, 7).End(xlUp).Offset(1) = Now Cells(Rows.Count, 7).End(xlUp).NumberFormat = "hh:mm" Me.btnStart.Enabled = True Me.btnStop.Enabled = False End Sub – Laura Walker Dec 22 '14 at 19:19
Your `btnStart_Click` and `btnStop_Click` macros appear to be in a worksheet. Are they in the same worksheet as the actual buttons? – TheEngineer Dec 22 '14 at 19:19
Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub – Laura Walker Dec 22 '14 at 19:20
it's not allowing me to edit and see what macros were assigned to each button. this was working fine on Friday! – Laura Walker Dec 22 '14 at 19:24
So I tried to add another button and assign it to a Start/Stop Macro. That worked fine. I clicked the button and got a Compile Error - Expected Function or Variable and then it highlighted Sub btnStart() of the code – Laura Walker Dec 22 '14 at 19:27
Try changing the name of the sub to something else and reassigning the macro. Occasionally you can get that error if the sub is named the same as a module or another sub that was automatically created at some point. – TheEngineer Dec 22 '14 at 19:33
Thank you! I'll try that. Can you talk to me like I'm two and tell me how that can be done? – Laura Walker Dec 22 '14 at 19:38
I just meant to change `Sub btnStart_Click()` to something like `Sub StartButton()` and then assign the StartButton macro to the Form control. The name doesn't have to include `_Click` for a Form control. It would help if you could upload a copy of your workbook for me to take a look at. – TheEngineer Dec 22 '14 at 19:43
@LauraWalker Did that work for you? If not, can you upload a copy of your workbook somewhere? – TheEngineer Dec 23 '14 at 16:16
@LauraWalker Were you able to fix this issue? – TheEngineer Jan 29 '15 at 16:03

Sara · Answer 2 · 2015-01-13T20:04:30.820

Good afternoon, I had a similar issue with a form control in an Excel spreadsheet that was working just fine one day, and the next day, it could no longer "find" the form control and displayed the same error.

After doing a Google search within the past 24 hours I located a post that points the finger at December security updates.

http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2014/12/11/forms-controls-stop-working-after-december-2014-updates-.aspx

I know this isn't very helpful but it's something.

Edit/Update: Worked with my PC Tech guru and he was able to resolve the issue by re-registering two DLL's and deleting .EXD files in the user profile. The .EXD files get recreated (and thusly recompiled) the next time Excel opens.

Here are the DLL's:

c:\windows\syswow64\COMCTL32.OCX

c:\windows\syswow64\MSCOMCTL.OCX

Good luck!

Run Time Error 424 Object Required- VBA Start Stop

2 Answers2

Linked