Announcement

Collapse
No announcement yet.

Save Userform Only

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Save Userform Only



    Good afternoon! I'm not having much luck here and have searched and haven't found help. I want to create a userform that users can fill out and then be able to save so that it can be emailed/saved with that data. I guess my first question is can this be done? What I have so far is the userform and a save button. Now what do I need to do to allow a user to populate this form and save the form with the data so that it can be attached in an email or just saved to a folder? Or is there an easier way?
    Code:
    Private Sub CommandButton1_Click()
        Application.ScreenUpdating = False
        Dim Fname As Variant
        ' Opens Save dialog box
        Do
        Fname = Application.GetSaveAsFilename( _
        fileFilter:="Excel Files (*.xls), *.xls")
        Loop Until Fname <> False
        ActiveWorkbook.SaveAs Filename:=Fname
        Application.ScreenUpdating = True
        Unload UserForm1
        ThisWorkbook.Close True
        
    End Sub
    
    Private Sub UserForm_Initialize()
    
    End Sub
    Any help to get me in the right direction would be appreciated! Thanks!

  • #2
    Re: Save Userform Only

    When you save or send a userform the data in the controls is set to the default values rather than those the user set. It would be easiest to move the data onto a worksheet and e-mail that sheet. (or export to Word for fomatting or...)

    Comment


    • #3
      Re: Save Userform Only

      Thanks for the response! So, the user would need to fill in the userform and then I would need to export that to possibly Word? How do I accomplih that? I am new to VB and don't know how to do that. Thanks!

      Comment


      • #4
        Re: Save Userform Only

        I don't know how to do that either. A spreadsheet designed for easy reading and display of the data could be designed. (That's the way I would go.) I just mentioned Word in case your circumstance had unusual conditions (i.e. a weird boss). If that is the case, other more knowlegable folks, will be able to help you.

        I'd e-mail an Excel worksheet to people, after it has been set by the userform entries.

        Comment


        • #5
          Re: Save Userform Only

          Ok, so how do I capture what the user inputs on the form? The form has 4 text boxes and 8 checkboxes. Thanks.

          Comment


          • #6
            Re: Save Userform Only

            Userforms aren't really meant to be either sent or printed.
            Boo!

            Comment


            • #7
              Re: Save Userform Only

              Thanks for the information. That's what I initially wanted to do and didn't know if it could be done. Now that I know it can't be done, I need another approach. I want the user to be able to fill out the form and save the results. So, from what I gather, what the user inputs into the form will have to be populated elsewhere (i.e. excel worksheet, word doc). I don't know how to accomplish that. Can anyone help? I've got the userform built. It has four text boxes, eight checkboxes, and a save button to save the user input.

              Comment


              • #8
                Re: Save Userform Only

                Ok...I've almost got it! When executed a user will input values into the userform, then click the submit button. This will populate a worksheet with those values. Then the user clicks the save button. This should create a word document with those values and then save the document. This is the point that I get hung up. When I click on the save button I get a command failed error. The line that is hightlighted is in the save button click event: .ActiveDocument.SaveAs Filename:=SaveAsName. I don't know what is wrong. I've attached all of the code for this userform. Any help would be appreciated.
                Code:
                Option Explicit
                
                Private Sub CommandButton1_Click() 'submit button
                    Sheets("DATA").Activate
                    'make sure text boxes aren't empty
                    If TextBox3.Text = "" Then
                        MsgBox "Enter Date"
                        Exit Sub
                    End If
                    
                    If TextBox4.Text = "" Then
                        MsgBox "Enter Analyst"
                        Exit Sub
                    End If
                    
                    If TextBox1.Text = "" Then
                        MsgBox "Enter Request"
                        Exit Sub
                    End If
                    
                    If TextBox2.Text = "" Then
                        MsgBox "Enter Issue"
                        Exit Sub
                    End If
                    
                    Cells(2, 1) = TextBox3.Text
                    Cells(2, 2) = TextBox4.Text
                    Cells(2, 3) = TextBox1.Text
                        
                    If CheckBox1.Value = True Then Cells(2, 4) = "PT50"
                    If CheckBox2.Value = True Then Cells(2, 5) = "QT04"
                    If CheckBox3.Value = True Then Cells(2, 6) = "QT05"
                    If CheckBox4.Value = True Then Cells(2, 7) = "ALL"
                    If CheckBox6.Value = True Then Cells(2, 8) = "9XX"
                    If CheckBox7.Value = True Then Cells(2, 9) = "CTD"
                    If CheckBox8.Value = True Then Cells(2, 10) = "UDF"
                    If CheckBox5.Value = True Then Cells(2, 11) = "OTHER"
                    
                    Cells(2, 12) = TextBox2.Text
                    
                    'TextBox3.Text = "" 'clears date field
                    'TextBox3.SetFocus 'resets to date field
                      
                    'Unload UserForm1
                    'ThisWorkbook.Close True
                    
                End Sub
                
                Private Sub CommandButton2_Click() 'cancel button
                    Unload UserForm1
                End Sub
                
                Private Sub CommandButton3_Click() 'save button
                '   Creates word doc
                    Dim WordApp As Object
                    Dim Data As Range, message As String
                    Dim Date1 As Date
                    Dim Analyst As String, Request As String, Regionpt50 As String
                    Dim Regionqt04 As String, Regionqt05 As String, Regionall As String
                    Dim Xx As String, Ctd As String, Udf As String, Other As String
                    Dim Issue As String
                    Dim SaveAsName As String
                    
                '   Start Word and create an object
                    Set WordApp = CreateObject("Word.Application")
                    
                '   Information from worksheet
                    Set Data = Sheets("DATA").Range("A2:L2")
                    message = Sheets("DATA").Range("N2")
                    
                '       Assign current data to variables
                        Date1 = Data.Cells(2, 1).Value
                        'Date1 = Format(Data.Cells(2, 1).Value, "dd/mm/yyyy")
                        Analyst = Data.Cells(2, 2).Value
                        Request = Data.Cells(2, 3).Value
                        Regionpt50 = Data.Cells(2, 4).Value
                        Regionqt04 = Data.Cells(2, 5).Value
                        Regionqt05 = Data.Cells(2, 6).Value
                        Regionall = Data.Cells(2, 7).Value
                        Xx = Data.Cells(2, 8).Value
                        Ctd = Data.Cells(2, 9).Value
                        Udf = Data.Cells(2, 10).Value
                        Other = Data.Cells(2, 11).Value
                        Issue = Data.Cells(2, 12).Value
                        
                '       Determine the file name
                        SaveAsName = ThisWorkbook.Path & "\" & Request & ".doc"
                
                '       Send commands to Word
                        With WordApp
                            .Documents.Add
                            With .Selection
                                .Font.Size = 16
                                .Font.Bold = True
                                .Font.Underline = True
                                .ParagraphFormat.Alignment = 1
                                .TypeText Text:="INCORRECT/ADDITIONAL INFORMATION"
                                .TypeParagraph
                                .TypeParagraph
                                .TypeText message
                                .TypeParagraph
                                .TypeParagraph
                                .Font.Size = 12
                                .ParagraphFormat.Alignment = 0
                                .Font.Bold = False
                                .TypeText Text:="Date:" & vbTab & Date1
                                .TypeParagraph
                                .TypeText Text:="Analyst:" & vbTab & Analyst
                                .TypeParagraph
                                .TypeText Text:="Request:" & vbTab & Request
                                .TypeParagraph
                                .TypeText Text:="Region(s):" & vbTab & Regionpt50
                                .TypeParagraph
                                .TypeText Text:="Type:" & vbTab & Xx
                                .TypeParagraph
                                .TypeText Text:="Issue:" & vbTab & Issue
                                .TypeParagraph
                                
                                
                            End With
                                .ActiveDocument.SaveAs Filename:=SaveAsName
                        End With
                
                '   Kill the object
                    WordApp.Quit
                    Set WordApp = Nothing
                    
                '   Clear contents of DATA spreadsheet
                    Sheets("DATA").Activate
                    Range("A2:L2").ClearContents
                    Unload UserForm1
                    'ThisWorkbook.Close True
                End Sub
                Thanks!

                Comment


                • #9
                  Re: Save Userform Only

                  Why not simply save to a worksheet & email the worksheet only?
                  Hope that Helps

                  Roy

                  New users should read the Forum Rules before posting

                  For free Excel tools & articles visit my web site

                  If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                  RoyUK's Web Site

                  royUK's Database Form

                  Where to paste code from the Forum

                  About me.

                  Comment


                  • #10
                    Re: Save Userform Only

                    Thanks royUK! I've figured out how to export to Word and create a document. Now I'm having troubles getting my variables to pass to the document. Any ideas?!?! Does the section in the code where i set the data and assign it to the variables look right?
                    Code:
                    Dim WordApp As Object
                        Dim Data As Range, message As String
                        Dim Dte As Variant
                        Dim Analyst As String, Request As String, Regionpt50 As String
                        Dim Regionqt04 As String, Regionqt05 As String, Regionall As String
                        Dim Xx As String, Ctd As String, Udf As String, Other As String
                        Dim Issue As String
                        Dim SaveAsName As String
                        Dim Fname As Variant
                        
                        
                    '   Start Word and create an object
                        Set WordApp = CreateObject("Word.Application")
                        
                    '   Information from worksheet
                        Set Data = Sheets("DATA").Range("B:B")
                        message = Sheets("DATA").Range("D15")
                        
                    '       Assign current data to variables
                            'Dte = Data.Cells(1, 2).Value
                            Dte = Format(Data.Cells(1, 2).Value, "MM/DD/YYYY")
                            Analyst = Data.Cells(2, 2).Value
                            Request = Data.Cells(3, 2).Value
                            Regionpt50 = Data.Cells(4, 2).Value
                            Regionqt04 = Data.Cells(5, 2).Value
                            Regionqt05 = Data.Cells(6, 2).Value
                            Regionall = Data.Cells(7, 2).Value
                            Xx = Data.Cells(8, 2).Value
                            Ctd = Data.Cells(9, 2).Value
                            Udf = Data.Cells(10, 2).Value
                            Other = Data.Cells(11, 2).Value
                            Issue = Data.Cells(12, 2).Value

                    Comment


                    • #11
                      Re: Save Userform Only

                      Why not just copy the data range to the Clipboard & paste it to Word?
                      Hope that Helps

                      Roy

                      New users should read the Forum Rules before posting

                      For free Excel tools & articles visit my web site

                      If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                      RoyUK's Web Site

                      royUK's Database Form

                      Where to paste code from the Forum

                      About me.

                      Comment


                      • #12


                        Re: Save Userform Only

                        Thanks to all of you for the help! I've figured it out! This site is very helpful!

                        Comment

                        Working...
                        X