Getting text off buttons and error handling

[Loco]

I am still new to VBA and still learning as I go. I currently working on a new workbook that is a compilatin of 30+ others. The original documents are pairs of worksheets, one sheet is a report that is filled in, with some data checks, and the other sheet is a label with data from the report.

Most of the code I have is prewritten, I have just made minor changes, and made is so that I have one main code that pulls data from another page to recreates the original documents. The idea is to make more work now creating it whilst I have the time, so that later I can save time implementing any changes.

I have achieved everything I need the new version to do, and was adding error handling when I found an issue. The bulk of my code relies on text on the buttons, which when clicked call the macros for for navigation, and to select the correct data for the report. The code I have found achieves this, and is working is:

CurrentCat = ActiveSheet.Buttons(Application.Caller).Caption

Which is then used to in code such as:

CurrentCat = ActiveSheet.Buttons(Application.Caller).Caption
Sheets(CurrentCat).Visible = True
Sheets(CurrentCat).Select

Or

CurrentBatch = ActiveSheet.Buttons(Application.Caller).Caption
ChemRow = WorksheetFunction.Match(CurrentBatch, Columns(1), 0)

When stepping through, Application.Caller is assigned Error 2023, tripping the On Error line.

Does anyone have any suggestions on either a different way to get the text on buttons, or bypass the error?

 

[Anthony47]

Are you sure that when you get the error the macro had been started directly by the button?

 

[Loco]

Are you sure that when you get the error the macro had been started directly by the button?

The macro is deffinatly starting from the button, it wouldn't work otherwise, as the text is vital for changing pages. Without error catching it runs perfectly fine.

I've stepped through the code and its definatly Application.Caller that produces the error. I've looked into the Application.Caller function and it returns a string, integer or error. Its returning an error as the form buttons doesnt have a name to return.

I could leave the error handling out, but I plan to release this early for testing, before all the pages and data have been added. This will throw errors when changing pages or using match and not finding what its looking for and I would like to reduce the chance the end users will break it.

 

[Anthony47]

The Caller property help states that you get "The #REF! error value" (this is error 2023) when the caller is "The Macro dialog box (Tools menu), or any caller not described earlier"
Btw, your "buttons" are from the Modules toolbox, are they?

Now I understand that you plan to start the same macro from different buttons, each assigning a different scope to the macro, thus it's vital to rely on the button caption.
If you cannot reach a reliable condition then you might switch to using radio buttons or checkboxes associated to a single button.