Error Handling for Macros, while adding a dimension.

[#1]

Hi All,

I'm have a dynamic feature for my client, were I have an Input Box which lists fields and if the user selects any of them, then that field is added to the pivot table.

Now my client says that this report is gonna have Section Access and certain fields will be restricted to certain users of the report. Now say if a particular user who does not have access to a particular field that is in the list of Input Box and he selects it at the Input Box, then the macro fails.

I need now to able to either catch this error and inform the user that they donot have access to the field or dynamically vary the list in the Input Box.

I would prefer the later option. But any solution is looked forward and appreciated.

Thanks in advance.

Br,

Prasanna.

[#2 · Accepted solution]

If you are using VBS for macros, you can catch errors using 'on error resume next'.

This code for example checks for a file existence and if it doesnt then creates it, rather than the error being reported. The key thing is to turn back on standard error handling after the statement being monitored!

on error resume next

set logFile = fso.GetFile(vExportLog)

if err <> 0 then ' file does not exist
on error goto 0
set logFile = fso.CreateTextFile(vExportLog)
set logFile = fso.GetFile(vExportLog)
else
on error goto 0
end if

You could use this technique with msgbox to achieve what you are after.

Regards,

Gordon

[#3]

Hi Prasanna,

You can dynamically set predefined values. Make the necessary expression and concat it with ';' delimiter.

To display the error message, constraints tab of input box has "Error Message" which again is an expression box. Using this functionality over macros is preferable as its will not give additional issues while using thru access point.

Hope this helps,

Kiran.

[#4]

Hi Gordon,

Thanks for your time and help. It did work. Thank you once again.

Hi Kiran,

Thanks for your time.

I was not able to dynamically vary the content, though I was able to get the needed values in a variable dynamically.

But concatenating it with ';' creates a problem. So I kind of took Gordan's solution.

But I really thank you for the time you have spent.

Br,

Prasanna

[bumin]

Hi,

when you say goto 0

how do you enter 0