userForm input for filename

savvy · #1 08-24-2020, 02:01 PM

This is my UserForm:

Option Explicit

Private Sub CommandButton1_Click()

Dim PTFN As Range
Set PTFN = ActiveDocument.Bookmarks("PTFN").Range
PTFN.Text = Me.TextBox1.Value

Dim PTLN As Range
Set PTLN = ActiveDocument.Bookmarks("PTLN").Range
PTLN.Text = Me.TextBox2.Value

Dim DRFN As Range
Set DRFN = ActiveDocument.Bookmarks("DRFN").Range
DRFN.Text = Me.TextBox3.Value

Dim DRLN As Range
Set DRLN = ActiveDocument.Bookmarks("DRLN").Range
DRLN.Text = Me.TextBox4.Value
Me.Repaint
UserForm1.Hide

End Sub

Private Sub UserForm_Click()

End Sub

I would like to also have a macro which will place "PTLN, PTFN" into save as box in the proper folder. I tried just putting the text as the first line of the document but only the text of PTLN will be accepted and the ", text of PTFN" will not appear.

The document is a template.dotm which gets altered by several macros. The way I invoke save as is with F12.

I tried the below code but am too inexperienced to understand what the errors mean and how to fix them.
Sub SaveForm()
Dim strPath As String
Dim strName As String
strPath = Environ("USERPROFILE") & "\Documents"
' strName = ActiveDocument.FormFields("FieldName").Result
strName = ActiveDocument.Bookmarks("PTFN").Range

If strName = "" Then
MsgBox "Complete the 'fieldname' field!"
ActiveDocument.Bookmarks("PTFN").Select
Exit Sub
End If
With Dialogs(wdDialogFileSaveAs)
.Name = strPath & strName
.Show
End With
End Sub

Thanks

Guessed · #2 08-24-2020, 04:11 PM

When you say you want those in the Save Box, do you want the static text of "PTLN, PTFN' or do you want the values entered into the userform text boxes?

If you want to pre-populate the default filename when you press F12, you should write that value to the document's Title property. eg
ActiveDocument.BuiltInDocumentProperties(wdPropert yTitle) = "This is a default filename"

In terms of errors on the second code, check your bookmarks actually exist. When your CommandButton1 code runs, it finds the bookmarks and writes text there but there is an insidious bug that removes the bookmark as part of that writing. So you need to reinstate the bookmark after setting the text value if you are going to need the bookmark in the document afterwards.

Code:

Dim PTFN As Range
Set PTFN = ActiveDocument.Bookmarks("PTFN").Range
PTFN.Text = Me.TextBox1.Value
ActiveDocument.Bookmarks.Add Name:="PTFN", Range:=PTFN

Refer to this link for some additional bookmark info Working with Bookmarks in VBA.

I note also that your SaveForm code is not a replacement for the action of pressing F12 unless you have set that as a shortcut key.

gmayor · #3 08-24-2020, 08:25 PM

There is no need to use F12. As you have the information in your userform, you can simply use it to save the document. I also recommend testing to ensure that the bookmarks are present as they are easily deleted (or better still use content controls)
https://www.gmayor.com/insert_content_control_addin.htm will convert your bookmarks to content controls.

For bookmarks

Code:

Option Explicit

Private Sub CommandButton1_Click()
Dim oRng As Range
Dim strPath As String

    If TextBox1.Text = "" Then
        MsgBox "Complete PTFN"
        TextBox1.SetFocus
        Exit Sub
    End If
    If TextBox2.Text = "" Then
        MsgBox "Complete PTLN"
        TextBox2.SetFocus
        Exit Sub
    End If
    If TextBox3.Text = "" Then
        MsgBox "Complete DRFN"
        TextBox3.SetFocus
        Exit Sub
    End If
    If TextBox4.Text = "" Then
        MsgBox "Complete DRLN"
        TextBox3.SetFocus
        Exit Sub
    End If


    If ActiveDocument.Bookmarks.Exists("PTFN") = True Then
        Set oRng = ActiveDocument.Bookmarks("PTFN").Range
        oRng.Text = TextBox1.Text
        ActiveDocument.Bookmarks.Add "PTFN", oRng
    End If

    If ActiveDocument.Bookmarks.Exists("PTLN") = True Then
        Set oRng = ActiveDocument.Bookmarks("PTLN").Range
        oRng.Text = TextBox2.Text
        ActiveDocument.Bookmarks.Add "PTLN", oRng
    End If

    If ActiveDocument.Bookmarks.Exists("DRFN") = True Then
        Set oRng = ActiveDocument.Bookmarks("DRFN").Range
        oRng.Text = TextBox3.Text
        ActiveDocument.Bookmarks.Add "DRFN", oRng
    End If

    If ActiveDocument.Bookmarks.Exists("DRLN") = True Then
        Set oRng = ActiveDocument.Bookmarks("DRLN").Range
        oRng.Text = TextBox4.Text
        ActiveDocument.Bookmarks.Add "DRLN", oRng
    End If

    strPath = Environ("USERPROFILE") & "\Documents\"
    ActiveDocument.SaveAs strPath & TextBox2.Text & TextBox1.Text & ".docx"

    Set oRng = Nothing
    Unload Me
