Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Save Userform Only

  1. #1
    Join Date
    4th May 2007
    Location
    USA
    Posts
    12

    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?
    VB:
    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    23rd April 2007
    Posts
    3,335

    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...)

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    4th May 2007
    Location
    USA
    Posts
    12

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    23rd April 2007
    Posts
    3,335

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    4th May 2007
    Location
    USA
    Posts
    12

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Save Userform Only

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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    4th May 2007
    Location
    USA
    Posts
    12

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    4th May 2007
    Location
    USA
    Posts
    12

    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.
    VB:
    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!

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,427

    Re: Save Userform Only

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

    Roy

    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

    About me.

  10. #10
    Join Date
    4th May 2007
    Location
    USA
    Posts
    12

    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?
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Save UserForm To Worksheet
    By oediaz in forum EXCEL HELP
    Replies: 3
    Last Post: October 24th, 2007, 12:53
  2. Remove Userform Upon Save As
    By adresmith in forum EXCEL HELP
    Replies: 4
    Last Post: April 11th, 2007, 01:07
  3. Display Userform 5 Seconds Before Save
    By BorneoHornbill in forum EXCEL HELP
    Replies: 3
    Last Post: September 15th, 2006, 03:50
  4. Controls not saving to userform after save
    By Eric Smith in forum EXCEL HELP
    Replies: 15
    Last Post: February 23rd, 2006, 21:03
  5. save data from userform into worksheet
    By von in forum EXCEL HELP
    Replies: 30
    Last Post: October 4th, 2004, 03:47

Bookmarks

Posting Permissions

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