I'm asking for some advice before coding some SmartView macros. We want to setup a SmartView tab in a workbook and send it to users so they can fill it in and submit the data to a Hyperion Planning database.
We want to have excel formulas on certain rows in the sheet that do simple totaling for accounts and periods. We would ideally like those formula cells to be protected so they can't be overwritten by the users.
What we've found is that when you protect a sheet in SmartView, users can't login to the database until the sheet is unprotected. Users also can't submit or refresh data from a protected sheet.
I'm planning to code some VB macros attached to buttons on the sheet to do submits and refreshes that would include un-protecting and re-protecting the sheets. What should I do for logging in? Another button on the sheet? We have multiple planning databases and users login to different databases, so any login macro would need to prompt for the server/app/database in addition to userid/password.
Any advice before I do this would be greatly appreciated. Thanks.
Use the HypConnected to determine if the sheet is connected.
If it is, then refresh or submit (depending on the purpose of the function).
If it's not, then call a connect function to connect, then refresh or submit
If you need to connect more than one worksheet, it might make sense to get the users id/password using an Excel userform so that you can use them for subsequent connections.
One word of warning.. Excel workbook apps can result in more user support than expected, use a planning form if you can.