Solved

Control tip, how to put a return character in long strings.

Posted on 2011-03-19
8
302 Views
Last Modified: 2012-08-13
I have several Excel forms and in each of the controls i am popuating the controltiptext with information on teh control.  Is there a way to input a return character in the controtiptext so it is not just one long string?
0
Comment
Question by:ssmith94015
  • 5
  • 3
8 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 35173499
you can use vbCRLF which is the carriage return line feed code.

Here's a function you can put in a module - it will insert the vbCRLF as the increment you want to make the break - e.g., 50 chars, or whatever.


Code:
Function breakString(sControlTip As String, iBreak) As String
Dim tempStr As String
    
    If iBreak > Len(sControlTip) Then Exit Sub 'no need for vbCRLF as the string is already shorter than that
    
    For i = 1 To Len(sControlTip) Step iBreak
        If i = 1 Then
            tempStr = Mid(sControlTip, i, iBreak)
        Else
            tempStr = tempStr & vbCrLf & Mid(sControlTip, i, iBreak)
        End If
    Next i
    
    breakString = tempStr

End Function

Open in new window


Now, if your string is called sBIGstring, you can set your control tip using....

  resultForTip = breakstring(sBIGstring,50) 'carriage return, line feed every 50 characters...

Enjoy!

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35173511
Whoops - I tested as a sub, then converted to a function...  then, I put the last error checking in.  The Exit Sub statement should be Exit Function...

Here's the revised code:

Function breakString(sControlTip As String, iBreak) As String
Dim tempStr As String
    
    If iBreak > Len(sControlTip) Then Exit Function 'no need for vbCRLF as the string is already shorter than that
    
    For i = 1 To Len(sControlTip) Step iBreak
        If i = 1 Then
            tempStr = Mid(sControlTip, i, iBreak)
        Else
            tempStr = tempStr & vbCrLf & Mid(sControlTip, i, iBreak)
        End If
    Next i
    
    breakString = tempStr

End Function

Open in new window

Dave
0
 

Author Comment

by:ssmith94015
ID: 35173525
In what event do I put the controltiptext statement?  In the form's initialize event?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:ssmith94015
ID: 35173561
I put this in the form's initialize event.  The string does show up, but it does not line-break.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35173598
can you send me a pared-down version quickly?  Or, should I build it from here?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35173610
Never mind - I built one.  Its not looking good.  not sure controltiptext has a word wrap option.  It wasn't designed for that.

Doing some googling and found that folks put labels on the userform with the info needed to guide.  some even "unhid" the labels based on the mouse hovering for a short while.

Would you be interested in going down this path?

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 35173621
This one's pretty cool - put a "what's this" help button on your userform, then you can document to your heart's content...

http://www.andypope.info/vba/whatsthishelp.htm


Let me know if you'd like further assistance with this.

Dave
0
 

Author Closing Comment

by:ssmith94015
ID: 35173793
Thanks!  Sounds like you got the same Google results I did!  what is really weird is several years ago I was creating an ACCESS database form and got it to have multiple lines.  I don't know how I did nor can I find a copy of that old database and form - Naturally!  I will follow up on your link.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This collection of functions covers all the normal rounding methods of just about any numeric value.
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question