Show Values instead of formulas when you click on any cell that contains formula

[hanan_ak]

Hi everyone,

I have a workbook with multiple sheets. All sheets contains formulas and static values. I have already protected the sheets but I don't want to hide the formula in the formula bar. What I am trying to achieve is when ever someone is using my workbook and when they click on any cell which, contains formulas in the sheet, instead of showing the formula in the formula bar, it should show the calculated result (value).

If anyone could help me in this regard. I have searched a lot but could not get hold on any thing that could resolve my issue.

Thanks in advance.

Hannah

 

[JLGWhiz]

Not sure exactly what you are referring to, but if you mean display the value in the formula bar, you would need to talk to Microsoft about that. If the formulas are being displayed on the worksheet then you can go to File|Options|Advanced|Display Options for This Worksheet and uncheck the box for displaying formulas instead of their calculated results.

 

[hanan_ak]

Thanks four reply. Actually

in the first image when select the cell, you can see there is a formula. I would like to have a VBA code, that it should not show the formula in the formula bar. It should hide the formula in the background and only show the value instead.

I know in excel we can hide the formula and protect the sheet. So, when we select the formula, it does not show formula. but all I want is, instead of hiding, it should only show the calculated result when the user select the cell containing formula in the formula bar.

1) https://drive.google.com/file/d/1qieveXka_ySLz9baG0qpqocmciyLhSlO/view?usp=sharing

2) https://drive.google.com/file/d/1JkYqxJYI4fkLt-o2yaTdq661m5CoRm-J/view?usp=sharing

Hope, I managed to explain well.

 

[JLGWhiz]

Thanks four reply. Actually

in the first image when select the cell, you can see there is a formula. I would like to have a VBA code, that it should not show the formula in the formula bar. It should hide the formula in the background and only show the value instead.

I know in excel we can hide the formula and protect the sheet. So, when we select the formula, it does not show formula. but all I want is, instead of hiding, it should only show the calculated result when the user select the cell containing formula in the formula bar.

1) https://drive.google.com/file/d/1qieveXka_ySLz9baG0qpqocmciyLhSlO/view?usp=sharing

2) https://drive.google.com/file/d/1JkYqxJYI4fkLt-o2yaTdq661m5CoRm-J/view?usp=sharing

Hope, I managed to explain well.

The formula bar display features cannot be changed with VBA. If there is a formula in the selected cell, the formula bar displays the formula. If there is only a value in the cell then the formula bar displays a value. You cannot hide the formula to display only a value in the formula bar and then recover the formula. To display only the value in the formula bar, the cell must contain only the value. You can change the formula to a value by using the Copy/Paste Special Values option, but your formula is replaced with the value, not hidden.