Macros, VBA Functions
- When I open a file, it asks if I want to "Enable or Disable a Macro"-- there are no macros
- Can my Macro make Excel NOT ask "do you want to overwrite" type of questions?
- Can I ask my user for confirmation before executing the macro?
- Is there a way to hide the process of executing macro?
- My Stop Recording toolbar has disappeared. How do I get it back?
- User Defined Functions
- Run Macros Automatically
- How do I run a macro every time a certain cell changes its value?
- I want Excel to run this macro automatically every time the Excel file is opened.
- I want to add a toolbar to my spreadsheet that when clicked, brings up Userform1.
- I want to show a userform each time my file is opened.
- Is it possible to call a macro from a worksheet formula?
- Macros and Security
- Working with Ranges
- How do I find the first empty cell in column A?
- How do I find the cell below the last entry in column A?
- How do I find the last row in my spreadsheet?
- I want to loop through all selected cells and perform an operation on each of them.
- I want to loop through all worksheets and perform an operation on each
- I want to loop through all workbooks in a folder and perform an operation on each
- Working with Files
- How can I tell if a file exists in a specific folder?
- How can I tell if a specific workbook is open?
- How can I tell if a specific worksheet exists?
- I want to let the user select a file within my macro.
- I want to let the user enter a "Save As" location in my macro.
- How do I close a file/close Excel with a macro
All of a sudden, when I open the file, it asks if I want to "Enable or Disable a Macro". There are no macros in this workbook.
A macro has been added and then removed, leaving an empty module. Empty modules trigger the macro query, as does an actual macro.
- Warning: As a precaution, you should make a backup copy of the file, before you remove any code.
- Right click on any sheet tab and choose View Code, to open the Visual Basic Editor.
- In the Project Explorer at the left of the screen, find the workbook. In the sample shown here, Book4 is the workbook name -- VBAProject (Book4)
- Look for a Modules folder, and open it. (If there is no Modules folder, go to Step 6.)
- For each module in the folder:
- Right-click on the module name.
- Choose Remove Module1 (the name of your module may be different)
- Click No when asked if you want to Export.
- Open the Microsoft Excel Objects folder.
- For each worksheet, and for ThisWorkbook:
- Double-click on the object name, to open its code module. In this sample, you'd double-click on Sheet1 (Sheet1)
- On the keyboard, press Ctrl+A to select all the code (even if the code module looks empty)
- Press the Delete key.
- Look for a Forms folder, and open it.
- Delete any UserForms that it contains.
- Look for a Class Modules folder, and open it.
- Delete any class modules that it contains.
- Close the Visual Basic Editor.
- Save the changes to the workbook.
A macro to remove all VBA code in a workbook is available
at Chip Pearson's web site:
Can I have my Macro make Excel NOT ask "the file already exists, do you want to overwrite" type of questions?Application.DisplayAlerts = False 'code to save, overwrite, delete, whatever goes here Application.DisplayAlerts = TrueSub AskAndDo() If MsgBox("Are you sure ?", vbYesNo + vbQuestion) = vbNo _ Then Exit Sub 'Code goes here instead of MsgBox "Actions here" End SubApplication.ScreenUpdating = False 'code here Application.ScreenUpdating = True
To reactivate the Stop Recording toolbar:
- Choose Tools | Macro | Record New Macro
- Click OK
- Choose View | Toolbars | Stop Recording
- Click the Stop Recording button (the blue square)
The next time you record a macro, the toolbar should automatically appear.
Note: When you're finished recording, click the Stop Recording button. If you close the toolbar by clicking the X, it will disappear again.
2. User Defined Functions
Include all the cells that your UDF depends on in the argument list. Or enter this as the first statement in your Function:Application.Volatile
This will cause the function to be executed whenever a calculation occurs in the workbook.
There are buttons from the Forms toolbar and there are buttons from the Control Toolbox. If "Assign Macro" is not an option then it's from the Control Toolbox.
Choose "View code" and call your macro from it like this:Private Sub CommandButton1_Click() Call Macro1 End Sub
4. Run Macros Automatically
There is an event called Worksheet_Change which is triggered when a value is entered (it will not fire when a formula result changes). One of the arguments to this event is 'Target' which is a reference to what changed. Since this event will occur whenever a value changes - you can use the target to see if it is the cell you are interested in:Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C5")) Is Nothing Then Exit Sub Else 'The cell you are monitoring has changed! 'Do whatever you need to do... End If End Sub
Place the code in (or call it from) the Workbook_open event of the ThisWorkbook module in the VB editor. Or simply name your macro Auto_Open. If you choose to use both then Workbook__open will run before Auto_open.
Auto_open will not run if the workbook is opened by another macro, you must use the RunAutoMacros method. Contrary; Workbook_open will run if the workbook is opened by a macro, you must use Application.EnableEvents = False to prevent it.
Assign the toolbar button to this macro, which should be in a standard VBA module:Sub ShowForm () Userform1.Show End Sub
Combine the two solutions above:Private Sub Workbook_Open() UserForm1.Show End Sub
orSub Auto_open() UserForm1.Show End Sub
See Chip Pearson's http://www.cpearson.com/excel/events.htm for detail and many more useful events.
Is it possible to call a macro from the condition true or false side of a worksheet formula? i.e. If(A2="OK",Run macro1,run macro2)
Basically, the answer is No. You can write functions in VBA that you can call from worksheet cells, but these functions can only return a value. They can't modify other cells or alter any part of the Excel environment. (You may be able to use a worksheet change event to call the macro.)
5. Macros and Security
Some of the functions that we want our macros to accomplish are not possible without first unprotecting the worksheet/workbook with the password.Worksheets("MySheet").Unprotect password:="drowssap" 'your code here Worksheets("MySheet").Protect password:="drowssap"
Be sure to protect your macro code to hide the sheet password.
Go to Tools > VBAProject properties, lock the project for viewing, and enter a password.
You cannot. A workaround is to simulate keystrokes with the SendKeys method
6. Working with Ranges
If ActiveSheet.UsedRange.Count < 2 Then MsgBox 1 Else MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row End IfMsgBox Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
(This will return 2 on an empty column A)orSub FindLastCell() Dim LastCell As Range With ActiveSheet Set LastCell = .Cells(.Rows.Count, "A").End(xlUp) If IsEmpty(LastCell) Then 'do nothing Else Set LastCell = LastCell.Offset(1, 0) End If End With MsgBox LastCell.Row End SubMsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).RowNote: You may want to reset the last cellSub DoOnSelection() Dim oCell As Range For Each oCell In Selection oCell.Font.Bold = True Next End Sub
I want to loop through all worksheets and perform an operation on each of them (unprotecting or whatever).Sub AllSheets() Dim ws As Worksheet For Each Ws In ActiveWorkbook.Worksheets MsgBox ws.Name Next End SubSub AllFolderFiles() Dim wb As Workbook Dim TheFile As String Dim MyPath As String MyPath = "C:\Temp" ChDir MyPath TheFile = Dir("*.xls") Do While TheFile <> "" Set wb = Workbooks.Open(MyPath & "\" & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End Sub
7. Working with Files
Function bFileExists(rsFullPath As String) As Boolean bFileExists = CBool(Len(Dir$(rsFullPath)) > 0) End FunctionFunction bWorkbookIsOpen(rsWbkName As String) As Boolean On Error Resume Next bWorkbookIsOpen = CBool(Len(Workbooks(rsWbkName).Name) > 0) End FunctionFunction WksExists(wksName As String) As Boolean On Error Resume Next WksExists = CBool(Len(Worksheets(wksName).Name) > 0) End FunctionYou can call this function from your code, e.g.:Msgbox WksExists("Sheet19")Sub SelectWebPageToOpen() Dim ThePage As Variant ThePage = _ Application.GetOpenFilename("Webpage (*.htm*), *.htm*", _ , "Pick one:") If ThePage = False Then MsgBox "You cancelled" Else MsgBox "Do something with file " & CStr(ThePage) End If End SubSub SelectSaveFileName() Dim TheFile As Variant TheFile = Application.GetSaveAsFilename("C:\Temp\File.xls", _ "Workbook (*.xls), *.xls", , "Your choice:") If TheFile = False Then MsgBox "You cancelled" Else MsgBox "Do something with file " & CStr(TheFile) End If End SubActiveWorkbook.Close savechanges:=False 'true ??? --will close the active workbookWorkbooks("mywkbk.xls").Close savechanges:=False 'true ??? --will close mywkbk.xlsThisWorkbook.Close savechanges:=False 'true ??? --will close the workbook that holds the code that's running.Application.Quit will close all of Excel. --Be careful with this one.
FAQs collected by Harald Staff, Excel MVP
Posted by Debra Dalgleish, Excel MVP
Excel Tips and Book List