vba 在单元格背景中插入文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18197130/
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
Insert text into the background of a cell
提问by Scott Jones
I am looking for a way to insert text into the background of a cell, so that I can still enter numbers on top of that text - similar to a watermark except for an individual cell. Any ways to do this, preferably without using a macro (but open to these solutions as well)?
我正在寻找一种将文本插入单元格背景的方法，以便我仍然可以在该文本的顶部输入数字 - 类似于水印，但单个单元格除外。有什么方法可以做到这一点，最好不使用宏（但也对这些解决方案开放）？
回答by user3357963
Similar to Andrews post, this is the VBA version which formats the shape correctly and also allows direct selecting of cells.
与 Andrews post 类似，这是 VBA 版本，它可以正确设置形状的格式并允许直接选择单元格。
Code MODULE:
代码模块：
Sub watermarkShape()
Const watermark As String = "watermark"
Dim cll As Range
Dim rng As Range
Dim ws As Worksheet
Dim shp As Shape
Set ws = Sheet1
Set rng = ws.Range("A1:F10") 'Set range to fill with watermark
Application.ScreenUpdating = False
For Each shp In ws.Shapes
shp.Delete
Next shp
For Each cll In rng
Set shp = ws.Shapes.AddShape(msoShapeRectangle, 5, 5, 5, 5)
With shp
.Left = cll.Left
.Top = cll.Top
.Height = cll.Height
.Width = cll.Width
.Name = cll.address
.TextFrame2.TextRange.Characters.Text = watermark
.TextFrame2.TextRange.Font.Name = "Tahoma"
.TextFrame2.TextRange.Font.Size = 8
.TextFrame2.VerticalAnchor = msoAnchorMiddle
.TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenter
.TextFrame2.WordWrap = msoFalse
.TextFrame.Characters.Font.ColorIndex = 15
.TextFrame2.TextRange.Font.Fill.Transparency = 0.35
.Line.Visible = msoFalse
' Debug.Print "'SelectCell (""" & ws.Name & """,""" & cll.address & """)'"
.OnAction = "'SelectCell """ & ws.Name & """,""" & cll.address & """'"
With .Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.Transparency = 1
.Solid
End With
End With
Next cll
Application.ScreenUpdating = True
End Sub
Sub SelectCell(ws, address)
Worksheets(ws).Range(address).Select
End Sub
UPDATE:
更新：
the example below assigns a watermark of the cell address to odd rows and leaves the even rows as the constant watermark. This is an exaple based on my comment that any cell can be assigned any watermark text based on whatever conditons you want.
下面的示例将单元格地址的水印分配给奇数行，并将偶数行保留为常量watermark。这是一个基于我的评论的示例，即可以根据您想要的任何条件为任何单元格分配任何水印文本。
Option Explicit
Sub watermarkShape()
Const watermark As String = "watermark"
Dim cll As Range
Dim rng As Range
Dim ws As Worksheet
Dim shp As Shape
Set ws = Sheet1
Set rng = ws.Range("A1:F10") 'Set range to fill with watermark
Application.ScreenUpdating = False
For Each shp In ws.Shapes
shp.Delete
Next shp
For Each cll In rng
Set shp = ws.Shapes.AddShape(msoShapeRectangle, 5, 5, 5, 5)
With shp
.Left = cll.Left
.Top = cll.Top
.Height = cll.Height
.Width = cll.Width
.Name = cll.address
If cll.Row Mod 2 = 1 Then
.TextFrame2.TextRange.Characters.Text = cll.address
Else
.TextFrame2.TextRange.Characters.Text = watermark
End If
.TextFrame2.TextRange.Font.Name = "Tahoma"
.TextFrame2.TextRange.Font.Size = 8
.TextFrame2.VerticalAnchor = msoAnchorMiddle
.TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignCenter
.TextFrame2.WordWrap = msoFalse
.TextFrame.Characters.Font.ColorIndex = 15
.TextFrame2.TextRange.Font.Fill.Transparency = 0.35
.Line.Visible = msoFalse
' Debug.Print "'SelectCell (""" & ws.Name & """,""" & cll.address & """)'"
.OnAction = "'SelectCell """ & ws.Name & """,""" & cll.address & """'"
With .Fill
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
.Transparency = 1
.Solid
End With
End With
Next cll
Application.ScreenUpdating = True
End Sub
Sub SelectCell(ws, address)
Worksheets(ws).Range(address).Select
End Sub
回答by Mathieu Guindon
You can use a custom number format (select the cell(s), hit Ctrl+1, number formats, custom) to specify a light-grey text to display when the cell value is 0 - Color15makes a nice watermark color:
您可以使用自定义数字格式（选择单元格，按 Ctrl+1，数字格式，自定义）指定在单元格值为 0 时显示的浅灰色文本 -Color15制作漂亮的水印颜色：
[Black]000000;;[Color15]"(order number)";@
No messy shapes, no VBA, and the watermark disappears when the value is actually filled up.
没有凌乱的形状，没有VBA，实际填满值时水印就会消失。
And if you absolutely need to do it in VBA, then you can easily write a function that builds the format string based on some parameters:
如果您绝对需要在 VBA 中执行此操作，那么您可以轻松编写一个基于某些参数构建格式字符串的函数：
Public Function BuildWatermarkFormat(ByVal watermarkText As String, Optional ByVal positiveFormat As String = "General", Optional ByVal negativeFormat As String = "General", Optional ByVal textFormat As String = "General") As String
BuildWatermarkFormat = positiveFormat & ";" & negativeFormat & ";[Color15]" & Chr(34) & watermarkText & Chr(34) & ";" & textFormat
End Function
And then you can do:
然后你可以这样做：
myCell.NumberFormat = BuildWatermarkFormat("Please enter a value")
myCell.Value = 0
And you can still supply custom formats for positive/negative values as per your needs; the only thing is that 0is reserved for "no value" and triggers the watermark.
您仍然可以根据需要为正/负值提供自定义格式；唯一的事情是0保留给“无价值”并触发水印。
myCell.NumberFormat = BuildWatermarkFormat("Please enter a value", "[Blue]#,##0.00_)", "[Red](#,##0.00)")
myCell.Value = -25
回答by Andrew
- Select the Cell where you want to make the Background.
- Click "Insert" and insert a rectangular Shape in that location.
- Right click on the shape - select "Format Shape"
- Goto "Fill" and select "Picture or texture fill"
- Goto “Insert from File” option
- Select the picture you want to make water-mark
- Picture will appear at the place of rectangular shape
- Now click on the picture “right click” and select Format Picture
- Goto “Fill” and increase the transparency as required to look it like a “Water Mark” or light beckground
- This will get printed also.
- 选择要制作背景的单元格。
- 单击“插入”并在该位置插入一个矩形形状。
- 右键单击形状 - 选择“设置形状格式”
- 转到“填充”并选择“图片或纹理填充”
- 转到“从文件插入”选项
- 选择要加水印的图片
- 图片会出现在长方形的地方
- 现在单击图片“右键单击”并选择“设置图片格式”
- 转到“填充”并根据需要增加透明度，使其看起来像“水印”或浅色背景
- 这也会被打印出来。
回答by Lee
Type your text in a cell anywhere. Copy it and it will be saved on the clipboard. Insert a rectangular shape anywhere. Right click and choose "Send to back". This will make sure it will be at the background. Right click and "Format Shape". Do to tab "Fill" and click on "picture or texture fill". At the "insert from" choose "clipboard". Now whatever text you have copied onto your clipboard will be in the rectangular shape. Resize the shape to fit the cell(s) you desired. Adjust however you like for example remove the rectangular lines, add shadow, change font, remove background etc.
在任意位置的单元格中键入文本。复制它，它将保存在剪贴板上。在任何地方插入一个矩形。右键单击并选择“发送回”。这将确保它位于后台。右键单击并“设置形状格式”。执行选项卡“填充”并单击“图片或纹理填充”。在“插入自”中选择“剪贴板”。现在，您复制到剪贴板上的任何文本都将呈矩形。调整形状以适合所需的单元格。根据您的喜好进行调整，例如删除矩形线、添加阴影、更改字体、删除背景等。