End Sub

for content controls

Code:

Option Explicit

Private Sub CommandButton1_Click()
Dim oCC As ContentControl
Dim strPath As String

    If TextBox1.Text = "" Then
        MsgBox "Complete PTFN"
        TextBox1.SetFocus
        Exit Sub
    End If
    If TextBox2.Text = "" Then
        MsgBox "Complete PTLN"
        TextBox2.SetFocus
        Exit Sub
    End If
    If TextBox3.Text = "" Then
        MsgBox "Complete DRFN"
        TextBox3.SetFocus
        Exit Sub
    End If
    If TextBox4.Text = "" Then
        MsgBox "Complete DRLN"
        TextBox3.SetFocus
        Exit Sub
    End If

    For Each oCC In ActiveDocument.ContentControls
        Select Case oCC.TITLE
            Case "PTFN": oCC.Range.Text = TextBox1.Text
            Case "PTLN": oCC.Range.Text = TextBox2.Text
            Case "DRFN": oCC.Range.Text = TextBox3.Text
            Case "DRLN": oCC.Range.Text = TextBox4.Text
        End Select
    Next oCC


    strPath = Environ("USERPROFILE") & "\Documents\"
    ActiveDocument.SaveAs strPath & TextBox2.Text & TextBox1.Text & ".docx"

    Set oCC = Nothing
    Unload Me
End Sub

Guessed · #4 08-24-2020, 10:08 PM

I usually try to modularise the code to make it easier to maintain the userforms and add/remove elements on the userform without having to update the code as well. The following code assumes you have populated the Tag property of each textbox on your userform and then you can use that tag value to find the relevant bookmark or content control that aligns with that textbox.

Code:

Private Sub CommandButton1_Click()
  Dim aCtrl As Control, sTag As String, strPath As String, aRng As Range
  For Each aCtrl In Me.Controls
    Select Case TypeName(aCtrl)
      Case "TextBox"
        sTag = aCtrl.Tag   'aCtrl.Name 'if you prefer
        If ActiveDocument.Bookmarks.Exists(sTag) Then
          Set aRng = ActiveDocument.Bookmarks(sTag).Range
          aRng.Text = aCtrl
          ActiveDocument.Bookmarks.Add Name:=sTag, Range:=aRng
        ElseIf ActiveDocument.SelectContentControlsByTitle(sTag).Count > 0 Then
          ActiveDocument.SelectContentControlsByTitle(sTag)(1).Range.Text = aCtrl
        End If
    End Select
  Next aCtrl
  strPath = Environ("USERPROFILE") & "\Documents\"
  ActiveDocument.SaveAs strPath & Me.TextBox2 & Me.TextBox1 & ".docx"
  Me.Hide
End Sub

gmayor · #5 08-24-2020, 10:32 PM

I agree, though I find new users tend to have more problems understanding such an approach.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
filename field not displaying correct filename when that name starts with #	plrsmith	Word	1	07-06-2018 03:10 AM
Filename	UnlimitedPower	Word VBA	1	08-19-2016 12:22 AM
Userform calls other userform, then populate worksheet	Lehoi	Excel Programming	0	02-03-2016 02:58 PM
VBA Code in a UserForm module to delete a Command Button which opens the userform	Simoninparis	Word VBA	2	09-21-2014 03:50 AM
Is it possible to take an input from a UserForm in one document to a UserForm in a do	BoringDavid	Word VBA	5	05-09-2014 09:08 AM