Word Wrap VBA to Specific Column Width

vahnx

Board Regular
Joined
Apr 10, 2011
Messages
188
I have a database in excel, here's some sample data.

76231134.png


When they click the print button in Cell A2, up comes my form

15903702.png


When the print tags button is pressed, my secondary sheet is populated with the cell they clicked on

35779280.png


Now it's using a font, calibri 11, which isn't proportional but I was wondering if there's a way in VBA to "auto" detect the max width and place words on the next row accordingly (like a word wrap). Here's what I need it to look like:

65190481.png


Here's my existing code to place the comments as is:

Code:
                entireComment = "Comments: " & Range("P" & i)                
                .Range("A" & cnt + 11).Value = Mid(entireComment, 1, 33)
                If Len(entireComment) >= 36 Then
                    .Range("A" & cnt + 12).Value = Mid(entireComment, 36, 35)
                End If
                If Len(entireComment) >= 71 Then
                    .Range("A" & cnt + 13).Value = Mid(entireComment, 72, 35)
                End If
                If Len(entireComment) >= 106 Then
                    .Range("A" & cnt + 14).Value = Mid(entireComment, 107, 35)
                End If
                If Len(entireComment) >= 141 Then
                    .Range("A" & cnt + 15).Value = Mid(entireComment, 142, 35)
                End If
                If Len(entireComment) >= 176 Then
                    .Range("A" & cnt + 16).Value = Mid(entireComment, 177, 35)
                End If
                If Len(entireComment) >= 211 Then
                    .Range("A" & cnt + 17).Value = Mid(entireComment, 212, 35)
                End If

This is one of the last hurdles in a project I'm working on.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
This is out of the VBA help file, but you can probably figure out how to use it.

Code:
<CODE>Worksheets("Sheet1").Range("B2").Value = _
"This text should wrap in a cell."
Worksheets("Sheet1").Range("B2").WrapText = True
Code:
</CODE>
 

vahnx

Board Regular
Joined
Apr 10, 2011
Messages
188
Thank you so much! Much simpler solution than what I attempted.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Thank you so much! Much simpler solution than what I attempted.

The VBA help files are not that easy to use if you are not familiar with the conventions and VBA language. But the more they are used the easier it gets.

Regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,127,211
Messages
5,623,415
Members
415,972
Latest member
SY1234

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top