Exit UserForm if too many characters selected

User avatar
Charles Kenyon
3StarLounger
Posts: 275
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Exit UserForm if too many characters selected

Post by Charles Kenyon »

I am attempting to write a userform. If it is started with too many characters (>58) selected in the document, I want to immediately terminate it after a message box. Here is my initialization code.

Private Sub UserForm_Initialize()
Dim i As Integer
Let i = Selection.Range.Characters.Count
If i > 58 Then
MsgBox Title:="Too many characters selected! (" & i & " characters)", _
Prompt:="Maximum number of characters is 58." & vbCrLf & _
"Please select fewer characters.", _
Buttons:=vbCritical
GoTo Goodbye
End If
' PLACE SELECTED TEXT IN DISPLAY TEXT BOX
Let Me.txtPromptText.Text = Selection.Range.Text
' Set focus to text box
Me.lblVersion.Caption = "Version " & ThisDocument.Variables("Version").Value
GoTo InitializeEnd
Goodbye:
Unload Me
InitializeEnd:
End Sub


It works as expected when the number of characters is under the limit.
If over the limit, it gives the msgbox just fine. Then throws an error message when I OK out of the message box.
Run time error '91':
Object variable or With Block variable not set.
Attorney Charles Kenyon

Templates in Microsoft Word http://www.addbalance.com/usersguide/templates.htm
Modifying the QAT http://addbalance.com/word/QATmodification.htm

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

Re: Exit UserForm if too many characters selected

Post by HansV »

If you try to unload the userform in the UserForm_Initialize event, you unload it before it has been fully loaded, causing an error.
As an alternative, perform the check in the macro that shows the userform. The macro (in a standard module) could look like this:

Code: Select all

Sub ShowForm()
    Dim i As Long
    i = Selection.Range.Characters.Count
    If i > 58 Then
        MsgBox Title:="Too many characters selected! (" & i & " characters)", _
            Prompt:="Maximum number of characters is 58." & vbCrLf & _
            "Please select fewer characters.", _
            Buttons:=vbCritical
    Else
        UserForm1.Show ' use the actual name of your userform
    End If
End Sub
The UserForm_Initialize event procedure can then be shortened to:

Code: Select all

Private Sub UserForm_Initialize()
    ' PLACE SELECTED TEXT IN DISPLAY TEXT BOX
    Me.txtPromptText.Text = Selection.Range.Text
    ' Set focus to text box
    Me.lblVersion.Caption = "Version " & ThisDocument.Variables("Version").Value
End Sub
Regards,
Hans

snb
3StarLounger
Posts: 217
Joined: 14 Nov 2012, 16:06

Re: Exit UserForm if too many characters selected

Post by snb »

or

Code: Select all

Private Sub UserForm_Initialize()
    If Len(Selection) > 58 Then
        MsgBox "Max. length: 58", 16, Replace("Too long: # characters", "#", Len(Selection))
        Unload Me
    Else
        txtprompttext = Selection
        lblVersion = "Version " & ThisDocument.Variables("Version")
    End If
End Sub

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

Re: Exit UserForm if too many characters selected

Post by HansV »

Hi snb, have you actually tried your code?

S3322.png
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

snb
3StarLounger
Posts: 217
Joined: 14 Nov 2012, 16:06

Re: Exit UserForm if too many characters selected

Post by snb »

Yes. The same result if the selection is 'too long'.

User avatar
Charles Kenyon
3StarLounger
Posts: 275
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: Exit UserForm if too many characters selected

Post by Charles Kenyon »

Thank you both. This is in an Add-In to add a field to a document.

I figured out a workaround. As I think was suggested, I trap the error in the code calling the userform.
I leave the statement in to quit the userform during the initialize but trap the error.

First, the report of an err.number of 91 in the message is false. I used a message box to report it then test for the actual number.

lbl_NoDocument: ' Error Handler - Will be called if there is not an open document
' MsgBox Err.Number
If Err.Number <> -2147418105 Then ' Check if the error was thrown earlier
MsgBox Prompt:="Sorry, you cannot insert a field unless you have a document open.", _
Title:="No document open! Cannot insert popup text.", Buttons:=vbExclamation
End If


The primary error handled is that the code is launched when there is no document open. However if the text is too long, it will give err.number -2147418105. Prior to quitting the userform, that generated its own message. This error handler simply ends the procedure in that case.
Attorney Charles Kenyon

Templates in Microsoft Word http://www.addbalance.com/usersguide/templates.htm
Modifying the QAT http://addbalance.com/word/QATmodification.htm

