User Form- GUI for macro

MS Excel: Automatically open a Form when spreadsheet is opened in Excel 2003/XP/2000/97


There are several "events" available within an Excel spreadsheet where you can place VBA code. In your case, we want to open the form when the "Workbook_Open" event fires.

To do this, press Alt-F11 to go to the Visual Basic editor.

Then select ThisWorkbook in the left window. In the right window, select Workbook in the first drop-down and Open in the second drop down. Then enter the following VBA code:

Private Sub Workbook_Open()

UserForm1.Show

End Sub

Now when the spreadsheet is opened, the form "UserForm1" will automatically open.





 

Creating PivotTable Reports and Charts with VBA in Excel 2010

This chapter will focus on how to use Visual Basic for Applications (VBA) in Microsoft Excel 2010 to create and manipulate Pivot Table reports and charts. Using code to create Pivot Table reports and charts can help you to do these tasks repeatedly and more efficiently

File Handling

In Microsoft Excel, I'd like to create a "Save As" macro that I can assign to a button. This macro should let me save my Excel spreadsheet while allowing me to select the file name.

How can I do this?

Answer: The following macro will perform a "File Save As" and then display a message box stating where the file was saved to.



Macro Code

The macro code looks like this:

Sub mcrSave()

'Retrieve file name to use for Save
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")

'If user specified file name, perform Save and display msgbox
If fileSaveName <> False Then
ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlNormal

MsgBox "Save as " & fileSaveName
End If

End Sub