Exit UserForm if too many characters selected
-
- 5StarLounger
- Posts: 628
- Joined: 10 Jan 2016, 15:56
- Location: Madison, Wisconsin
Exit UserForm if too many characters selected
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.
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
Modifying the QAT
The Importance of Styles in Microsoft Word
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Exit UserForm if too many characters selected
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:
The UserForm_Initialize event procedure can then be shortened to:
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
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
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 587
- Joined: 14 Nov 2012, 16:06
Re: Exit UserForm if too many characters selected
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
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Exit UserForm if too many characters selected
Hi snb, have you actually tried your code?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 587
- Joined: 14 Nov 2012, 16:06
Re: Exit UserForm if too many characters selected
Yes. The same result if the selection is 'too long'.
-
- 5StarLounger
- Posts: 628
- Joined: 10 Jan 2016, 15:56
- Location: Madison, Wisconsin
Re: Exit UserForm if too many characters selected
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.
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
Modifying the QAT
The Importance of Styles in Microsoft Word
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
-
- 4StarLounger
- Posts: 587
- Joined: 14 Nov 2012, 16:06
Re: Exit UserForm if too many characters selected
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.
- no document exists
- no selection > 1 exists
Try to prevent any 'error' (whose error anyway ?) messages to bother the user.
-
- 5StarLounger
- Posts: 628
- Joined: 10 Jan 2016, 15:56
- Location: Madison, Wisconsin
Re: Exit UserForm if too many characters selected
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.
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
Modifying the QAT
The Importance of Styles in Microsoft Word
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Exit UserForm if too many characters selected
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
...
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 628
- Joined: 10 Jan 2016, 15:56
- Location: Madison, Wisconsin
Re: Exit UserForm if too many characters selected
Thank you Hans.
Attorney Charles Kenyon
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
-
- 4StarLounger
- Posts: 587
- Joined: 14 Nov 2012, 16:06
Re: Exit UserForm if too many characters selected
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
-
- 5StarLounger
- Posts: 628
- Joined: 10 Jan 2016, 15:56
- Location: Madison, Wisconsin
Re: Exit UserForm if too many characters selected
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.
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
Modifying the QAT
The Importance of Styles in Microsoft Word
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 628
- Joined: 10 Jan 2016, 15:56
- Location: Madison, Wisconsin
Re: Exit UserForm if too many characters selected
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.
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
Modifying the QAT
The Importance of Styles in Microsoft Word
Templates in Microsoft Word
Modifying the QAT
The Importance of Styles in Microsoft Word
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Exit UserForm if too many characters selected
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 ).
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 ).
Best wishes,
Hans
Hans