Excel Macros - General Questions. When I open a file, it asks if I want to 'Enable or Disable a Macro'. There are no Excel macros in this workbook. An Excel macro has been added and then removed, leaving an empty module. Empty modules trigger the macro query, as does an actual macro.
Has anyone found a reason for a workaround for this problem? After running a macro from a button on a worksheet a number of times, the button vanishes. The problem occurs both in XL2004 for the Mac and XL2003 running on XP in a Dell laptop. Macros—automated actions or sets of actions in Excel—can spare you some of the tedium associated with repetitious command sequences and data manipulation tasks. In this course, learn how to create and use macros to automate tasks in Excel 2016 for Mac.
To see the steps for removing this warning, please watch this short video tutorial. The written instructions are below the video. Your browser can't show this frame. Here is a link to the page. 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 Excel macros 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 = True My macros trigger my Event macros. How can I prevent that? Application.EnableEvents = False 'code to clear, overwrite, delete, whatever goes here Application.EnableEvents = True My Excel macros clear a range and now take forever. How can I make them faster?
If you have Google Desktop Search installed, either turn it off in Excel or disable events: Application.EnableEvents = False 'code to clear a range Application.EnableEvents = True Can I ask my user for confirmation before executing Excel macros? Sub AskAndDo If MsgBox('Are you sure?' , vbYesNo + vbQuestion) = vbNo Then Exit Sub Else 'Code goes here End If End Sub Is there a way to hide the process of executing Excel macros? If you turn off ScreenUpdating, the Excel file won't show all the steps as the macro runs. It might also help the macro run faster. Application.ScreenUpdating = False 'code here Application.ScreenUpdating = True My Stop Recording toolbar has disappeared. How do I get it back?
To reactivate the Stop Recording toolbar in Excel 2003:. 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.
How do I get the Developer tab on the Excel Ribbon? In Excel 2010 and 2013:.
Right-click on the Ribbon, and click Customize the Ribbon. In the Customize the Ribbon list, add a check mark to the Developer tab. Click OK, to close the Excel Options window. In Excel 2007:. Click the Office button, then click Excel Options. Click the Popular category.
Add a check mark to Show Developer tab in the Ribbon. Click OK, to close the Excel Options window User Defined Functions I have a user defined function that doesn't recalculate. 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. Buttons to Run Excel Macros When right-clicking on a button the 'Assign Macro' command is not present. Some buttons are Form Controls and other buttons are ActiveX Controls. If you right-click a button from Form Controls, the popup menu includes the Assign Macro command.
If 'Assign Macro' is not an option, then the button is from the ActiveX Controls. You can delete that button, and make a new one from the Form Controls. Or, follow these steps to assign a macro to a button from the ActiveX Controls:. Right-click on the button, and choose 'View code'. In the command button click code, call your macro like this: Private Sub CommandButton1Click Call Macro1 End Sub Run Excel Macros Automatically. How do I run a macro every time a certain cell changes its value? There is an event called WorksheetChange 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 WorksheetChange(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 I want Excel to run a macro automatically when the Excel file is opened. Place the code in (or call it from) the Workbookopen event of the ThisWorkbook module in the VB editor. Or simply name your macro AutoOpen. If you choose to use both then Workbookopen will run before Autoopen.
Autoopen will not run if the workbook is opened by another macro, you must use the RunAutoMacros method. Contrary; Workbookopen will run if the workbook is opened by a macro, you must use Application.EnableEvents = False to prevent it. For more examples, see. I want to show a userform each time my file is opened. Use the one of the following macros.
As, the first example goes in the ThisWorkbook module. Private Sub WorkbookOpen UserForm1.Show End Sub or Sub Autoopen UserForm1.Show End Sub Is it possible to call Excel macros from the condition true or false side of a worksheet formula? 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. For cells that are manually changed, you can use a to call a macro.
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. Is there a way to protect the Excel macros I create so people can't see or alter them? In the Visual Basic Editor, go to the Tools menu, and click VBAProject properties.
On the Protection tab, click in the box to add a check mark for 'Lock the project for viewing', Enter a password, and confirm the password, then click OK. How can I unprotect a VBA project using Excel macros? A workaround is to simulate keystrokes with the SendKeys method 6. Working with Ranges in Excel Macros. How do I find the first empty cell in column A?
If ActiveSheet.UsedRange.Count ' Set wb = Workbooks.Open(MyPath & ' ' & TheFile) MsgBox wb.FullName wb.Close TheFile = Dir Loop End Sub 7. Working with Files in Excel Macros. How can I tell if a file exists in a specific folder? Function bFileExists(rsFullPath As String) As Boolean bFileExists = CBool(Len(Dir$(rsFullPath)) 0) End Function How can I tell if a specific workbook is open? Function bWorkbookIsOpen(rsWbkName As String) As Boolean On Error Resume Next bWorkbookIsOpen = CBool(Len(Workbooks(rsWbkName).Name) 0) End Function How can I tell if a specific worksheet exists? Function WksExists(wksName As String) As Boolean On Error Resume Next WksExists = CBool(Len(Worksheets(wksName).Name) 0) End Function You can call this function from your code, e.g.: Msgbox WksExists('Sheet19') I want to let the user select a file within my Excel macros. 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 Sub I want to let the user enter a 'Save As' location in my Excel macros. Sub 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 Sub How do I close a file/close Excel with a macro? ActiveWorkbook.Close savechanges:=False 'true??? -will close the active workbook Workbooks('mywkbk.xls').Close savechanges:=False 'true??? -will close mywkbk.xls ThisWorkbook.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.