"Method or Data Member not found" - VBA, working fine last week, now it's broke!

[RockandGrohl]

Story of my life.

When I run this code, I get this error:

Sub filterrun()

Call ControlPanel.SignOffFilter_Click




End Sub

ControlPanel definitely exists and SignOffFilter definitely exists.

Any ideas what's going on?

Private Sub SignOffFilter_Click()

Dim lastrow As Long


Worksheets("Bullets").Activate
lastrow = Cells(Rows.Count, "A").End(xlUp).Row


Cells.EntireColumn.Hidden = False
Cells.EntireRow.Hidden = False
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0


If ActiveSheet.AutoFilterMode = False Then
ActiveSheet.AutoFilterMode = True
End If

 

[RoryA]

That click routine is private, so you can't call it with Call from outside the object. It sounds like the code should be in a normal module and called from both locations.

 

[RockandGrohl]

That click routine is private, so you can't call it with Call from outside the object. It sounds like the code should be in a normal module and called from both locations.

Hi Rory, I've changed it to Public and it appears to have worked, thanks - any idea why this was working last week? Nobody else touches the VBA apart from me and I haven't been in the VBA for weeks, the file is used most days of the week by staff doing simple commands (clicking a button or filling in a cell)

Cheers.

 

[RoryA]

Nope - that should never have worked in that format.

 

[RockandGrohl]

Nope - that should never have worked in that format.

That's weird because I can promise you it has always been Private Sub... Is there a circumstance it can change from Public to Private?

 

[RoryA]

Only if someone changes it. The only way that should have worked before is if the calling routine used to be in the same module.

 

[RockandGrohl]

Only if someone changes it. The only way that should have worked before is if the calling routine used to be in the same module.

Yeah we just opened a backup from the 30th March and it's showing the same error.

This is weird because there's two people in this whole company that have macro editing enabled, me and the IT admin, neither of us have touched this file's VBA -ever- the chap who wrote it left.

The error happens every time a user clicks the control panel (a userform)

This control panel was opening today in the morning, but not after about 10am.

It's also been opened 10x a week, every week for the last 2 years.

AFAIK the macro has never been changed. The two people in the whole company who even know what "ALT+F11" do haven't touched it.

HOW WEIRD!

To be honest, our entire backend system is built around various heavy-macro spreadsheets. Interdepartmental communication, etc, it's all done on Excel. It's bonkers. So many problems, so many sheets just spontaneously corrupt or break in some fashion. Absolutely insane cost-saving.