在消息框 excel Vba 中格式化文本

Formatting text in messagebox excel Vba

提问by confusedMind

I am new to Excel VBA and have written Vba code to loop through cells and get their values.After that do some processing and if it matches a certain criteria append them to a list with line break. Do this until all rows are done. This works perfectly fine and end result is as image below:

我是 Excel VBA 的新手，并编写了 Vba 代码来循环遍历单元格并获取它们的值。之后进行一些处理，如果它符合某个条件，则将它们附加到带有换行符的列表中。这样做直到所有行都完成。这工作得很好，最终结果如下图：

The thing is i want the formatting to look neat , so is there a way that the gap in between the text is same on all rows so it looks neat. The way i add the rows is :

问题是我希望格式看起来整洁，所以有没有办法使所有行上的文本之间的间隙都相同，因此看起来很整洁。我添加行的方式是：

          Dim tmpLine
          tmpLine = line & "      " & dateVal
          mainMessage = mainMessage & tmpLine & vbNewLine

Not sure if its the perfect way but that is what i know...

不确定它是否是完美的方式，但这就是我所知道的......

回答by simpLE MAn

I got this, maybe it'll solve your problem:

我明白了，也许它会解决你的问题：

Sub msgBoxTest()

    MsgBox "inininegefedf" & vbTab & "1234" & vbCr & _
            "asdcainininegefedf" & vbTab & "1234" & vbCr & _
            "inininegefedf" & vbTab & "1234afsad", vbCritical

End Sub

Here is the result:

结果如下：

回答by Walter Rauschenberger

I've implemented a solution for this as complete as possible and spent a considerable effort for it. When transferring clsFmsgBox, frmFmsgBox, and modFmsgBoxfrom FmsgBox.xlsmWorkbook the below code leads to this kind of message boxmay be managed with comparably little effort. The Workbook above provides coding examples as well.

我已经为此尽可能完整地实施了一个解决方案，并为此付出了相当大的努力。当从FmsgBox.xlsm工作簿传输clsFmsgBox、frmFmsgBox和modFmsgBox 时，下面的代码导致这种消息框可以用相对较少的努力进行管理。上面的工作簿也提供了编码示例。

 With cFmsgBox
    .Title = "Message Box supporting formatted text. Example 1"
    .Msg = "This is the first " & _
     .b("Test ") & "message spanning over several lines and paragraphs. The default margins, spaces, and font size had been used. " & _
                   "No need to say that the form width had been adjusted manually in order to have an optimum appearance." & _
     .Lf & _
     .Lf & "The formats " & _
     .b("bold") & ", " & _
     .u("underline") & ", and " & _
     .i("italic ") & "and may be combined with any of the colours " & _
     .b(.i(.u(.red("red")))) & ", " & _
     .b(.i(.u(.blue("blue")))) & ", and " & _
     .b(.i(.u(.green("green")))) & "." & _
     .Lf & .Lf & _
           "5 different links may be included in the message text, either in the full form like " & _
     .link("www.google.com") & " or with a friendly name like " & .link("www.google.com", "google.com") & ", which masks the url behind it." & _
     .Lf & _
     .Lf & _
        "Also it shows 2 of the 6 possible reply buttons and that they may contain any text. Since the number of lines is maximized to 3 their width will be adjusted " & _
        "automatically - but will remain the same for all buttons. The string returned by the display call is identical with the string of the clicked reply button."

    .Reply1 = "Click this reply to continue with the next example"
    .Reply2 = "Click this reply to finish with the Message Box solution's features"
    .Dsply 318

    If .Reply = .Reply1 Then Example2
End With

The message is displayed in a dedicated UserForm with dynamically created labels for each formated text string and dynamically created (up to 6) command buttons. The key to the formatted message are the format properties .b("xxx") for bold, .i("xxx") for italic and so on, which can all be nested like .b(.i("xxx")) to get an italic,bold text for example.

