Saving a Userform

Resolved · Urgent Priority · Version 2013

Wed 19 Nov 2014, 15:48 Delegate Lone said...

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

Wed 19 Nov 2014, 16:33 Trainer Jens said...

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

Wed 19 Nov 2014, 20:09 Delegate Lone said...

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

Wed 19 Nov 2014, 20:38 Trainer Jens said...

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

Fri 21 Nov 2014, 14:33 Delegate Lone said...

RE: Saving a Userform

Hi Jens,

Thanks for your response.
Unfortunately, I do not have additional funds to cover this at the present time, other than to use the forum and any free online resources.

Kindest regards,

Training courses