Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Aug 2013
    Posts
    46
    Thanks
    11
    Thanked 0 Times in 0 Posts

    WORD VBA that pull content of text box

    I am trying to write VBA that will let the user type text into text box, and save the file as "text".docm.
    For example, if the text box includes "aa", the result would be saving this file as aa.docm file.

    Since I am new to VBA, I tried recording a macro and use the code as reference, but themacro wouldnt let me type into the text box.

    Any ideas?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    YigalB,

    Welcome to the Lounge as a new poster!

    This should do the trick:
    Code:
    Sub MySaveFile()
    
       Dim zFileName  As String
       
       zFileName = InputBox("Please Enter the desired file name.", "User Entry Required")
       
       If zFileName = "" Then
         MsgBox "File NOT saved you did not supply a filename!", _
                vbOKOnly + vbCritical, "Error: Filename missing!"
       Else
         ChangeFileOpenDirectory "[Replace With Drive/Path where document is to be stored]"
         
         ActiveDocument.SaveAs2 FileName:=zFileName & ".docm", FileFormat:= _
            wdFormatXMLDocumentMacroEnabled, LockComments:=False, Password:="", _
            AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
            EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
            :=False, SaveAsAOCELetter:=False, CompatibilityMode:=14
    
       End If
       
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Aug 2013
    Posts
    46
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Thanks for the welcome. This forum looks impressive!

    And thanks for the code: it works great.
    I had in mind something else: a pre-prepared text box in the word document, that will keep the same value.
    But I like the direction you suggested, because the next step I had in mind was to ask several questions, each with few pre-made answers, and paste them to the document.
    Could you help with:
    1- Start as you suggested, and paste the answer to the document, something like" "the file name is xxxxx"
    2- ask the user a question (e.g. how are you today), supply several answers (e.g. 1- good, 2-bad, 3- i have no idea), let the user chose and paste "the user is good" (in case the 1st was chosen).
    3- save the document and exit

  4. #4
    Lounger
    Join Date
    Aug 2013
    Posts
    46
    Thanks
    11
    Thanked 0 Times in 0 Posts
    It seems the forces of good computing are with me, as you wished.

    I learned how to paste the input into the word file. I still need to format it better, but that can wait.

    Next step is to display a multiple choices and let the user select one, and paste it to the file - similar to combo, but interactive. Is that possible?

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Willow Grove, Pennsylvania, USA
    Posts
    205
    Thanks
    4
    Thanked 49 Times in 40 Posts
    Quote Originally Posted by YigalB View Post
    Next step is to display a multiple choices and let the user select one, and paste it to the file - similar to combo, but interactive. Is that possible?
    The simplest way to do that is to use a dropdown list content control. Take a look at http://gregmaxey.mvps.org/word_tip_p..._controls.html for information. If you just want the user's selection to appear in the document text, you don't have to do anything else besides inserting the control and adding the items to its list (in its Properties dialog).

  6. #6
    Lounger
    Join Date
    Aug 2013
    Posts
    46
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jjfreedman View Post
    The simplest way to do that is to use a dropdown list content control. Take a look at http://gregmaxey.mvps.org/word_tip_p..._controls.html for information. If you just want the user's selection to appear in the document text, you don't have to do anything else besides inserting the control and adding the items to its list (in its Properties dialog).
    That was my plan at first.
    Then RetiredGeek suggested interactive text box which changed my mind, because this way the user will not be able to skip choosing questions. Also, I think it will be easier for to maintain changes in the future by programming, rather than re-editing the document.

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    YiagalB,

    Ok this is a little more complicated but not much. See the attached Word document for details.

    First you'll need to create a UserForm (Insert->Userform in the VBE).
    UserFormWord.JPG

    Use the tools menu to drag and drop items on the form size and place them then give them properties.

    Next, you click on each created item and write the code to control how it reacts.

    Lastly, you write the driver code in a module to show the form and process the results.
    UserFormTest.JPG

    I hope this is what you were looking for.

    HTH
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Lounger
    Join Date
    Aug 2013
    Posts
    46
    Thanks
    11
    Thanked 0 Times in 0 Posts
    I appreciate the effort!
    I will need some time to understand what you did and how it works. Very good practice for me.

    I assume you took this direction because there is no interactive drop down menu.

  9. #9
    Lounger
    Join Date
    Aug 2013
    Posts
    46
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi Guys
    I think I a calling it a day. So far I created a button that opens a new document (to keep the master document untouched), calls subroutine that collects the file name, saving it with the proper name, and prints some information to the document.
    I also programmed the drop down menu list, but since I need about a dozen of this, I think I need to learn how to call a subroutine with parameters, so the code will be readable and easy to maintain.
    I will also need to learn how to get the chosen value of the user after each time.

    But these will have to wait a day. Thank you so much so far!

  10. #10
    Lounger
    Join Date
    Aug 2013
    Posts
    46
    Thanks
    11
    Thanked 0 Times in 0 Posts
    At this stage I know how to add text, basic formatting, and add drop down menu with VBA.
    Two questions:
    1- where can I find a list of all the font controls (colors, alignment, etc.)
    2- When I add new line text to the document, i used
    .TypeParagraph
    , but once I added drop down menu, it moved down even if I placed the
    .TypeParagraph
    after the drop down menu. What can I do in order to place each text line and each menu as I want?

  11. #11
    Lounger
    Join Date
    Aug 2013
    Posts
    46
    Thanks
    11
    Thanked 0 Times in 0 Posts
    I learned how to control the order of the text and drop down boxes, but now I have an issue with two things: saving the file and office comatability (perhaps I should open new thread for that). As for saving the file:
    i am using the code as recommended by RetiredGeek:
    Code:
    Sub MySaveFile()
    
       Dim zFileName  As String
       
       zFileName = InputBox("Please Enter the desired file name.", "User Entry Required")
       
       If zFileName = "" Then
         MsgBox "File NOT saved you did not supply a filename!", _
                vbOKOnly + vbCritical, "Error: Filename missing!"
       Else
         ChangeFileOpenDirectory "[Replace With Drive/Path where document is to be stored]"
         
         ActiveDocument.SaveAs2 FileName:=zFileName & ".docm", FileFormat:= _
            wdFormatXMLDocumentMacroEnabled, LockComments:=False, Password:="", _
            AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
            EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
            :=False, SaveAsAOCELetter:=False, CompatibilityMode:=14
    
       End If
       
    End Sub
    At first, I used absolute path instead of
    Replace With Drive/Path where document is to be stored
    It works fine, but now I need to make it work on any computer, so I tried using ".", but it didn't work.
    Any idea?

    two more questions about saveAs:
    - why is it SaveAs2? why not simply SaveAs?
    - Do I need to supply all the
    LockComments:=False, Password:="", _
    AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
    EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
    :=False, SaveAsAOCELetter:=False, CompatibilityMode:=14
    parameters?
    Last edited by YigalB; 2013-08-12 at 11:23.

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    YigalB,

    If you want to prompt the user for the directory create a new module with this code:
    Code:
    Option Explicit
    
    '                        +--------------------------+             +----------+
    '------------------------|Windows Function Type Defs|-------------| 08/11/05 |
    '                        +--------------------------+             +----------+
    Public Type BROWSEINFO
        hOwner As Long
        pidlRoot As Long
        pszDisplayName As String
        lpszTitle As String
        ulFlags As Long
        lpfn As Long
        lParam As Long
        iImage As Long
    End Type
    
    '                         +-------------------------+             +----------+
    '-------------------------|     zGetDirectory()     |-------------| 07/25/05 |
    '                         +-------------------------+             +----------+
    'Calls: N/A
    'Notes: This function will bring up a form to let the user select a directory
    
    Public Function zGetDirectory(Optional Msg) As String
    
        Dim bInfo As BROWSEINFO
        Dim zPath As String
        Dim lRetVal2 As Long, lRetVal As Long, iEndOfStr As Integer
    
        bInfo.pidlRoot = 0  '*** Root folder = Desktop ***
    
    '***   Title in the dialog ***
        If IsMissing(Msg) Then
            bInfo.lpszTitle = "Select a Drive/Directory."
        Else
            bInfo.lpszTitle = Msg
        End If
    
        bInfo.ulFlags = &H1  '*** Type of directory to return ***
        lRetVal = SHBrowseForFolder(bInfo)  '*** Display the dialog ***
        zPath = Space$(512)     '*** Parse the result ***
        lRetVal2 = SHGetPathFromIDList(ByVal lRetVal, ByVal zPath)
        If lRetVal2 Then
            iEndOfStr = InStr(zPath, Chr$(0))
            zGetDirectory = Left(zPath, iEndOfStr - 1)
        Else
            zGetDirectory = ""
        End If
        
    End Function             'zGetDirectory(Optional Msg)
    Then replace:
    Code:
    Sub MySaveFile()
    
       Dim zFileName  As String
       
       zFileName = InputBox("Please Enter the desired file name.", "User Entry Required")
       
       If zFileName = "" Then
         MsgBox "File NOT saved you did not supply a filename!", _
                vbOKOnly + vbCritical, "Error: Filename missing!"
       Else
         ChangeFileOpenDirectory "[Replace With Drive/Path where document is to be stored]"
         
         ActiveDocument.SaveAs2 FileName:=zFileName & ".docm", FileFormat:= _
            wdFormatXMLDocumentMacroEnabled, LockComments:=False, Password:="", _
            AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
            EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
            :=False, SaveAsAOCELetter:=False, CompatibilityMode:=14
    
       End If
       
    End Sub
    With:
    Code:
    Sub MySaveFile()
    
       Dim zFileName  As String
       Dim zDirName   As String
    
       zFileName = InputBox("Please Enter the desired file name.", "User Entry Required")
       
       If zFileName = "" Then
         MsgBox "File NOT saved you did not supply a filename!", _
                vbOKOnly + vbCritical, "Error: Filename missing!"
       Else
         zDirName = zGetDirectory("Select the desired drive\path")
         If zDirName = "" Then Exit Sub   '*** User Cancelled ***
    
         ChangeFileOpenDirectory zDirName
         
         ActiveDocument.SaveAs2 FileName:=zFileName & ".docm", FileFormat:= _
            wdFormatXMLDocumentMacroEnabled, LockComments:=False, Password:="", _
            AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
            EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
            :=False, SaveAsAOCELetter:=False, CompatibilityMode:=14
    
       End If
       
    End Sub
    No you do NOT need to supply all the values in the SaveAs2 command as the defaults are present. SaveAs2 added in 2010. HTH
    Last edited by RetiredGeek; 2013-08-12 at 12:35.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •