Macro stopped running

[matthewlouis]

When I open worksheet, there is a macro that is supposed to run. When I hit Alt F11, the box is grayed out with no code.

I don't code, just a formula guy, this was a macro someone here on the board helped me with. So I am dense on coding. But this is a great macro.

When I open the worksheet, I am not asked anything. I went to File>Info> and the Security Information is not restricting (I trust the source).

What happened to the macro and why is the box grayed out and no code is there?

Thanks in advance!

 

[Joe4]

If you add this VBA code to the "ThisWorkbook" module, I think it will do what you want:

Private Sub Workbook_Open()
    Call clicky
End Sub

Then, every time you open the workbook, as long as VBA/Macros are enabled, your "clicky" procedure will run automatically.

 

[matthewlouis]

OK, I added it. To get it to run, what do I do?

 

[Joe4]

OK, I added it. To get it to run, what do I do?

Close the workbook, then re-open it.
As I metioned in my previous post:

Then, every time you open the workbook, as long as VBA/Macros are enabled, your "clicky" procedure will run automatically.

 

[matthewlouis]

OK, was out for a bit, doing it now.

 

[matthewlouis]

Closed, opened, it said ambiguos name detected clicky

 

[Joe4]

Closed, opened, it said ambiguos name detected clicky

That is because I believe that you said that you copied "clicky" and pasted it in a different module.
So it now appears in two places in your workbook. Just delete one of them - you don't need it in two places.

 

[matthewlouis]

OK, let me try that.

 

[matthewlouis]

Still not working. Look at the attached.

 

[Joe4]

I am guessing that is because you have a multi-sheet workbook, and since none of your the ranges in your code have sheet qualifications, it is simply running against whatever sheet it opens up to (and if it is not the correct sheet, you may get errors!).

Which sheet does this data (cell K2) reside in?
You can fix it by adding a line that tells your VBA code which sheet to run against, i.e.

Sub clicky()
   Sheets("YourDataSheetNameHere").Select
'    Rest of your code below...

 

[matthewlouis]

OK, let me add that line.

Once added, again, do I close the sheet and reopen and that will run the macro?