vba 在公式栏中隐藏公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16684297/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
hiding formulas in formula bar
提问by kazSone
When I used, in visual basic, the codes shown below, the HYPERLINKpart appears in the formula bar, in the worksheet. I just want the "TextHere"to display in the formula bar.
当我在visual basic中使用下面HYPERLINK显示的代码时，该部分出现在bar工作表中的公式中。我只想"TextHere"显示在公式栏中。
What additional codes can I add? I've tried to hide it by changing its properties in the protection tab, but it does not allow me to edit the TEXT anymore. I would like it to still be edit-friendly.
我可以添加哪些附加代码？我试图通过在保护选项卡中更改其属性来隐藏它，但它不允许我再编辑文本。我希望它仍然是编辑友好的。
Sub IndexingSheets()
Sheets(1).Range("A1").Formula = _
"=HYPERLINK(""#" & ThisWorkbook.Sheets(2).Name & "!A2"", ""TextHere"")"
End Sub
回答by
How to hide a formula from the formula bar
Let me demonstrate two ways of hiding formulas from the formula bar
如何在公式栏中
隐藏公式 让我演示两种从公式栏中隐藏公式的方法
No1.
1号。
To hide the formula from the formula bar you have to set the HiddenFormulaproperty the Rangeobjectto True
It will only work while the worksheet is protected
So the trickhere is:
-> select all cells and unlock them for editing
-> select cells you want to hide formulas from and lock them
-> protect the sheet
要从公式栏中隐藏公式，您必须HiddenFormula将Range对象的属性设置为True
它仅在工作表受保护时才有效
所以这里的技巧是：
-> 选择所有单元格并解锁它们进行编辑
-> 选择要隐藏的单元格公式并锁定它们
-> 保护工作表
Select all cells and unlock them for editing
-> select all cells, right click anywhere to format cells. Go to the Protectiontab and unselect Locked
Select cells you want to hide formulas from and lock them
-> select A1, right click, go to Protectiontab and select Lockedand Hidden
Protect the sheet
-> Click the Reviewtab, then Protect Sheetand OK ( no password necessary )
Now notice, you can still edit any cell except the A1. Look at the formula bar - There is no formula! Its HIDDEN!
选择所有单元格并解锁以进行编辑
-> 选择所有单元格，右键单击任意位置以设置单元格格式。转到Protection选项卡并取消Locked
选择选择要从中隐藏公式的单元格并锁定它们
-> 选择A1，右键单击，转到Protection选项卡并选择Locked并Hidden
保护工作表
-> 单击Review选项卡，然后Protect Sheet确定（不需要密码）
现在注意，您仍然可以编辑除A1之外的任何单元格。看公式栏——没有公式！它的隐藏！
这是一个 VBAVBA解决方案：
Sub HideTheFormula()
Dim ws As Worksheet
Set ws = Sheets(1)
Call IndexingSheets
Call Setup(ws)
Call ProtectSheet(ws)
'Call UnprotectSheet(ws)
End Sub
Sub IndexingSheets()
Sheets(1).Range("A1").Formula = _
"=HYPERLINK(""#" & ThisWorkbook.Sheets(2).Name & "!A2"", ""TextHere"")"
End Sub
Sub ProtectSheet(ByRef ws As Worksheet)
'ws.Protect userinterfaceonly:=True
ws.Protect
End Sub
Sub UnprotectSheet(ByRef ws As Worksheet)
ws.Unprotect
End Sub
Sub Setup(ByRef ws As Worksheet)
With ws.Cells
.Locked = False
.FormulaHidden = False
End With
ws.Range("A1").Locked = True
ws.Range("A1").FormulaHidden = True
End Sub
No2.
2号。
With a new spreadsheet insert this code in a new VBE(ALT+F11) Module. Execute the Mainmacro from the View Macroswindow (ALT+F8)
使用新的电子表格将此代码插入新的VBE( ALT+ F11) 中Module。Main从View Macros窗口 ( ALT+ F8)执行宏
Sub Main()
With Range("A1")
.Formula = "=1+1"
End With
With Range("A2")
.Formula = "=1+1"
.Value = .Value
End With
End Sub
After execution have a look at the sheets ranges A1and A2
When A1gets selected and you look at the formula bar you can see the formula =1+1,
however when you select A2even though you have put a formula in the cell, it has been evaluatedand hiddenso now it displays the evaluated value (how cool!)
The same principle applies when you are pulling a value from a closed workbook, for instance
执行后看看床单范围A1和A2
当A1被选中，你看看公式栏中可以看到公式=1+1，
然而，当你选择A2，即使你已经把该单元格的公式，它已经evaluated和隐藏所以现在显示器该评估值（如何的酷！）
当你在同样的原则也适用于从关闭的工作簿拉着一个值，例如
Sub PullValueFromAClosedWorkbooksRange()
With Range("A1")
.Formula = "='C:\Users\admin\Desktop\[temp.xlsm]Sheet1'!A1"
.Value = .Value
End With
End Sub
