Back to excel page
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
Name a range or cell
ThisWorkbook.Names.Add Name:="lentchr", RefersToR1C1:=Selection
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 oeF
Application.Dialogs(xlDialogAddinManager).Show TI
Application.Dialogs(xlDialogAlignment).Show oeA
Application.Dialogs(xlDialogApplyStyle).Show oS
Application.Dialogs(xlDialogArrangeAll).Show WA
Application.Dialogs(xlDialogAttachToolbars).Show TT From module
Application.Dialogs(xlDialogAutoCorrect).Show TA
Application.Dialogs(xlDialogBorder).Show OEB
Application.Dialogs(xlDialogCalculation).Show TOVC
Application.Dialogs(xlDialogCellProtection).Show oePP
Application.Dialogs(xlDialogClear).Show EA
Application.Dialogs(xlDialogColorPalette).Show TOCCCC
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 oeN
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(xlDialogApplyNames).Show INA
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
http://www.mredkj.com/tutorials/tips_ie.html