 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
| |
Back to Excel Macros Page 1
Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 InstantBuy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!
Use Excel Formulas In Excel VBA Excel VBA Evaluate Method & WorksheetFunction Property. Excel Formulas/Functions In Macro Code
Count of Items in List VBA With the aid of a Pivot Table and Excel VBA we can get a count of each item that appears in a list.
Refresh Pivot Table via Excel Macros Those of you that use Pivot Tableswill be well aware of their power. However, one draw-back can be that you can only refresh the PivotTables automatically by setting it refresh on open via the PivotTable Options. Lets look at some ways (Excel Macros) we can refresh all, or chosen Pivot Tables.
Hide/Show Pivot Table Field ItemsLets look at how we can use Excel VBA to show or hide Pivot Table Field Items. The Excel macro here should give you a good idea how this can be done.
Hide Pivot Table Field Items by Criteria Same as above but you can specify criteria.
Return Excel Worksheet Name to a Cell In Excel it is possible to use the CELL function/formula and the MID, LEN and FIND to return the name of an Excel Worksheet in a Workbook. We can also use a handy Custom Excel VBA Function.
Printing PivotTables & PivotCharts. Print Each Page Field (Report Filter) Of Pivot Tables & Pivot Charts
Removing Duplicates From Your Spreadsheet Ways to remove duplicated data from you Worksheet, quickly.
Controls from Forms Toolbar vs Controls from the Control Toolbox Toolbar Some useful information on these 2 types of Controls.
Create Custom Excel Menu Items It is possible to customize Excel in many different ways with Excel VBA. However, to make your Excel spreadsheets have that professional look and feel, one can add their own custom menus to an existing Excel toolbar.
Adding a Command Button to the Excel Right Click Menu Excel allows us to modify nearly every part of Excel. One of these parts is the standard Excel CommandBars, or Toolbars. Sometimes you may have a macro that you wish to enable users to run by right clicking in any cell and choosing your macro from the Cell shortcut pop-up menu.
Disable Cut Copy In Excel While we can stop users cutting/copying cells via Worksheets and/or Workbook protection this is often at the expense of losing other Excel features that you may still want Excel users to access. The Excel macro code here can be used to stop users being able to Cut or Copy any cells in the Workbook.
Quick Access to Excel Custom ListsExcel Custom Lists are a great way to quickly get a list of numbers or text onto a Worksheet. These are done via theExcel Fill Handle . Excel has built in Lists for Weekdays (Mon-Fri), Months (Jan-Dec) and numeric sequences. We are also able to add our own Custom Lists via Tools>Options - Custom Lists. However, once you have added a few of your own Custom Lists, it can be hard to remember the first item in the list that must be entered in a cell.
Find Number Between 2 Numbers Excel and most MS Office Applications, have a Find feature than can be use to find a specified value, or text string, in a range, Worksheet, or Workbook. However, no such feature exists where we can tell Excel to find the first occurrence of a number that is between a Minimum number and a maximum number.
Color Cell Formula Precedents Here is some VBA code that will color the precedent cells of any single cell formula.
Find Feature to Find 3 Matching Criteria The standard Excel Find feature is great for locating matching cells. However, it cannot be use 'as is' to locate say 3 matching cells on the same row within a table.
Convert Text to Upper/Proper CaseExcel has 2 built in functions for converting text to either UPPER CASE or Proper Case. However, there are many instances when using the Worksheet Function approach is not practical.
Automatically Force Text to Upper/Proper Case We can use Excel VBA code in the Private Module of the Worksheet Object to force any text entered to be UPPER case, or Proper case. It also shows code needed for a TextBox Control.
Stop Excel VBA Code Being Case Sensitive By default, Excel VBA code is case sensitive and uses Binary comparisons. This means that "Cat" and "cat" are not seen as being the same. There are many times however, where you like Excel VBA to not use Binary comparisons and have "Cat" = "cat". This can be done in at least 2 ways.
Hide/Restore Excel Toolbars One of the most exciting parts of Excel is perhaps its ability to be customized to show the end user something quite different. For example, many build their own custom toolbar and distribute them with their Spreadsheet. However, the one question that come up time and time again is 'how can I hide all of Excel's toolbars and show only mine when they are using my spreadsheet?'
Convert Excel Formula References The Excel macro code here can be used to convert all Excel formulas from absolute to relative and/or relative to absolute. It can also give a mix of relative row, absolute column reference, or absolute row, relative column reference. Simply select the cells that should be changed, run the code and choose the reference type you want.
Track/Report Excel ChangesAs some Excel users are aware, Excel has a feature called Track Changes , found under Tools on the Worksheet Menu Bar. However, when this is chosen you are forced to share the Workbook. With this feature enabled, there are many standard Excel features that are no longer available.
Saving an Excel Workbook as Cell Text It's quite common for Excel users to want to save their Excel file as a name that resides in Worksheet cell.
Excel Custom Functions - User Defined Functions There is some important information here on Excels custom functions, including what they can and cannot do. There are also some UDF's that you can use to sum by colour, sort by colour, extract numbers and much more.
Add Category & Description to a Custom Function - When a Custom Function (UDF/User Defined Function) is written it is, by default, added to the User Defined category of the Insert Function dialog. With the use of the MacroOptions Method we can add any Custom Function to any category, or add it to a custom category that we create. We can also use MacroOptions Method to write a brief description about our Custom Function.
Filter ListBox DataIf you have used Excel UserForms before you may have also used the ListBox Control to return a table of data to the user. Sometimes this data is too much information for the user and it needs to be filtered down to meet a specified criteria.
Prevent Save AsThe code here can be used to stop any users saving a Workbook as another name and/or path.
Prevent Save As another NameThe code here can be used to stop any users saving a Workbook as another name,but will allow the Workbook to be save to another location.
Stop/Prevent Save Prompts in Excel There are times when you may wish to prevent any "Save" prompts when closing an Excel Workbook manually, or via Excel VBA code. This can be achieved in a number of ways depending on your desired outcome.
Stop Do You Want To Save Changes Some users will get the "Do You Want To Save Changes Made to..." when closing an Excel Workbook that has had no changes, or even a new Workbook without changes.
Prevent UserForm Closing to Top Right X The code here can be placed in the Private Module of any UserForm Object to prevent any users closing the Excel UserForm via the top right X.
Minimize/Maximize Button to a UserForm Here is a nice simple method you can use for a Minimize/Maximize Button on a UserForm.
Delete Excel Named RangesBy using either of the 2 Excel macros on this page you can easily delete named ranges.
Collect User Data/Input via an InputBoxThere are many times in Excel VBA that we are required to gather information from a user. This page shows you how.
Create a Hyperlinked List of Excel Workbooks With the use of Excel VBA Macro code we can create a list of Hyperlinked Excel Workbook names on any Excel Worksheet.
Loop Through a Folder of Excel Workbooks With the use of Excel VBA Macro code we can loop through a Folder on ones Hard-drive and work on all Excel Workbooks within it.
Loop Through Worksheets The code hereLoops through all Worksheets in the active Workbook and runs code on them selectively.
Testing For ...... Four Excel macros that will check to see if....
A Workbook is already open or not.
A Workbook exists in a file and folder.
A Worksheet exists in the active Workbook.
A named range exists in the active Workbook.
A range is hidden by Excels Auto Filters.
Excel Ranges Examples of how to work with ranges using Excel VBA. Going to the last used cell, last cell in a list and lots more.
Excluding Headings It's often that one needs to work on a range of data, or table, but NOT include any headings/headers. This can be done very easily with the ListHeaderRows Property of an Range Object.
Excel Dialog BoxesExamples of how to use Excels built in dialog boxes such as GetOpenFileName and GetSaveAsFileName.
Excel VBA ErrorsExamples on how to prevent expected and unexpected Run time errors while working in VBA.
Excel VBA EventsShows how you can use Excels Workbook Events to hide all toolbars and show only a custom toolbar. Then restore all toolbars back to how the user had them. There is code to get past Conditional Formatting 3 criteria limit. How to create a UserForm splash screen and much more.
Update Links in Excel When you have formula links in an Excel Workbook (destination) to another Excel Workbook (source) you will be asked if you wish to update links in the Workbook (destination) each time you open it. This can be painful when you always want, or don't want, links updated.
Excel VBA Intersect MethodThe Intersect Method will return a Range Object that represents the intersection of two, or more, ranges. It can be used to determine if a specified Range Object intersects another specified range(s). Or, in layman's terms, does a specified range intersect another range(s).
Extract From Workbook Method that can be used to extract data from a closed Excel Workbook. This method is so obvious it is often overlooked.
Speed Up CodeExamples of how you can speed up your Excel VBA code. Some are just good habits you should try and form.
Speed up Excel VBA Macro CodeIf you have Excel VBA macro code that runs slow, the chances are it's caused by Excel having to recalculate at each line of code.
Protect and Unprotect All Worksheets Step-by-step instructions on creating your own feature to Protect and Unprotect all Worksheets in one go.
Add Worksheets Adding worksheets to Excel is very simple. For example, to add a Worksheet after the active sheet (default unless stated otherwise), name it "MySheet" and have it become the active sheet, you would use some code like shown here.
Add Worksheets in Month Order Excel Macro code that will add Worksheets in calendar month order
Add Worksheets in Numeric Order Excel Macro code that will add Worksheets in numeric order
VBE (Visual Basic Editor) Tips Examples on getting about the VBE and how to insert symbols in your message boxes, make that UserForm toolbox into a template and more.
Excel VBA all sorts Many examples on Excel VBA.
VBA Loops Using loops in Excel should really be used only when nothing else will do the job. Why? Because they are horribly slow!
Validating UserForm TextBox to Only Accept Numbers If you are fairly comfortable with Excel VBA you will most likely want to design a UserForm to ensure correct use of your Spreadsheet. However, just placing some TextBoxes on a UserForm for users to enter data is not enough. For example, the TextBoxes may be intended to take only numeric data and not Text strings.
Validating UserForm TextBox to Only Accept Text
Matching ComboBox Controls We can use Excel VBA to have one ComboBox Control dependent on the item chosen in another.
Multi Select ListBoxWhen using a ListBox we can do so in a way that can allow users to make multiple selections from the ListBox. After they have made their selection(s) normally a CommandButton would be used to take those selections and place them onto a Worksheet or another UserForm Control.
Code for Excel UserForms and their Controls . Lots of examples of how to use Excel UserForms and their Controls such as a ListBox, TextBox, ComboBox and more!.
Workbook Downloads Here you will find some free workbook downloads that demonstrate Excel formulas, features, functions and code.
Create Worksheets for Each Item in an Excel Table of Data I'm often asked how one could create x number of Worksheets where each one houses the data specific to each item in a table. The task at hand is to create 1 Worksheet for each item (named as the item) and have all associated data on it.
SpecialCells Method One of the most beneficial Methods in Excel (in my experience) is the SpecialCells Method. When used, it returns a Range Object that represents only those type of cells we specify.
Excel VBA & AutoFilter AutoFilter provides us with a MUCH faster alternative to loops of all kinds
Excel VBA AutoFilter Criteria How to add the criteria for AutoFilter in VBA.
Excel VBA AutoFilter DatesUsing dates in AutoFilter can be tricky if not using the US date format.
Worksheet Names From Cells In Excel Formulas How to use Worksheet Names and Range References in Excel Formula. Code that automatically adds/deletes the worksheet name to a list as Worksheets are added/deleted.
Sort Alphanumeric Text Excel has a problem trying tosort alphanumeric cells in cells by the number portion only. The reason is simply because Excels Sort features evaluates each cell value by reading left to right. However, we can over-come this in a few ways with the aid of Excel Macros.
Stop Formula Viewing With Sheet Protection The normal method to stop formula viewing is to Hide Formulas via Format>Cells - Protection and check Hidden. Then apply Worksheet Protection. The draw back with the standard method is that the entire Worksheet is protected and hence stopping many other features from being used when not even in a formula cell.
Back to Excel Macros Page 1
See Also: Excel Duplication Manager Add-in |Excel Number Manager Add-in | Excel Text Manager Add-in |Excel Named Range Add-in Manager |Excel OzGrid Plus Add-in |Excel Time Sheet | Excel Time Wage and Pay book
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates