Exact SPACE between two strings and proper position for Second String in Multiline Textbox

[NimishK]

Hello

Can someone refine the below code inorder to get Exact Amount of SPACE between 2 strings
Tried to create function but somehow could not succeed.
I want exact SPACE between Two Strings. So the alignment comes perfectly between Two Strings in Textbox
Somehow the alignment between two strings is not properly aligned with below code

Option Explicit

Private Sub UserForm_Initialize()
Dim strDescp(4) As String, strQuot(4) As String
Dim strHeader As String
Dim i As Integer, gapSpc As Integer
Dim strData As String

strHeader = "Description" & Space(10) & "Quoted"

strDescp(1) = "The Brown Fox"
strDescp(2) = "quickly Jumped"
strDescp(3) = "over the"
strDescp(4) = "Lazy dogs"

strQuot(1) = "Yes"
strQuot(2) = "No"
strQuot(3) = "No"
strQuot(4) = "Yes"

strData = strHeader & vbCrLf

For i = 1 To UBound(strDescp)
  gapSpc = NewTotSpaces((strDescp(i)), 15, 8)
  strData = strData & strDescp(i) & Space(gapSpc) & strQuot(i) & vbCrLf
Next i

TextBox1.Text = TextBox1.Text & strData
End Sub

Public Function NewTotSpaces(strChar As String, ByVal maxStrLen As Integer, extraSpc As Integer) As Integer

Dim strLen As Integer
Dim newPos As Integer, i As Integer, rMangSpc As Integer, exactSpc As Integer

strLen = Len(strChar)

If strLen >= maxStrLen Then
   rMangSpc = maxStrLen 'strLen
   exactSpc = (rMangSpc - maxStrLen)           'rMangSpc '+ extraSpc
   NewTotSpaces = exactSpc
Else
If strLen < maxStrLen Then
   rMangSpc = maxStrLen - strLen
   exactSpc = rMangSpc + extraSpc
   NewTotSpaces = exactSpc
 
End If
End If

End Function

NimishK

 

[Zot]

I have no idea how you are expecting the output. Is it something like on left Description column and on right Yes/No?

Then why not use ListBox instead. Create 2 columns fill accordingly

Some reference

Populating Multi-Column ListBox/ComboBox – Daily Dose of Excel

dailydoseofexcel.com

 

[NimishK]

Thanks Zot
my purpose is to use textbox and not listbox nor combobox.

The above coding was done only for two Variables only.

have no idea how you are expecting the output. Is it something like on left Description column and on right Yes/No?

My Output to have proper spacing and for second string to be in a correct position irrespective lenght of 1st string
You may execute the above the code for checking

NimishK
609

 

[Zot]

My result is like this. I put the code in UserForm. I guess you want it to be like two column ListBox.

The problem is each character has different width. So, it is going to hard to create a virtual vertical line to create same spacing. Maybe I can try something later.

 

[NimishK]

The problem is each character has different width. So, it is going to hard to create a virtual vertical line

Exactly. different fonts will have different width

I guess you want it to be like two column ListBox.

Structure should be properly aligned with correct postioning of Second String. FYI I am not all going to use List or combobox
By the way your image indicates you have not put your textbox to Multiline

Textbox1.Multiline = True

 

[Zot]

Exactly. different fonts will have different width


Structure should be properly aligned with correct postioning of Second String. FYI I am not all going to use List or combobox
By the way your image indicates you have not put your textbox to Multiline

Textbox1.Multiline = True

Aaah, Right. No Multiline.

Anyway, would putting two textbox side by side be a possible solution? It would be easier

 

[Akuini]

Exactly. different fonts will have different width

Try using monospace font such as Courier New or Consolas.

TextBox1.Font = "Courier New"

 

[Zot]

Try using monospace font such as Courier New or Consolas.

TextBox1.Font = "Courier New"

That is the best solution. There are a number of monospace fonts out there.

 

[NimishK]

Thanks Zot and Akuini for the suggestion of using Monospace fonts like
"Courrier New", "Consolas"
I tried "Fixedsys" too.
All the above 3 fonts gave right results.
Also how to check for monospace fonts or any link with list of monospace fonts ?

So this meas coding as per # 1 was just perfect. if not you could correct it.


Thanks
NimishK

 

[Zot]

Thanks Zot and Akuini for the suggestion of using Monospace fonts like
"Courrier New", "Consolas"
I tried "Fixedsys" too.
All the above 3 fonts gave right results.
Also how to check for monospace fonts or any link with list of monospace fonts ?

So this meas coding as per # 1 was just perfect. if not you could correct it.


Thanks
NimishK

I see the result was just fine when I ran your program