Solved

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

Posted on 2011-03-19
8
301 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
Comment Utility
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
Comment Utility
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
Comment Utility
In what event do I put the controltiptext statement?  In the form's initialize event?
0
 

Author Comment

by:ssmith94015
Comment Utility
I put this in the form's initialize event.  The string does show up, but it does not line-break.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 41

Expert Comment

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

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now