该消息显示在专用用户窗体中，带有为每个格式化文本字符串动态创建的标签和动态创建的（最多 6 个）命令按钮。格式化消息的关键是格式属性 .b("xxx") 为粗体，.i("xxx") 为斜体等等，它们都可以像 .b(.i("xxx")) 一样嵌套例如，获取斜体、粗体文本。

Alternatively, instead of a concatenaed string the message text may be provided with RTF/HTML like formating tags whereby the open/close Tag character defaults to {} but may as well be changed to <>. As an example: "{b}bold{/b) {i}italic{/i}." will display: bolditalic.

或者，消息文本可以提供类似 RTF/HTML 的格式化标签，而不是串联字符串，其中打开/关闭标签字符默认为 {}，但也可以更改为 <>。例如：“{b}bold{/b) {i}italic{/i}”。将显示：粗斜体。

回答by Mathieu Guindon

Implement string.Format() - a bit overkill for just this one msgbox, but the reusability is quite endless:

实现 string.Format() - 就这一个 msgbox 来说有点矫枉过正，但可重用性是无穷无尽的：

Implementing String.Format() in VB6

在 VB6 中实现 String.Format()

Something like msg = StringFormat("{0}\n{1,-10}{2:cMM/DD/YYYY}", msg, line, dateVal)should work.

类似的东西msg = StringFormat("{0}\n{1,-10}{2:cMM/DD/YYYY}", msg, line, dateVal)应该工作。

Or, focus on this part of the implementation:

或者，专注于这部分的实现：

alignmentPadding = Abs(CInt(alignmentSpecifier))
If CInt(alignmentSpecifier) < 0 Then
    'negative: left-justified alignment
    If alignmentPadding - Len(formattedValue) > 0 Then _
        formattedValue = formattedValue & _
            String$(alignmentPadding - Len(formattedValue), PADDING_CHAR)
Else
    'positive: right-justified alignment
    If alignmentPadding - Len(formattedValue) > 0 Then _
        formattedValue = String$(alignmentPadding - Len(formattedValue), PADDING_CHAR) & formattedValue
End If

Where PADDING_CHARwould be a " " whitespace and alignmentSpecifierthe amount of padding you need.

PADDING_CHAR“” 空格和alignmentSpecifier您需要的填充量在哪里。

In other words, pad lineto 20 characters and then append the date:

换句话说，填充line到 20 个字符，然后附加日期：

tmpLine = line & String$(20 - Len(line), " ") & dateVal & vbNewline

..works for me:

..对我有用：

?"'abc12" & string$(20-len("'abc12"), " ") & "12/12/2004"
'abc12              12/12/2004
?"'abc1234" & string$(20-len("'abc1234"), " ") & "12/12/2004"
'abc1234            12/12/2004
?"'abc1234456" & string$(20-len("'abc1234456"), " ") & "12/12/2004"
'abc1234456         12/12/2004

UPDATE

更新

It seems the issue isn't about the message string itself, but because of the font used in MsgBox: what worked for me, worked because I used the immediate paneto get quick results, and that's displayed with a monospaced font (where all characters are same width).

似乎问题不在于消息字符串本身，而是由于以下字体中使用的字体MsgBox：对我有用的字体，因为我使用了即时窗格来获得快速结果，并且使用等宽字体显示（其中所有字符都是宽度相同）。

I suggest you create a quick form with a fixed-width Labelthat uses a font like "Courier New" or "Consolas"... or just display the date in front of the variable-length part.

我建议您创建一个固定宽度的快速表单Label，使用“Courier New”或“Consolas”之类的字体……或者只在可变长度部分前面显示日期。

回答by Simon

I would recommend using a "userform". Under your VBA project in the VBA editor, right click and select "insert / userform" (or use top menu "insert / userform")

我建议使用“用户表单”。在 VBA 编辑器中的 VBA 项目下，右键单击并选择“插入/用户表单”（或使用顶部菜单“插入/用户表单”）

