Show data from two columns in message box

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Show data from two columns in message box

Post by YasserKhalil »

Hello everyone
I am storing some data from two columns and I need to display a message box with the two columns in neat way
Here's the line that constructs the string

Code: Select all

s1 = s1 & IIf(s1 = Empty, Empty, vbCrLf) & shINF.Cells(x, 2).Value & IIf(Len(shINF.Cells(x, 2).Value) <= 30, Space(50 - Len(shINF.Cells(x, 2).Value)), Empty) & shINF.Cells(x, 4).Value
I tried also

Code: Select all

s1 = s1 & IIf(s1 = Empty, Empty, vbCrLf) & shINF.Cells(x, 2).Value & Space(50 - Len(shINF.Cells(x, 2).Value)) & shINF.Cells(x, 4).Value
The message box doesn't displayed in neat way (although I tried to put spaces for the short texts). What I need is to make the text from the first column equal to 50 characters


Note: when I tried to print the texts in the immediate window, it worked well and as expected. Is there a default width for the message box?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Show data from two columns in message box

Post by HansV »

This would only work with a fixed-width font, but MsgBox uses a proportional font. So the text will never line up correctly.
As an alternative, populate a text box on a userform, and assign a fixed-width font such as Courier New or Consolas to the text box.

S1552.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Show data from two columns in message box

Post by HansV »

I used

Code: Select all

        s1 = s1 & IIf(s1 = "", "", vbCrLf) & Left(shInf.Cells(x, 2).Value & Space(50), 50) & shInf.Cells(x, 4).Value
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Show data from two columns in message box

Post by YasserKhalil »

I don't want to create a userform for such a task, in fact. Is it possible to make this through the message box itself?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Show data from two columns in message box

Post by HansV »

It might be possible, but I cannot help you with that.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Show data from two columns in message box

Post by YasserKhalil »

I could solve it by chance. I calculated the max number of characters in the column and it was 22 and I used this trick of vbTab

Code: Select all

s1 = s1 & IIf(s1 = Empty, Empty, vbCrLf) & shINF.Cells(x, 2).Value & Space(2) & Space(40 - Len(shINF.Cells(x, 2).Value)) & vbTab & IIf(Len(shINF.Cells(x, 2).Value) < 23, vbTab, Empty) & shINF.Cells(x, 4).Value