Is there any way to get MsgBox columns to line up?

Jennifer Murphy · #1 02-29-2020, 09:50 AM

Apparently, MsgBox uses a proportionally spaced font, so padding data with spaces does not result in aligned columns as this code illustrates:

Code:

Const t1 As String = "abcdef"
Const t2 As String = "ABCDEF"
Const t3 As String = "xx yy zz"
Const d1 As Double = 0.4567
Const d2 As Double = 12.3456
Const d3 As Double = 333.4444
Dim msg As String
msg = Left("  Text      ", 10) & Left(" Value   ", 8) & vbCr _
    & Left(t1 & "        ", 10) & Right("   " & Format(d1, "##0.00"), 8) & vbCr _
    & Left(t2 & "        ", 10) & Right("   " & Format(d2, "##0.00"), 8) & vbCr _
    & Left(t3 & "        ", 10) & Right("   " & Format(d3, "##0.00"), 8)
MsgBox msg
msg = Left("  Text      ", 10) & Left(" Value   ", 8) & vbCr _
    & Left(t1 & "        ", 10) & Right("   " & Format(d1, "000.00"), 8) & vbCr _
    & Left(t2 & "        ", 10) & Right("   " & Format(d2, "000.00"), 8) & vbCr _
    & Left(t3 & "        ", 10) & Right("   " & Format(d3, "000.00"), 8)
MsgBox msg

Is there any way to make this work with MsgBox?

If not, is there another relatively simple way to get aligned columns displayed to the Word document?

Guessed · #2 02-29-2020, 02:36 PM

Relatively simple? No

You could probably change the Windows dialog font to Courier if it was that important to align text with spaces. You could also use tabs if the lengths are close

Code:

msg = Left("  Text      ", 10) & Left(" Value   ", 8) & vbCr _
    & t1 & vbTab & Format(d1, "000.00") & vbCr _
    & t2 & vbTab & Format(d2, "000.00") & vbCr _
    & t3 & vbTab & Format(d3, "000.00")
MsgBox msg

But the right way to do it would be using a UserForm and listbox control.

Jennifer Murphy · #3 02-29-2020, 06:51 PM

Quote:

Originally Posted by Guessed

Relatively simple? No

But the right way to do it would be using a UserForm and listbox control.

That's what I thought. I've been meaning to learn UserForms for a while. This may be the push I need.

Can I trouble you to post a basic skeleton for how to get started?

Given these variables:

Code:

Dim MaxNum as Long
Dim iRow() as Long
Dim dDays() as Double
Dim sTitle() as String

As the code runs, these arrays get ReDim'd to (1 To MaxNum).

I'd like the UserForm to display one line for each row or the arrays plus this title so that everything lines up:

Code:

 Row   Days   Title
  1    3.25  Title #1
  2   12.67  Title #2
  3    0.17  Title #3
  4   14.50  Title #4
   . . .

And then give the user the option of selecting one of the rows by number.

Thanks

gmayor · #4 03-01-2020, 12:13 AM

Using a userform and a list box it is a simple task to present the data from an array. The attached uses a two column list box and the data from your original query.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Match to columns and then copy line to second list.	Borut2019	Excel	0	02-18-2019 01:04 AM
Problem with Msgbox in VBA	Puzzled Programmer	Excel Programming	6	09-06-2018 04:34 PM
columns in last table row don't line up	mavisgold	Word	1	03-26-2017 02:32 AM
Formatting the line between columns in Word Mac 2011	bpanda	Word	1	06-03-2013 07:52 PM
Make MsgBox appear...	Jamtart	PowerPoint	3	09-01-2012 08:21 AM