Create filename using the UserForm text box with VBA in Word

Question

In Word 2007, I created a Macro-Enabled Template that uses Form Fields with various calculations/formulas. I am trying to automate the .SaveAs based on the input of a text box from a UserForm with a command button click.

How do I dim and set the input text to use in the filename?

I have additional code to create an invoice number that I pull into the filename and that works. I'm just having problems with how to store the input text from the UserForm text box so that I can use in my AutoNew subroutine.

Sub AutoNew()

frmCustName.Show

Dim Cust As String
Dim RA as String

RA = ActiveDocument.Bookmarks("RntlAgrNO").Range.Text
Cust = txtInput
ActiveDocument.SaveAs FileName:="C:\MyPath\" & RA & Cust & ".docm"

End Sub

Here is my UserForm macro.

My UserForm = "frmCustName"
My TextBox = "txtInput"
My CommandButton = "cmdName"

This populates the input text into two bookmarks in my form. How do I store this txtInput so that I can call it in my AutoNew code?

Private Sub cmdName_Click()

Dim BK As String
BK = txtInput

Dim NameBK1 As Range
Set NameBK1 = ActiveDocument.Bookmarks("CName1").Range
NameBK1.Text = BK

Dim NameBK2 As Range
Set NameBK2 = ActiveDocument.Bookmarks("CName2").Range
NameBK2.Text = BK

frmCustName.Hide

End Sub

If you preserve the bookmarks when populating the text (see stackoverflow.com/a/72873872/478884) then after the form closes you can do something like Cust = ActiveDocument.Bookmarks("CName1").Range.Text — Tim Williams
– Tim Williams, Commented Apr 12, 2024 at 21:12

Panzer · Accepted Answer · 2024-04-15 16:06:18Z

0

You should use content controls instead of bookmarks. Bookmarks are deleted when you change their text. Content controls aren't.

Add a content control in your document and name it "CName1". Then you can set content control text like this aDoc.SelectContentControlsByTitle("CName1").Item(1).Range.Text = txtInput.Text

But you don't really need to store txtInput text. Here is my code. Put it in cmdName_Click event, it will save your file under new name when user presses the button after filling txtInput:

Private Sub cmdName_Click() 'put command button in your userform

    Dim aDoc As Document 'you can also declare it as Public variable in a module
    Set aDoc = ActiveDocument
    Dim folder As String

    If txtInput.Text = "" Then 'check if user filled txtInput first
       MsgBox "remider for the user to fill txtInput", vbInformation + vbOKOnly, "ATTENTION"
       Exit Sub
    End If

    'Then you need to push that text in your filename
    folder = "D:\Документы Оля\Тест"
    aDoc.SelectContentControlsByTitle("CName1").Item(1).Range.Text = txtInput.Text 'in case you want to store txtInput value, but in my code you don't need it
    aDoc.SaveAs2 folder & "\" & "fixed part of your filename" & txtInput.Text & ".docm"

End Sub

If you want to use bookmarks anyway, put those bookmarks in content control "CC_values" which can't be deleted and is locked. Then hide CC_values content control (change font property to hidden). After that put the procedure below in a module and call it whenever you need to rewrite bookmark text (it stores range of a bookmark then changes text in that range and adds a bookmark with the same name to that range):

Sub Set_Bookmark_Range_Value(ByRef pBMName As String, pText As String)

Dim aDoc As Document
Set aDoc = ActiveDocument

aDoc.SelectContentControlsByTitle("CC_values").Item(1).LockContents = False 'unlocking content control that stores bookmarks assigned to userform controls

    'to change bookmark text (bookmark name = pBMName)
    Dim oRng As Word.Range
    If aDoc.Bookmarks.Exists(pBMName) Then
        Set oRng = aDoc.Bookmarks(pBMName).Range
        oRng.Text = pText
        aDoc.Bookmarks.Add pBMName, oRng
    Else
        MsgBox "Bookmark " & pBMName & " doesn't exist.", vbCritical + vbOKOnly, "DELETED BOOKMARK"
    End If
    
aDoc.SelectContentControlsByTitle("CC_values").Item(1).LockContents = True 'locking content control that stores bookmarks assigned to userform controls
    
lbl_Exit:
    Exit Sub
    
End Sub

edited Apr 15, 2024 at 16:06

answered Apr 13, 2024 at 9:51

Panzer

766 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

tlw Over a year ago

thanks for your input and time. My issue with setting the filename in the cmdName_click is that I want to save the filename with additional info that is stored in the AutoNew code. Is there a way to store the txtInput.Text so that I can use it elsewhere?

Panzer Over a year ago

If you need to store text from userform textbox, write something like that in txtInput_Change event: aDoc.SelectContentControlsByTitle("CName1").Item(1).Range.Text = txtInput.Text. Or use the procedure I gave to change bookmark text without deleteting the bookmark. Later you can access its text (and assign it to a string variable): ActiveDocument.Bookmarks("bkmrk_name_here").Range.Text.

Panzer Over a year ago

Did you have any success?

tlw Over a year ago

Thanks for checking back in. I did not even try this because I don't want to change the place holder to a content controlled field.

Collectives™ on Stack Overflow

Create filename using the UserForm text box with VBA in Word

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related