how to create save button in a userform in excel?

[ellehcer08]

hi there!!

is it possible to create a SAVE BUTTON in a userform and when clicked, it will saved only the words in the textbox and the listbox of the userform in a microsoft word file?or in another excel file... if so, please tell me how to do it.. thanks a lot...

chel

 

[Trevor G]

If you want to transfer what is in a userform to a word document you would look to add the Reference to Word via the VBA screen in Excel and also the Tools Menu and search down the list until you find Microsoft Word XX.Object Library (XX is the version number you are using). Then add some create a Command button on the UserForm and then name it something like cmdSave then double click the button and add code like this:

Assuming the word document already exist you would need to set bookmarks in the document and then you can goto the bookmark

cmdSave_Click()
Dim wrdApp as Word.Application
Set wrdApp = CreateObject("Word.Application")
Application.ScreenUpdating = False

With wrdApp
.Documents.Open "Path to Doc.doc" 'Place in full path and Document name including .doc
.Selection.GoTo What:=wdGoToBookmark, Name:="test1" 'Using a bookmark in the document
.Selection.TypeText Text:= me.textboxname
.Visible = True
.Documents.Save

end with
Application.ScreenUpdating = True
MsgBox "Data Copied Across", vbInformation, "Sample"

End Sub

 

[ellehcer08]

hi! i have a code here, it saves it to another excel file..

what the button does:
1) it opens another workbook and copy the sheet from the original excel file to the new excel workbook.
2) the SAVE AS automatically pops open with the TITLE (txtRecipeName) indicated in the sheet.

but when i click the SAVE button in the SAVE AS window, the last part says "........To save the VBA project in the Excel 5.0/95 format, search Microsoft Office Online for "VBA converters, YESNOCANCEL"
what is wrong with my code?

here's the code:

Private Sub cmdSave_Click()

    answer = MsgBox("SAVE this list to another worksheet?", vbQuestion + vbYesNo)
        If answer = vbNo Then Exit Sub
        If answer = vbYes Then

    Sheets("IngredientList").Select
    Sheets("RecipeCalc").Select
    Sheets("IngredientList").Copy
    Range("C2").Select
    Application.WindowState = xlMinimized
    
    Filename = Application.GetSaveAsFilename(txtRecipeName, "Excel Workbook (*.xlsm), *.xlsm")
    If Filename <> False Then ActiveWorkbook.SaveAs Filename, xlExcel7
    Unload Me
    
    End If
      
End Sub

Kindly help! thanks a lot...

 

[Trevor G]

This seems to work, I have used an Array on your sheet names

Dim answer As String, filename As Object
answer = MsgBox("SAVE this list to another worksheet?", vbQuestion + vbYesNo)
If answer = vbNo Then Exit Sub
If answer = vbYes Then
Sheets(Array("IngredientList", "RecipeCalc")).Copy
' Sheets("IngredientList").Select
' Sheets("RecipeCalc").Select
' Sheets("IngredientList").Copy
Range("C2").Select
Application.WindowState = xlMinimized
Application.Dialogs(xlDialogSaveAs).Show txtRecipeName



' filename = Application.GetSaveAsFilename(txtRecipeName, "Excel Workbook (*.xlsm), *.xlsm")
' If filename <> False Then ActiveWorkbook.SaveAs filename, xlExcel12
Unload Me

End If

 

[ellehcer08]

hi trevor! thanks for the code... PERFECT! love it..! but one thing.. is there any chance that it can be saved automatically as a macro-enabled worksheet? but anyhow, thanks a lot for the big help!

 

[Trevor G]

I have to ask, if the sheets are being copied is there code behind the sheets as copying to a new workbook with no code being added doesn't add up, I maybe wrong though in this case !

Glad the code works for you though.

 

[ellehcer08]

i omitted the "RecipeCalc" here in your code.. i just want the "ingredientList" sheet to be copied to another workbook..

Sheets(Array("IngredientList", "RecipeCalc")).Copy

but the ingredientList sheet contains a macro, which is the printpreview... but even though i saved it as a "regular" workbook, the print preview works perfectly fine...

thanks again