Hi I am hoping that someone may have experienced the issue that I'm having and have a possible fix.

I'm have developed a Cost & Budget workbook for a client and in order to help eliminate user errors in the data I am protecting all the worksheets. Although I am allowing certain cells on the worksheets to be edited.

The issue that has come up is on the woksheets that contain the Cost Simulation and Downtime Simulation. When I run the Simulation excel displays the following
prompt: "The cell or chart you are trying to change is protected and therefore read-only." Realizing that RiskAmp simulation is changing the values in the cells and chart on the worksheet I am not UnProtecting the worksheet on activate using the following vb function:

'Receives the name of the worksheet.
'Unprotects the passed sheet.
Sub UnProtectSheet(ThisSheet As Worksheet)
Sheets(ThisSheet.Index).Unprotect
End Sub

The entire page is now unprotected and when I run the RiskAmp simulation I get the same excel prompt. "The cell or chart you are trying to change is protected and therefore read-only." When I click the 'Ok' button the prompt is displayed again when I click 'Ok' again the simulation starts and finishes.

Does anyone know why I'm getting these prompts even though the sheet has been unprotected and why the simulation will run but only after clicking the 'Ok' button twice?

Any help or ideas would be appreciated.

Thanks,
John Muncaster

Hi,

I think this is something that changed between Excel 2003 and 2007 - it used to be possible to use protected sheets, but no longer. (If you are seeing it in 2003, then please let me know).

What you're describing should theoretically work, so I'd like for us to try it. We'll do something similar and see how it goes - if you are comfortable sending us your spreadsheet, that would help as well.

If for some reason what you're doing simply doesn't work, you might try using hidden sheets - while they are not protected, at a minimum the user won't see them and therefore will be less likely to make accidental errors.

But as I said we'll experiment a bit and see what we can come up with.

Alex
support@riskamp.com

So we ran a couple of tests, and this should work. The issue is likely when you're calling the "unprotect" routine. If it doesn't get called at the right time, then you'll see the popups.

What I would suggest (assuming it fits into your spreadsheet) is running the simulation from VBA. That way, you can make sure to unprotect the relevant sheet before running the simulation, and re-protect it as soon as it is done.

You would do this using a button on the spreadsheet, or some other user interface - then when the button is clicked, run code something like

...

' hide this from view; otherwise, you'll see the
' pages flip back and forth (this is optional)

Application.ScreenUpdating = False

' unprotect the sheet

Sheets("Protected").Unprotect

' run a simulation (see [www.riskamp.com])

Application.ExecuteExcel4Macro ("RiskAMP_RunSimulation(500)")

' now when it's done, put protection back on

Sheets("Protected").Protect

' and reset visibility

Application.ScreenUpdating = True

...

That would run a simulation for 500 trials. In my example, the name of the protected sheet is "Protected"; that should be whatever your sheet or sheets are called.

Let me know if this makes sense.

Alex
support@riskamp.com

Hi Alex,

Thank you very much for your information and time you spent looking into this issue and sorry it took me so long to reply Im working on 2 big projects.

To answer your questions and statements.


1) "So we ran a couple of tests, and this should work. The issue is likely when you're calling the "unprotect" routine. If it doesn't get called at the right time, then you'll see the popups. "

Just so you know I am running excel 2003.
I am calling my 'uprotect' routine on sheet activate and then running RiskAmp from the toolbar. So I would have to assume that my sheet is unprotected before I run the RiskAmp Simulation but still get the error message. My quick fix (for the demo) was to just leave the 2 smiluation sheet unprotected.

-------------------------------------------------------------------------------

2) "What I would suggest (assuming it fits into your spreadsheet) is running the simulation from VBA. That way, you can make sure to unprotect the relevant sheet before running the simulation, and re-protect it as soon as it is done. "

If I were to run the RiskAmp Simulation from VB I would want to allow the user to choose how many trials they want to run.
With that said would this call work?
Application.ExecuteExcel4Macro ("RiskAMP_RunSimulation("A1")")
Where A1 would be the cell reference to the user entered trials.

I would also like to let the user choose whether to show the simulation or not show the simulation. What would be the calling function for this? Or is that even possible?

----------------------------------------------------------------------------------

Once again I appreciate your time..

Thanks,
John Muncaster

Hi,

No problem - we like to get this stuff in the forum in the event that it can help other people, so I'm happy to help. Let me answer some of this in turn:

> I am calling my 'uprotect' routine on sheet activate
> and then running RiskAmp from the toolbar. So I would
> have to assume that my sheet is unprotected before I
> run the RiskAmp Simulation but still get the error message.

This really depends on when the sheet gets activated (and the routine is run). It's likely that in your case it's not happening in the right order. You could probably figure out a way to make it work, but it's really wiser to use VBA here.

> If I were to run the RiskAmp Simulation from VB I would want
> to allow the user to choose how many trials they want to run.

You can do this, it's a little more complicated than your example. You have to get the number of trials as a number (not a cell name), but this is easy; in VBA, you can use something like

numberOfTrials = Range( "A1" ).value

which will get the value out of A1. It would be a good idea to check that it's a valid number, too.

> I would also like to let the user choose whether to show the
> simulation or not show the simulation.

You can do this the same way; the "run simulation" function which you call from vba takes a second parameter, which controls whether to hide updates. So if you wanted to run a simulation but show the updates, you would call something like

Application.ExecuteExcel4Macro ("RiskAMP_RunSimulation( 1000, FALSE )")

I've put together a simple example that ties all this together. You should be able to use it almost as-is, just changing the names of the various cells and sheets. Basically, when you click the button it

(1) gets the number of trials, from a cell

(2) gets whether to show screen updates, from another cell

(3) unprotects worksheets

(4) runs a simulation

(5) re-protects worksheets.


It's at [www.riskamp.com]. Let me know if this works for you.

Cheers,

Alex