PDA

View Full Version : [SOLVED] Cell displays an error unless the column is double clicked



Beatrix
05-22-2017, 09:14 AM
Hi Everyone

I have got a spreadsheet with a vba script which runs data and protects the control worksheet with a password at the end.However a certain cell with a specific formula in H2 displays error #VALUE!. When I unprotected the worksheet and double clicked on column H to resize then it shows the value.

I added a line to script to change the width or auto fit or update the formula or
Application.Calculation = xlCalculationAutomatic however nothing changed.

Any suggestions to fix this problem would be much appreciated.

Many Thanks
B.

Bob Phillips
05-22-2017, 01:52 PM
Post the workbook so we can see the problem.

Beatrix
05-23-2017, 04:39 AM
Thanks very much for your reply xld.

I attached the test workbook. When you press the button in Control sheet it creates a new month but H2(Named Range:Resident) displays error value unless you unprotected the worksheet and double click on column H.

Is there a solution for this please?

password: test

Cheers
B.

mdmackillop
05-23-2017, 05:04 AM
Add "Calculate" to force update to external file

RptName = myPath & "/test " & Format(Date, "yyyy mm dd ") & RptResident & ".xls"
Calculate
wb.SaveAs RptName

Beatrix
05-23-2017, 05:23 AM
Thanks very much mdmackillop :)
It's working now :)

I am just learning so can I ask the logic here please? I understand why we use calculate bit but why we are putting it after rpt name?

There was also a problem in E6 which the formula was not refreshing now this is fixed too.

Process creates an external file however the problem was occurring in control sheet in original file so that's why I didn't understand why we are putting the Calculate after RptName.

If it's difficult to explain that's fine.

Thanks again.
B.

mdmackillop
05-23-2017, 05:28 AM
It possibly isn't critical. I used Formula Evaluate tool on the formula in H1 to see the issue. I didn't test to see exactly when this was required but it seemed it should go somewhere at the end.

Beatrix
05-23-2017, 06:18 AM
Many Thanks, it makes sense.