98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Saving a Userform
Saving a Userform
Resolved · Urgent Priority · Version 2013
Lone has attended:
Excel VBA Intermediate course
Saving a Userform
Hi,
I am new to VBA and wish to ask how to save a Userform using a cmd button Save or Save as? I tried the below code which actually saves the entire workbook at the selected file path and replaced "ThisWorkbook" with "name of my userform":
Private Sub Saveas_Click()
Filename =Application.GetSaveAsFilename
MsgBox (Filename)
ThisWorkbook.Saveas (Filename) ' this saves the entire wb. replaced "ThisWork" with my userform name and got compile error "Invalid use of property"
End sub
I simply want to save the contents of my userform (using the userform name) to a path on my pc and not the excel workbook.
Also, how can I print specific pages of my Multipages userform by referencing their respective page numbers, similar to the conventional Miscrosoft method of selecting which specific pages to print (e.g., pages 1,3,5 or 5-10, etc ?
Thanks
RE: Saving a Userform
Hi Lone,
Thank you for the forum questions.
The userform will be saved when you save the workbook. If you mean that you need to save the content of the userform (the entered information in the text boxes) you will need to transfer the inputs to a worksheet an save the worksheet. A userform is just like a inputbox. Excel store the information in the computers memory and you will need to tell Excel how you want to use the information.
To printout pages from the userform. Have a look at the code below:
Option Explicit
Private Sub CommandButton1_Click()
Dim curPage As Long
Dim iCtr As Long
curPage = Me.MultiPage1.Value
For iCtr = 0 To Me.MultiPage1.Pages.Count - 1
Me.MultiPage1.Value = iCtr
Me.PrintForm
Next iCtr
Me.MultiPage1.Value = curPage
End Sub
This code will print out all pages if you change the name in the code to the names you use.
To printout specific pages you can use a input box and then store the values entered in a 1 dimension array.
Then you can loop through the array and printout the entered page numbers.
I hope that I have guided you in the right direction.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Saving a Userform
Hi Jens. Thanks for your prompt response. The userform is an output from solver(invoked through a cmd button that links to the solve button in solver and displays results through a separate userform to my multipages form. I added some other items such as labels, textboxes (one with date and time stamp) to this userform. I do not wish to transfer the contents to a ws but rather save them to .docx or pdf (tried pdf but requires some software installation).
As regards printing my multipages (which has 10 pgs), I have the below code which prints everything except pg1:
Private Sub CommandButton6_Click()
Application.Dialogs(xlDialogPrinterSetup).Show
DisAbleCurrentPage
setting it back to 1 to 10 to be consitent with multipage form GUI
For i = 2 To 10 'setting it as 1 to 10 to be consitent with my multipages brings some weird error, so now using 2-10 which works except no pg1
If currentPage = i Then
Else
currentPage = i
End If
MoveToNextPreviousPage
Select Case currentPage
Case 2
For j = 0 To 3
cboGoalID.ListIndex = j
Me.PrintForm
Next j
Case 3
For j = 0 To 5
cboProjectID.ListIndex = j
Me.PrintForm
Next j
Case 5
For j = 0 To 5
cboProjectIDTimeDemand.ListIndex = j
Me.PrintForm
Next j
Case 6
For j = 0 To 5
ComboBox2.ListIndex = j
Me.PrintForm
Next j
Case 8
For j = 0 To 5
cboProjectManagerID.ListIndex = j
Me.PrintForm
Next j
Case 9
For j = 0 To 5
ComboBox3.ListIndex = j
Me.PrintForm
Next j
Case 10
For j = 0 To 5
ComboBox4.ListIndex = j
Me.PrintForm
Next j
Case 4, 7
Me.PrintForm
End Select
Next i
End Sub
pages 2,3,5,6,8,9,10 have IDs (more like sub-pages/children which share common fields/textboxes). Pgs 1,4,7 have no IDs. I was hoping to use the 1st line (which works intermitently) to access the printer setup to select specific pages, orientation, etc but still prints everything. Would appreciate your help with saving userform contents as clarified and printing specific pages of multipages using above code (I am not good with loops). Thanks
RE: Saving a Userform
Hi Lone,
I can have a look at your working code for you and develop a code which will save it in a Word document but it is worth clarifying that this sort of query takes us beyond the scope of the forum.
In these cases we look at your work and if we can identify a solution we will let you know scope, duration and associated costs.
To save the data in a Word document you will need to refer to the Word object library and use Word VBA.
If you would like to look into this further please do contact our inquiries team.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Outlining - Grouping rows or columnsHighlight want you want to group and press ALT + SHIFT + left cursor arrow |