excel - Excel VBA 中心页眉/页脚“左对齐”
问题描述
有没有办法在 Excel 中对齐中心标题?我知道没有任何内置解决方案,但是否有任何 VBA 代码可以工作。我一直在尝试将单元格复制到标题,使用 VBA 设置中心标题,但我的中心标题一直是“对齐中心”。
我什至发现了非常复杂的代码来计算句子的长度并为每一行添加空格,但它并不能真正正常工作。
我还可以将行设置为在顶部重复而忘记页眉,但是页脚呢?如何设置中心页脚以对齐我的两行文本以左对齐?
我试过了:
With ActiveSheet.PageSetup
.LeftHeader = Range("a1").Value & " " & Range("b1").Value & " " & Range("a2").Value & " " & Range("b2").Value
End With
还将命名范围发送到标题:
Option Explicit
Sub SetCenterHeader()
Dim txt As String
Dim myRow As Range
With Range("NorthHead") ' reference named range
For Each myRow In .Rows ' loop through referenced range rows
txt = txt & Join(Application.Transpose(Application.Transpose(myRow.Value)), " ") & vbLf ' update 'txt' with current row cells values joined and separated by a blank
Next
End With
ActiveSheet.PageSetup.CenterHeader = Left(txt, Len(txt) - 1) ' set CenterHeader with resulting 'txt' excluding last vblf character
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub
结果总是一样的:
解决方案
可以尝试以下解决方法并根据您的要求进行修改
Sub test2()
Dim CenHd1 As String, CenHd2 As String, Fname As String
Dim Rng As Range
Dim Sht As Worksheet, MnSht As Worksheet
Dim Cht As ChartObject
Set Sht = ThisWorkbook.Worksheets(3)
Set MnSht = ThisWorkbook.Worksheets(1)
Set Rng = Sht.Range("F1:F2")
CenHd1 = "Excel"
CenHd2 = "I am already left Aligned"
Sht.Range("F1").Value = CenHd1
Sht.Range("F2").Value = CenHd2
Sht.Activate
ActiveWindow.DisplayGridlines = False
With Rng
.Columns.AutoFit 'added after taking trial snapshot to perfectly center and left align
.HorizontalAlignment = xlLeft
.Font.Name = "Bookman Old Style"
.Font.Size = 12
'May specify other visual effects
End With
Rng.CopyPicture xlScreen, xlPicture
Set Cht = Sht.ChartObjects.Add(0, 0, Rng.Width * 1.01, Rng.Height * 1.01)
Cht.Name = "TmpChart"
Sht.Shapes("TmpChart").Line.Visible = msoFalse
Cht.Chart.Paste
Fname = "C:\Users\user\Desktop\CentHead " & Format(Now, "dd-mm-yy hh-mm-ss") & ".jpg"
Cht.Chart.Export Filename:=Fname, Filtername:="JPG"
DoEvents
Cht.Delete
ActiveWindow.DisplayGridlines = True
MnSht.Activate
With MnSht.PageSetup.CenterHeaderPicture
.Filename = Fname
'.Height = 275.25
'.Width = 463.5
'.Brightness = 0.36
'.ColorType = msoPictureGrayscale
'.Contrast = 0.39
'.CropBottom = 0
'.CropLeft = 0
'.CropRight = 0
'.CropTop = 0
End With
'Enable the image to show up in the center header.
MnSht.PageSetup.CenterHeader = "&G"
'for Trial only
ActiveWindow.View = xlPageLayoutView
' Clear junk files
If Dir(Fname) <> "" Then Kill (Fname)
End Sub
代码也可以修改为带有参数的函数/过程,以便与不同的工作表、工作簿等重复使用。希望它有助于达到目的。
推荐阅读
- java - 获取有效 HTTP 字符的正则表达式
- python - 控制ROUNDED_RECTANGLE的圆角,python + pptx
- ios - 优化在 Ray Wenderlich 上发现的 2d 瓦片碰撞(objective-c)
- java - 如何知道 Netty ByteBuf 中是否没有数据可读取?
- r - 如何在 sas 中实现 r 中的子集?
- gradle - 如何从 processResources Gradle 任务中排除多个文件
- java - doc文件需要在文件名附近显示doc徽标,例如whatsapp
- python - 匹配 Pandas 数据框中的一对多列
- angular - 如何订阅rxjs中另一个对象中引用的对象的属性更改
- javascript - 我的 Ajax 请求只发送一次