Practical VBA
For version 7.0 and later
The screen updates when a macro is processing. To turn this off (False) and on (True) anywhere in the
"macro when you do not want to see the action. True turns it back on and twhen the macro ends, Excel"
will automatically turn it back to TRUE when the macro ends.
Application.ScreenUpdating=FALSE Application.ScreenUpdating=TRUE
"Name a custom tool button instead of ""Custom"" on a custom ToolBar ."
"If Tool Button is on its own ToolBar, first position"
" Toolbars(""Toolbar 13"").ToolbarButtons(1).Name = ""Name_Tool_Button"""
With the InputBox Method
"Name ""Custom"" to ""Name_Tool_Button"". Note the Toolbar and ButtonNumber before you click the "
Demo button in sheet SampMacsButtons.
WARNING: USER INFORMATION IS REQUIRED
" Toolbars(InputBox(""Toolbar 1300"", ""Toolbar Name or Number"")) _"
" .ToolbarButtons(InputBox(""Button Number 1?"", ""Position on Toolbar"")) _"
" .Name = InputBox(""Name_Tool_Button"", ""Macro Name for toolButton"")"
With the InputBox Method
Change the name of the toolbar Button
" Toolbars(""Toolbar 1300"").ToolbarButtons(1).Name =InputBox(""New Macro Name?"")"
With the InputBox Method
Delete a Toolbar of your choice. Buit-In Toolbars cannot be deleted.
" Toolbars(InputBox(""Name of Toolbar to Delete?"")).Delete"
With the InputBox Method
"Create a new Toolbar, positioned at the bottom"
" Toolbars.Add Name:=InputBox(""Name of new Toolbar to add? ALLNEW"")"
" Toolbars(InputBox("" Repeat: Name of new Toolbar to make visible? ALLNEW"")).Visible = True"
With Application
.ShowToolTips = True
.LargeButtons = False
.ColorButtons = True
End With
" With Toolbars(InputBox(""Repeat Again please: new Toolbar? ALLNEW""))"
.Position = xlBottom
.Left = 6
.Top = 1
End With
Message Box (moved across to center in box)
"MsgBox "" Hey! It Worked!"""
Scroll down one page Scroll up one page
ActiveWindow.LargeScroll Down:=1 ActiveWindow.LargeScroll Up:=-1
Select two different rows eg: Row 2 and Row 4 at the same time
" Cells.Range(""2:2,4:4"").select"
With the InputBox Method
" Cells.Range(InputBox(""select the cells"")).Select"
The results for the Copy and Paste are that the Rows will align one after the other eg Row 20 and Row 40
"are selected. Copy, and select cell A1. Row 20 is copied to Row 1 and Row 40 to Row 2. Only Column"
A will accept the paste.
The Cells.Range Method will also delete selection.
Select two different cells eg: A1 and D2 at the same time
" Cells.Range(""A1:A1,D2:D2"").select"
With the InputBox Method
" Cells.Range(InputBox(""select the cells"")).Select"
"Type in A1:A1, D2:D2 Or use the mouse to make selections. Click on cell to select, type a"
"semicolon : then a comma , and make next selection following same criteria. The = sign must be "
"deleted before you exit the InputBox. To deselect error input, delete directly from the InputBox "
before you exit.
Select the cells that surround the ActiveCell
" Range(""B2"").CurrentRegion.Select"
"Intersecting of different Row and Column ie Row 3 and Column D, ActiveCell is in Column A Row 3"
" Range(""3:3, D:D"").select"
"Intersecting of different Column and Row ie Column D and Row 3, ActiveCell is in Column D Row 1"
" Range(""D:D, 3:3"").select"
Select two different Columns eg: Column C and Column E at the same time
" Cells.Range(""C:C, E:E"").select"
"Select the cells in a range, either text or values. Select by Range Method or use your mouse to select"
" Range(""A21:E26"").Select"
Selection.ColumnDifferences(ActiveCell).Select
Run a macro in the current workbook Run a macro from a different workbook
" Application.Run ""RowByRow""" " Application.Run Macro:=""'Prac VBA.xls'!RowByRow"""
"Select the last cell in a range as the ActiveCell. Normally, A4 is the Activecell, now D6 is the ActiveCell"
" Range(""A4:D6"").Select"
intRowOffset = Selection.Rows.Count
intColOffset = Selection.Columns.Count
" Selection.Cells(intRowOffset, intColOffset).Activate"
The Offset Method starts at current position whereas the Cells Method starts at A1
" Cells(2, 1) = 99" A2 is 99 and
" Cells(3, 1) = ""Maxwell Smart""" A3 is Maxwell Smart
"Enter a Value or Text by User, with the InputBox Method"
" Range(""D4"").Value=InputBox(""Please enter a Value or Text"")"
"Select a Current Region of a User ActiveCell, Region exclusive to current position to last cell"
" Range(ActiveCell.Offset(0, 0), ActiveCell.SpecialCells(xlLastCell)).Select"
Select a Row where the ActiveCell is Delete a Row where the ActiveCell is
Selection.EntireRow.Select Selection.EntireRow.Delete
Select a Column where the activeCell is Delete a Column where the activeCell is
Selection.EntireColumn.Select Selection.EntireColumn.Delete
Move one Row up Move one Row down
" ActiveCell.Offset(-1,0).select" " ActiveCell.Offset(1,0).select"
Move one Column to the right Move one Column to the left Select next Cell to the right
" ActiveCell.Offset(0,1).select" " ActiveCell.Offset(0,-1).select" Selection.Next.Select
"Select a Range using the Offset Method:If ActiveCell is at B5, then the range selected (A1:E5) ends"
"up as (B5:F9). Range (A1:E5) is only a reference to the current position, not the real Range reference"
" ActiveCell.Offset(0,0).Range(""A1:E5"").select"
Copy and paste on the same sheet
" Range(""A1"").Copy(Range(""B1""))"
"Cut a cell, ready for Paste Method " Paste Method
" Selection.Cut Destination:=Range(""A33"")" ActiveSheet.Paste
De-activate the Cut Method
Application.CutCopyMode=False
Show a dialog box
" DialogSheets(""dialog1"").Show"
"Disable the Button Price4 on the DialogSheet. It ""greys"" the Button Name"
"DialogSheets(""Dialog1"").Buttons(""Price4"").Enabled = False"
Ensure the Cancel Button on the DialogBox Exits the Sub
" DialogOk = DialogSheets(""dialog1"").Show"
If Not DialogOk Then Exit Sub
Save ActiveWorkBook Close the ActiveWorkBook Quit the program
ActiveWorkBook.Save ActiveWorkBook.Close Application.Quit
Open a WorkBook in the current Directory Open a WorkBook from a different Directory
WorkBooks.Open MyFile.xls " WorkBooks.Open FILENAME:=""C:\Path\MyFile.xls"""
Open a WorkBook that is already open but is not the active WorkBook
" Windows(""My File.xls"").Activate"
Hide the formula in a cell Unhide the formula in a cell
Selection.FormulaHidden=True Selection.FormulaHidden=False
Lock the cell Unlock the cell
Selection.Locked=True Selection.Locked=False
Protect the sheet
" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True"
Unprotect the sheet Choose the option of Protect/Unprotect
ActiveSheet.Unprotect Application.Dialogs(xlDialogProtectDocument).Show
"Select the last position used, the last cell on the sheet"
ActiveCell.SpecialCells(xlLastCell).Select
Go to PrintPreview.
ActiveSheet.PrintPreview Application.Dialogs(xlDialogPrintPreview).Show
Add a WorkBook Add a WorkSheet
WorkBooks.Add Sheets.Add
Delete the current sheet Delete a sheet that is not active
ActiveWindow.SelectedSheets.Delete "Sheets(""Sheet1"").Delete"
"Set the Background, behind the GridLines, to a picture of your choice (It will not print)"
ActiveSheet.SetBackgroundPicture _
" ""C:\MSOffice\Clipart\Corel4 Clipart\TIFS\DRAGNFL1.TIF"""
Reset the Background to Blank
" ActiveSheet.SetBackgroundPicture """""
Close active workbook
ActiveWorkbook.RunAutoMacros Which:=xlAutoClose ActiveWorkBook.Close
Zoom worksheet to user size Show the Zoom DialogBox to make a choice of settings
ActiveWindow.Zoom=100 Application.Dialogs(xlDialogZoom).Show
"To open builtin dialog boxes, use the sendkeys method with each menu shortcut letter. The % activates"
"the menu. eg: Insert Name Define dialog box is SendKeys ""%IND"" . This method is not foolproof"
as the SendKeys Method only acts as the last command regardless of where it is inserted in the macro.
The proper VB is the Application Method. These Applications Methods are Stand Alone Methods
"To activate a sublevel, a second SendKeys ""% "" may be required"
"eg: to activate the PasteSpecial Box,"
" SendKeys ""%EC"" " Activates Copy Mode
" SendKeys ""%ES""" Activates SpecialPaste Dialog 1st 2nd
" ""% """ " ""% """
Application.Dialogs(xlDialogActivate).Show W
Application.Dialogs(xlDialogActiveCellFont).Show oe
Application.Dialogs(xlDialogAddinManager).Show TI
Application.Dialogs(xlDialogAlignment).Show oeA
Application.Dialogs(xlDialogApplyNames).Show INA
Application.Dialogs(xlDialogApplyStyle).Show oS
Application.Dialogs(xlDialogArrangeAll).Show WA
Application.Dialogs(xlDialogAttachToolbars).Show unknown
Application.Dialogs(xlDialogAutoCorrect).Show TA
Application.Dialogs(xlDialogBorder).Show OEB
Application.Dialogs(xlDialogCalculation).Show TOC3
Application.Dialogs(xlDialogCellProtection).Show TPP
Application.Dialogs(xlDialogClear).Show EA
Application.Dialogs(xlDialogColorPalette).Show TOC6
Application.Dialogs(xlDialogColumnWidth).Show OCW
Application.Dialogs(xlDialogConsolidate).Show DN
Application.Dialogs(xlDialogCreateNames).Show INC
Application.Dialogs(xlDialogCustomizeToolbar).Show VTC
Application.Dialogs(xlDialogDefineName).Show IND
Application.Dialogs(xlDialogDefineStyle).Show OS
Application.Dialogs(xlDialogDeleteFormat).Show EAF
Application.Dialogs(xlDialogDeleteName).Show IND
Application.Dialogs(xlDialogDemote).Show DGG
Application.Dialogs(xlDialogDisplay).Show TOV
Application.Dialogs(xlDialogEditDelete).Show ED
Application.Dialogs(xlDialogFileDelete).Show unknown
Application.Dialogs(xlDialogFileSharing).Show FH
Application.Dialogs(xlDialogFilterAdvanced).Show DFA
Application.Dialogs(xlDialogFindFile).Show FO
Application.Dialogs(xlDialogFont).Show OEF
Application.Dialogs(xlDialogFontProperties).Show OEF
Application.Dialogs(xlDialogFormatFont).Show OEF
Application.Dialogs(xlDialogFormatNumber).Show OEN
Application.Dialogs(xlDialogFormulaFind).Show EF
Application.Dialogs(xlDialogFormulaGoto).Show EG
Application.Dialogs(xlDialogFormulaReplace).Show EE
Application.Dialogs(xlDialogFunctionWizard).Show IF
Application.Dialogs(xlDialogGoalSeek).Show TG
Application.Dialogs(xlDialogInsert).Show IE
Application.Dialogs(xlDialogInsertObject).Show IO
Application.Dialogs(xlDialogInsertPicture).Show IP
Application.Dialogs(xlDialogMenuEditor).Show Unknown
Application.Dialogs(xlDialogNew).Show FN
Application.Dialogs(xlDialogNote).Show IT
Application.Dialogs(xlDialogOpen).Show FO
Application.Dialogs(xlDialogOptionsCalculation).Show TOC3
Application.Dialogs(xlDialogOptionsEdit).Show TOE
Application.Dialogs(xlDialogOptionsGeneral).Show TOG
Application.Dialogs(xlDialogOptionsListsAdd).Show TOC
Application.Dialogs(xlDialogOptionsTransition).Show TOT
Application.Dialogs(xlDialogOptionsView).Show TOV
Application.Dialogs(xlDialogOutline).Show DGE
Application.Dialogs(xlDialogPageSetup).Show FU
Application.Dialogs(xlDialogParse).Show Unknown
Application.Dialogs(xlDialogPasteSpecial).Show EC ES
Application.Dialogs(xlDialogPatterns).Show OEP
Application.Dialogs(xlDialogPivotTableWizard).Show DP
Application.Dialogs(xlDialogPrint).Show FP
Application.Dialogs(xlDialogPrinterSetup).Show FP
Application.Dialogs(xlDialogPrintPreview).Show FV
Application.Dialogs(xlDialogProperties).Show FI
Application.Dialogs(xlDialogReplaceFont).Show EE
Application.Dialogs(xlDialogRowHeight).Show ORE
Application.Dialogs(xlDialogRun).Show Unknown
Application.Dialogs(xlDialogSaveAs).Show FA
Application.Dialogs(xlDialogSaveWorkbook).Show FS
Application.Dialogs(xlDialogSaveWorkspace).Show FW
Application.Dialogs(xlDialogScenarioAdd).Show TCA
Application.Dialogs(xlDialogScenarioCells).Show TC
Application.Dialogs(xlDialogScenarioMerge).Show TCM
Application.Dialogs(xlDialogSelectSpecial).Show EG S
Application.Dialogs(xlDialogSetPrintTitles).Show FVS
Application.Dialogs(xlDialogShowToolbar).Show VT
Application.Dialogs(xlDialogSort).Show DS
Application.Dialogs(xlDialogSortSpecial).Show DS O
Application.Dialogs(xlDialogSplit).Show WS
Application.Dialogs(xlDialogStandardFont).Show OEF
Application.Dialogs(xlDialogStandardWidth).Show OCW
Application.Dialogs(xlDialogStyle).Show OS
Application.Dialogs(xlDialogSummaryInfo).Show FIS
Application.Dialogs(xlDialogTable).Show DT
Application.Dialogs(xlDialogUnhide).Show WU
Application.Dialogs(xlDialogVbaMakeAddin).Show Unknown
Application.Dialogs(xlDialogWorkbookAdd).Show FN
Application.Dialogs(xlDialogWorkbookCopy).Show EM
Application.Dialogs(xlDialogWorkbookInsert).Show IW
Application.Dialogs(xlDialogWorkbookMove).Show EM
Application.Dialogs(xlDialogWorkbookName).Show Unknown
Application.Dialogs(xlDialogWorkbookNew).Show Unknown
Application.Dialogs(xlDialogWorkbookOptions).Show TO
Application.Dialogs(xlDialogWorkbookProtect).Show TPW
Application.Dialogs(xlDialogWorkbookTabSplit).Show Unknown
Application.Dialogs(xlDialogWorkbookUnhide).Show Unknown
Application.Dialogs(xlDialogWorkgroup).Show Unknown
Application.Dialogs(xlDialogWorkspace).Show Unknown
Application.Dialogs(xlDialogZoom).Show VZ
The following Application Dialogs either need supporting VB or are just the Methods used by Excel to
display the next step of the Application
Application.Dialogs(xlDialogAddChartAutoformat).Show
Application.Dialogs(xlDialogAppMove).Show
Application.Dialogs(xlDialogAppSize).Show
Application.Dialogs(xlDialogAssignToObject).Show
Application.Dialogs(xlDialogAssignToTool).Show
Application.Dialogs(xlDialogAttachText).Show
Application.Dialogs(xlDialogAxes).Show
Application.Dialogs(xlDialogChangeLink).Show
Application.Dialogs(xlDialogChartAddData).Show
Application.Dialogs(xlDialogChartTrend).Show
Application.Dialogs(xlDialogChartWizard).Show
Application.Dialogs(xlDialogCheckboxProperties).Show
Application.Dialogs(xlDialogCombination).Show
Application.Dialogs(xlDialogCopyChart).Show
Application.Dialogs(xlDialogCreatePublisher).Show
Application.Dialogs(xlDialogDataDelete).Show
Application.Dialogs(xlDialogDataLabel).Show
Application.Dialogs(xlDialogDataSeries).Show
Application.Dialogs(xlDialogEditboxProperties).Show
Application.Dialogs(xlDialogEditColor).Show
Application.Dialogs(xlDialogEditionOptions).Show
Application.Dialogs(xlDialogEditSeries).Show
Application.Dialogs(xlDialogErrorbarX).Show
Application.Dialogs(xlDialogErrorbarY).Show
Application.Dialogs(xlDialogExtract).Show
Application.Dialogs(xlDialogFillGroup).Show
Application.Dialogs(xlDialogFillWorkgroup).Show
Application.Dialogs(xlDialogFormatAuto).Show
Application.Dialogs(xlDialogFormatChart).Show
Application.Dialogs(xlDialogFormatCharttype).Show
Application.Dialogs(xlDialogFormatLegend).Show
Application.Dialogs(xlDialogFormatMain).Show
Application.Dialogs(xlDialogFormatMove).Show
Application.Dialogs(xlDialogFormatOverlay).Show
Application.Dialogs(xlDialogFormatSize).Show
Application.Dialogs(xlDialogFormatText).Show
Application.Dialogs(xlDialogGallery3dArea).Show
Application.Dialogs(xlDialogGallery3dBar).Show
Application.Dialogs(xlDialogGallery3dColumn).Show
Application.Dialogs(xlDialogGallery3dLine).Show
Application.Dialogs(xlDialogGallery3dPie).Show
Application.Dialogs(xlDialogGallery3dSurface).Show
Application.Dialogs(xlDialogGalleryArea).Show
Application.Dialogs(xlDialogGalleryBar).Show
Application.Dialogs(xlDialogGalleryColumn).Show
Application.Dialogs(xlDialogGalleryCustom).Show
Application.Dialogs(xlDialogGalleryDoughnut).Show
Application.Dialogs(xlDialogGalleryLine).Show
Application.Dialogs(xlDialogGalleryPie).Show
Application.Dialogs(xlDialogGalleryRadar).Show
Application.Dialogs(xlDialogGalleryScatter).Show
Application.Dialogs(xlDialogGridlines).Show
Application.Dialogs(xlDialogInsertTitle).Show
Application.Dialogs(xlDialogLabelProperties).Show
Application.Dialogs(xlDialogListboxProperties).Show
Application.Dialogs(xlDialogMacroOptions).Show
Application.Dialogs(xlDialogMailEditMailer).Show
Application.Dialogs(xlDialogMailLogon).Show
Application.Dialogs(xlDialogMailNextLetter).Show
Application.Dialogs(xlDialogMainChart).Show
Application.Dialogs(xlDialogMainChartType).Show
Application.Dialogs(xlDialogMove).Show
Application.Dialogs(xlDialogObjectProperties).Show
Application.Dialogs(xlDialogObjectProtection).Show
Application.Dialogs(xlDialogOpenLinks).Show
Application.Dialogs(xlDialogOpenMail).Show
Application.Dialogs(xlDialogOpenText).Show
Application.Dialogs(xlDialogOptionsChart).Show
Application.Dialogs(xlDialogOverlay).Show
Application.Dialogs(xlDialogOverlayChartType).Show
Application.Dialogs(xlDialogPivotFieldGroup).Show
Application.Dialogs(xlDialogPivotFieldProperties).Show
Application.Dialogs(xlDialogPivotFieldUngroup).Show
Application.Dialogs(xlDialogPivotShowPages).Show
Application.Dialogs(xlDialogPlacement).Show
Application.Dialogs(xlDialogPromote).Show
Application.Dialogs(xlDialogPushbuttonProperties).Show
Application.Dialogs(xlDialogRoutingSlip).Show
Application.Dialogs(xlDialogSaveCopyAs).Show
Application.Dialogs(xlDialogSaveNewObject).Show
Application.Dialogs(xlDialogScale).Show
Application.Dialogs(xlDialogScenarioEdit).Show
Application.Dialogs(xlDialogScenarioSummary).Show
Application.Dialogs(xlDialogScrollbarProperties).Show
Application.Dialogs(xlDialogSendMail).Show
Application.Dialogs(xlDialogSeriesAxes).Show
Application.Dialogs(xlDialogSeriesOrder).Show
Application.Dialogs(xlDialogSeriesX).Show
Application.Dialogs(xlDialogSeriesY).Show
Application.Dialogs(xlDialogsetcontrolvalue).Show
Application.Dialogs(xlDialogSetUpdateStatus).Show
Application.Dialogs(xlDialogsheet).Show
Application.Dialogs(xlDialogsheetbackground).Show
Application.Dialogs(xlDialogShowDetail).Show
Application.Dialogs(xlDialogSize).Show
Application.Dialogs(xlDialogSubscribeTo).Show
Application.Dialogs(xlDialogSubtotalCreate).Show
Application.Dialogs(xlDialogTabOrder).Show
Application.Dialogs(xlDialogTextToColumns).Show
Application.Dialogs(xlDialogUpdateLink).Show
Application.Dialogs(xlDialogVbaInsertFile).Show
Application.Dialogs(xlDialogVbaProcedureDefinition).Show
Application.Dialogs(xlDialogView3d).Show
Application.Dialogs(xlDialogWindowMove).Show
Application.Dialogs(xlDialogWindowSize).Show
Print settings Print one Copy
Selection.PrintOut.PrintArea Copies:=1
With ActiveSheet.PageSetup
" .PrintTitleRows = """""
" .PrintTitleColumns = """""
End With
" ActiveSheet.PageSetup.PrintArea = """""
With ActiveSheet.PageSetup
" .LeftHeader = ""Practical VBA"""
" .CenterHeader = """""
" .RightHeader = """""
" .LeftFooter = """""
" .CenterFooter = """""
" .RightFooter = ""Page &P"""
.LeftMargin = Application.InchesToPoints(0.56)
.RightMargin = Application.InchesToPoints(0.6)
.TopMargin = Application.InchesToPoints(0.79)
.BottomMargin = Application.InchesToPoints(0.82)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False .Draft = False
.PrintNotes = False .PaperSize = xlPaperA4
.PrintQuality = 300 .FirstPageNumber = xlAutomatic
.CenterHorizontally = False .Order = xlDownThenOver
.CenterVertically = False .BlackAndWhite = False
.Orientation = xlLandscape .Zoom = 100
Delete cells and shift column up or down
Selection.Delete Shift:=xlUp Selection.Delete Shift:=xlDown
Selection.Font.Bold = True
"With ActiveCell.Characters(Start:=1, Length:=50).Font"
" .Name = ""Arial"""
" .FontStyle = ""Regular"""
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlNone
.ColorIndex = xlAutomatic
End With
"Change your Active Window settings to increase page space, or to hide certain settings from User"
With ActiveWindow With Application
.DisplayGridLines = True .DisplayFormulaBar = False
.DisplayHeadings = False .DisplayStatusBar = False
.DisplayHorizontalScrollBar = False .DisplayFullScreen = True
.DisplayVerticalScrollBar = False .ShowToolTips = True
.DisplayWorkbookTabs = False .LargeButtons = False
End With .ColorButtons = True
End With