From there, you'll see a number of ActiveX controls in a "toolbox" (or click "view/toolbox") that can help you construct what you're after: sounds like you're after a "listbox", which you should find in the toolbox (hover over the items and "listbox" will appear as a tooltip)

从那里，您将在“工具箱”（或单击“视图/工具箱”）中看到许多可以帮助您构建所需内容的 ActiveX 控件：听起来您在使用“列表框”，应该在工具箱中找到（将鼠标悬停在项目上，“列表框”将显示为工具提示）

With your listbox selected, you can set the number of columns plus the column widths in the "properties" window (click "view/properties window" if you can't see it)

选择列表框后，您可以在“属性”窗口中设置列数加上列宽（如果看不到，请单击“查看/属性窗口”）

See how you go, lots of good info on the web on VBA userforms and listboxes or happy to answer any more questions.

看看你的进展情况，网上有很多关于 VBA 用户表单和列表框的好信息，或者很乐意回答任何更多问题。

Cheers, Si

干杯，Si

[EDIT] Just thought of some code I've got which should get you started (from a little tool I wrote to turn Office 2010 references in to Office 2003 references): in my main code module I've just got:

[编辑] 刚刚想到了一些应该可以帮助您入门的代码（从我编写的一个小工具将 Office 2010 引用转换为 Office 2003 引用）：在我的主代码模块中，我刚刚得到：

Sub FixReferences() 'Ctrl-Shift-F

    ufWorkbooks.Show

    If ufWorkbooks.blCancel = False Then
        With ufWorkbooks.lbWorkbooks
            For intindex = 0 To .ListCount - 1
                If .Selected(intindex) Then
                    SwapReferences Workbooks(.List(intindex))
                End If
            Next
        End With
    End If
End Sub

This loads my userform (called ufWorkbooks) in the line ufWorkbooks.Show. Execution passes to that form at this point: when execution comes back, a variable from the form is checked to see if cancel has been pushed, then you can see I've got a function running on each item of my listbox on the form called "lbWorkbooks". The code in my userform looks like this:

这会在 ufWorkbooks.Show 行中加载我的用户表单（称为 ufWorkbooks）。此时执行传递到该表单：当执行返回时，检查表单中的一个变量以查看是否已推送取消，然后您可以看到我在名为的表单上的列表框的每个项目上运行了一个函数“lbWorkbooks”。我的用户表单中的代码如下所示：

Public blCancel As Boolean

Private Sub cbCancel_Click()
    blCancel = True
    Me.Hide
End Sub

Private Sub cbOK_Click()
    Me.Hide
End Sub

Private Sub UserForm_Activate()
    blCancel = False

    FillWorkbooks
End Sub

Sub FillWorkbooks()
    Dim wbBook As Workbook

    lbWorkbooks.Clear

    For Each wbBook In Workbooks
        lbWorkbooks.AddItem wbBook.Name
    Next

End Sub

Here you can see I'm using different "events" to trigger certain bits of code. At the bottom you can see my "FillWorkbooks" sub is first clearing my listbox (which ive named lbWorkbooks) and then I'm adding items to it. This is good for one column listboxes. In your case, you might want to use .AddItem then .List(0, 1) = "whatever" (row 0 in the listbox, column 1). I can't remember if AddItem will add a blank row at the top for you to reference with .List(0... or if you'll need to add a counter .AddItem then .List(i...

在这里您可以看到我正在使用不同的“事件”来触发某些代码位。在底部，您可以看到我的“FillWorkbooks”子项首先清除我的列表框（我将其命名为 lbWorkbooks），然后我正在向其中添加项目。这对一列列表框很有用。在您的情况下，您可能想要使用 .AddItem 然后 .List(0, 1) = "whatever"（列表框中的第 0 行，第 1 列）。我不记得 AddItem 是否会在顶部添加一个空白行供您使用 .List(0...

Alternately you can use Listbox.ListFillRange to use a range of cells from your spreadsheet as the data source for your listbox.

或者，您可以使用 Listbox.ListFillRange 将电子表格中的一系列单元格用作列表框的数据源。