snb
3StarLounger
Posts: 217
Joined: 14 Nov 2012, 16:06

Re: Exit UserForm if too many characters selected

Post by snb »

I would prevent starting a macro when:
- no document exists
- no selection > 1 exists

Try to prevent any 'error' (whose error anyway ?) messages to bother the user.

User avatar
Charles Kenyon
3StarLounger
Posts: 275
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: Exit UserForm if too many characters selected

Post by Charles Kenyon »

How does one prevent running a macro if no document exists?

For this procedure, it does not matter if there is nothing selected, so long as there is an active document.
Macro will be triggered by a QAT button and I do not have the skill to dim it if there is no active document.
Attorney Charles Kenyon

Templates in Microsoft Word http://www.addbalance.com/usersguide/templates.htm
Modifying the QAT http://addbalance.com/word/QATmodification.htm

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

Re: Exit UserForm if too many characters selected

Post by HansV »

You could exit the macro immediately if no document is open:

Code: Select all

Sub Something()
    If Documents.Count = 0 Then
        MsgBox  Prompt:="Sorry, you cannot insert a field unless you have a document open.", _
            Title:="No document open! Cannot insert popup text.", Buttons:=vbExclamation
        Exit Sub
    End If
    ' rest of the code
    ...
Regards,
Hans

User avatar
Charles Kenyon
3StarLounger
Posts: 275
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: Exit UserForm if too many characters selected

Post by Charles Kenyon »

Thank you Hans.
Attorney Charles Kenyon

Templates in Microsoft Word http://www.addbalance.com/usersguide/templates.htm
Modifying the QAT http://addbalance.com/word/QATmodification.htm

snb
3StarLounger
Posts: 217
Joined: 14 Nov 2012, 16:06

Re: Exit UserForm if too many characters selected

Post by snb »

Since you work with an addin you can simply use in normal.dot:

Code: Select all

Sub autonew()
    AddIns(2).Installed = True
End Sub

Code: Select all

Sub autoclose()
    AddIns(2).Installed = Documents.Count > 1
End Sub

User avatar
Charles Kenyon
3StarLounger
Posts: 275
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: Exit UserForm if too many characters selected

Post by Charles Kenyon »

Thank you. I am developing this Add-In for distribution. I will not mess with a user's normal.dotm.

I should have it done this weekend. Hans' method of checking for open documents works fine.

By the way, on my installation I just ran the code in the Immediate Window:
? Application.Addins.Count
The answer was 48. I'm glad I'm on an SSD drive.
Attorney Charles Kenyon

Templates in Microsoft Word http://www.addbalance.com/usersguide/templates.htm
Modifying the QAT http://addbalance.com/word/QATmodification.htm

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

Re: Exit UserForm if too many characters selected

Post by HansV »

48? Wow, that's a lot!
Regards,
Hans

User avatar
Charles Kenyon
3StarLounger
Posts: 275
Joined: 10 Jan 2016, 15:56
Location: Madison, Wisconsin

Re: Exit UserForm if too many characters selected

Post by Charles Kenyon »

HansV wrote:
23 May 2020, 21:48
48? Wow, that's a lot!
Yes, it is. I pick them up here and there and write them as well. It makes troubleshooting a real pain!

Speaking of which, here is a link to the latest which gives pop-up text in a Word document using the AutoTextList field instead of the Hyperlink field.
http://addbalance.com/word/download.htm#PopUp

I have tested in Word 2007 and 2019. The code should work in earlier versions as well but I only did .dotm so far.

The hyperlink field works much the same and Lene Fredborg devoted a lot of effort to that. The thing is it has the language at the end of the popup about using Ctrl + Click to follow the link. https://wordaddins.com/products/screen-tips/

If either of you would like the upgraded version which starts with selected text and allows shading, drop me a note at wordfaq@addbalance.com and I will send you a download link.
Pop Up Text Dialog.png
You do not have the required permissions to view the files attached to this post.
Last edited by Charles Kenyon on 26 May 2020, 21:08, edited 1 time in total.
Attorney Charles Kenyon

Templates in Microsoft Word http://www.addbalance.com/usersguide/templates.htm
Modifying the QAT http://addbalance.com/word/QATmodification.htm

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

Re: Exit UserForm if too many characters selected

Post by HansV »

Hi Charles,

Thank you for the offer, I appreciate it! It looks interesting, but I don't think I'd have much use for it (I only use Word for very simple purposes :blush:).
Regards,
Hans