Is there a way to limit MsgBox messages?

[JenniferMurphy]

I'm doing a little re-engineering of my personal add-in module. I am making changes to several functions. Some of these are incompatible with way the function used to work. I don't know where all of the function calls are (which workbooks and sheets), so I am putting MsgBox calls to tell the user to change the call. The problem is, if that function is called 100 or 1,000 times on a sheet, the user will have to clear 100 or 1,000 messages before they can make the changes.

Is there a better way?

Is there a way to limit number of MsgBox messages to just the first one in each sheet?

 

[Joe4]

Maybe just highlight them, and have a single message box at the end telling them there are errors to correct.
Or maybe have a sheet called something like "Error Log" and write all the pertinent details to that, which they can use to review and correct them.

 

[JenniferMurphy]

Maybe just highlight them, and have a single message box at the end telling them there are errors to correct.
Or maybe have a sheet called something like "Error Log" and write all the pertinent details to that, which they can use to review and correct them.

I don't understand what you mean by "highlight". Are you saying that I have the function change the color or fill or make it flashing, something like that?

I don't know how to generate a "single message at the end". How do I know when it's "the end"? Many of these functions do some sort of conversion. One of them converts a number from feet to feet and inches. It could be used in a table where it is called from 10, 100, or more cells. How can the UDF know when it's the last call?

Thanks

 

[JenniferMurphy]

Is there a way to search all workbooks for that UDF call? I tried the Windows XP search facility with "*.xls*" in the filename field and "=CvtFt2FtIn(" in the "Containing text" field. It found 0 hits even though I know there are several workbooks that use this UDF.

Is there another way to search all workbooks for a function call?

 

[Joe4]

OK. I think I may have misunderstood your original question.

I am putting MsgBox calls to tell the user to change the call.

How exactly are you doing this? If you have VBA code, can you post it here?

 

[danners430]

You could declare a Public string array in which each of the messages originally stored in a msgbox are kept. Then at the end of the procedure, have something like this:

Dim i As Long
Dim j As String


j = callarray(0) & vbNewLine


For i = 1 To UBound(callarray)
    
    j = j & callarray(i) & vbNewLine


Next i


MsgBox j

CallArray would initially be declared as CallArray(), and each time a new call is added it would be redeclared as CallArray(0 to UBound(CallArray) + 1), and the call stored in the new UBound(CallArray)

 

[JenniferMurphy]

You could declare a Public string array in which each of the messages originally stored in a msgbox are kept. Then at the end of the procedure, have something like this:

Dim i As Long
Dim j As String


j = callarray(0) & vbNewLine


For i = 1 To UBound(callarray)
    
    j = j & callarray(i) & vbNewLine


Next i


MsgBox j

CallArray would initially be declared as CallArray(), and each time a new call is added it would be redeclared as CallArray(0 to UBound(CallArray) + 1), and the call stored in the new UBound(CallArray)

Yep, I'll probably have to do something like that. Thanks for the template.