VBA Word Macro to Allow User to Select and Copy Text Multiple Times

Question

I am working on a VBA script with Microsoft Word that allows the user to select text that will be be copied to the clipboard so that it can exported to an Excel file. The user will make a number of selections and finally indicate he/she is done when the contents of the clipboard will be copied to a template Excel file.

There are two forms: the first (UserForm1 in the code below) queries the user for the Word filename. The filename variable is passed to the second form. The second form (frmModeLessForInput) is a modeless form. The behavior I need is that program control goes to the second form with two buttons "Continue" and "Done".

The user is allowed to navigate the document and place the cursor anywhere in the document. Then when "Continue" is pressed the form will call a subroutine (Highlight_Sentence) to copy the selected text to a "clipboard" variable. When "Done" is pressed control will be passed to called main module which will then copy the clipboard to the Excel file.

Below is the code. I have noted with comments where I am trouble with the code. One problem is the variables defined as Public in the ThisDocument module are not defined in the userforms and their subroutines.

The second problem is in the main module that the frmModelessForInput is supposed to be displayed and control is not supposed to be transferred to next statement {MsgBox "Sentences will now be copied to Excel file"....this is where I will put the code to copy the clipboard to the Excel file.} but the message appears before the frmModelessForInput form is run...thus the clipboard will be empty.

The third problem is that in frmModelessForInput form the statement str_clipboard = str_clipboard + str_clipboard_line is not working. Each time the "Continue" button is pushed str_clipboard loses it previous contents.

Any assistance in resolving these problems is appreciated. As VBA programming is a sideline for me I am still learning.

Note this an updated question Pause VBA Word macro, allow user to make a selection, and restart where it left off adding some more detail on the requirement and the sample code.

MAIN MODULE:

Option Explicit
Public str_clipboard As String
Public txt_active_document As String
Public i_how_many_sentences As Integer
Private Sub Test_master_macro()
    UserForm1.Show
    i_how_many_sentences = 0
    Call DisplayModeless
    MsgBox "Sentences will now be copied to Excel file" 'Problem: this msg displays before the frmModelessForInput is displayed
End Sub
Sub DisplayModeless()
    Dim frm As frmModelessForInput
    Set frm = New frmModelessForInput
    With frmModelessForInput
        .str_word_doc_filename = txt_active_document
        .str_no_copied = "0"
        .Show False
    End With
    Set frm = Nothing
End Sub

USERFORM1: form has field for user entering the document filename to user (str_filename) and a command button to close form (cmd_start_selecting_text)

Private Sub cmd_start_selecting_text_Click()
    'User enters filename on form for use in frmModelessForInput subroutine 
    txt_active_document = UserForm1.str_filename  'Problem: VBA reports txt_active_document as undefined even though it is a Public variable
    Unload Me
End Sub

FRMMODELESSFORINPUT: Form displays filename of Word file entered in UserForm1 and how many sentences have been copied to the clipboard

Option Explicit

Private Sub cmdContinue_Click()
 Dim str_clipboard, str_clipboard_line As String
       Call Highlight_Sentence(str_clipboard_line)
        i_how_many_sentences = i_how_many_sentences + 1 'Problem: VBA reports i_how_many_sentences as undefined even though it is a Public variable 
        frmModelessForInput.str_no_copied = i_how_many_sentences  'Same Problem
    str_clipboard = str_clipboard + str_clipboard_line 'Problem: each time I select a new text/sentence str_clipboard does not contain the contents of the previous selection
End Sub


Private Sub cmdDone_Click()
    Me.Hide
End Sub

Private Sub UserForm_Activate()
    'Position the form near the top-left of the window
    'So that the user can work with the document
    Me.Top = Application.ActiveWindow.Top + 15
    Me.Left = Application.ActiveWindow.Left + 15
End Sub

Private Sub Highlight_Sentence(clipboard As String)
    'This sub extends the selection to the entire sentence and copies the selection, the page number on which the selection is contained and the filename to the clipboard variable
    Dim txt_sentence, txt_page_no As String
    With Selection
     ' Collapse current selection.
     .Collapse
     ' Expand selection to current sentence.
     .Expand Unit:=wdSentence
    End With
    txt_sentence = Selection.Text
    txt_page_no = Selection.Information(wdActiveEndPageNumber)
    clipboard = txt_active_document & vbTab & txt_page_no & vbTab & txt_sentence & vbCrLf 'Problem: VBA reports txt_active_document as undefined even though it is a Public variable 
End Sub

Rich Michaels Rich Michaels · Accepted Answer · 2018-08-30T02:22:58

From what you stated you are running this from the ThisDocument Class Module and unless you fully qualify your references to those Public variables with the Class Name that is why you cannot access them from the UserForms Class Modules.

If you are going to leave your "Main Module" code in the ThisDocument Class Module then whenever you reference those Public variable you need to add ThisDocument.str_clipboard to the command.

I recommend however, to place your Main Module in a general Module such as NewModule and if you need to run it at a Document_Open event that you put a call to the Main Module and its Public variables in the Private Sub Document_Open event of the ThisDocument Class Module.

Your Msgbox is appearing at the wrong time because you are displaying a modeless user form, which means the VBA script thread continues to run after the UserForm is displayed. Move the Msgbox to the UserForm_Activate routine of the second UserForm you are displaying or move it to the Click_Done routine of the first UserForm before you Hide or Unload it.

Finally, you are not really using the Clipboard and using that term makes your code confusing in my opinion. I think you should rename it. Your code also appears to just be building one continuous string of text. Is that really what you want to do? Or, do you really mean to capture each selected text string and ultimately place each into separate cells within an Excel Worksheet? If that is the case, use an Array for the separate text strings.

VBA Word Macro to Allow User to Select and Copy Text Multiple Times

1 Answers