These examples show Excels features
such as VBA, Worksheet_Change Event, Advanced Filter, Dfunctions, Choose, Vlookup, Validation, Index, Match and more! you will need WinZip to open the Workbooks on this page. If you do not have WinZip download it here
AdvancedFilter.zip
This is a zipped Excel Workbook that has six working examples using Excels Advanced Filter. It has three that use no formulas and three that do use formulas.
--------------------------------------------------------------------------------
DFunctionsWithValidation.zip
This is a zipped Excel Workbook that shows how you can use Data Validation and the Dfunctions to extract data from a Table.
--------------------------------------------------------------------------------
FormulaExamples.zip
This is a zipped Excel Workbook that shows how you can use various formulas to extract information from a Table using LookUp formulas.
--------------------------------------------------------------------------------
FormsControls.zip
Think you need to know VBA to make things happen automatically, think again. Here are some clever uses of Option Buttons and Conditional Formatting.
--------------------------------------------------------------------------------
DynamicSelections.zip
Way to spice up your spreadsheet using a 3D effect on selected cell and the Office Assistant to guide you.
--------------------------------------------------------------------------------
SheetsToPrint.zip
Use of a UserForm and ListBox to display a list of non empty sheets for printing.
--------------------------------------------------------------------------------
AutoUpdateValidationList.zip
This is a zipped Excel Workbook that shows how you can use VBA housed in the Worksheet_Change Event to automatically add data to a cells Data Validation list.
--------------------------------------------------------------------------------
SumEveryNth.zip
This is a zipped Excel Workbook that shows how you can Sum every second, third fourth or Nth cell, the right way
--------------------------------------------------------------------------------
ChangingExternal.zip
This Workbook shows how you can use the Worksheet_Calculate Event and a Validation list to have formulas automatically reference different Workbooks. The code has been set so it works in both Excel 97 and 2000. The reason it uses the Worksheet_Calculate Event and not the Worksheet_Change is because a Validation list will not fire the Worksheet_Change Event in Excel 97.
This download contains four Workbooks. They should be all saved to the same folder on your hard drive. Then open "ChangingExternalFormulas".
--------------------------------------------------------------------------------
PrintCol.zip
This workbook example shows how you can move a long column of data (10,000 rows in the example) at each page break and place them across columns ready for printing. The code is also here.
--------------------------------------------------------------------------------
DynaRange.zip
This workbook demonstrates the use of dynamic ranges taken to a new level. It will create a dynamic range within another dynamic range, range or named range. For example you can actually have a dynamic range of all people whos name starts with the letter "H" within a list of names. If you are not familiar with dynamic ranges, click here
--------------------------------------------------------------------------------
MatchingLists.zip
Here is how you can use Data Validation in 2 cells and have the List of one depend on the item chosen from the other, no code required! There is also a more advanced versions which makes use of VBA code.
--------------------------------------------------------------------------------
Lookup.zip
This workbook shows how you can combine VLOOKUP, COUNTIF, Data Validation and Dynamic named ranges to create a very user friendly lookup table.
--------------------------------------------------------------------------------
ListMatch.zip
Uses two 'Type 2' InputBoxes that allow you to select two seperate ranges. The Values from the first range are then match and transfered to the second range.
--------------------------------------------------------------------------------
ListBoxTransfer.zip
Demonstrates how you can use a multi columned ListBox and transfer the user selection(s) to another table.
--------------------------------------------------------------------------------
EnableMacros.zip
A user friendly way to ensure macros are enabled within your VBA Workbook. This method uses some simple VBA to hide all sheets if macros are disabled.
--------------------------------------------------------------------------------
PasswordBook.zip
How to add a UserForm that prompts for a user name and password when the workbook opens.
--------------------------------------------------------------------------------
LisboxSort.zip
A very quick and easy way to add a list to a UserForm ListBox that has all duplicates removed and is then sorted.
--------------------------------------------------------------------------------
AddingCustomMenus.zip
This workbook demonstrates how to create a Custom menu (with sub-menus) on Excel's Worksheet Menu Bar. The custom menu is deleted when the Workbook is Deactivated and re-created when it's Activated.
--------------------------------------------------------------------------------
AutoSelectList.zip
The "Pick from list" option you get when you right click in a cell or use Alt+Down arrow is a very handy way to add to a list. Unfortunately it will only include text, this Workbook contains a possible work-around using Validation.
--------------------------------------------------------------------------------
ChartByMonth.zip
Very simple method to have a chart range expand and contract and have it instantly reflected in the chart. No VBA needed!
--------------------------------------------------------------------------------
OverDueDates.zip
A simple but very handy use of Conditional Formatting to show all over due dates.
--------------------------------------------------------------------------------
ChangingCombo.zip
A very simple method to have a ComboBox (from Forms toolbar) change it's Input Range according to a users choice from some OptionButtons. No VBA needed!
--------------------------------------------------------------------------------
ChangingCombo2.zip
Another very simple method to have a ComboBox (from Forms toolbar) change it's Input Range according to a users choice from another ComboBox. Again no VBA needed!
--------------------------------------------------------------------------------
SwapSelection.zip
A very simple macro that swaps the content of two cells with each other. Cells can be contiguous or non-contiguous!
--------------------------------------------------------------------------------
Pivot.zip
Simple use of a Pivot Table and a Dynamic named range to show the Top x occuring numbers in a list.
--------------------------------------------------------------------------------
WhichControl.zip
Example of how to use the one Procedure to determine which Control button was clicked and run it's own procedure.
--------------------------------------------------------------------------------
LookUpTaxRate.zip
Very simple method of looking up the appropriate tax rate using VLOOKUP.
--------------------------------------------------------------------------------
DisplayWaitMessage.zip
Here is a simple method anyone can use to let the user know that a Procedure is running. Often a progress bar is used, but this slows things down even more.
--------------------------------------------------------------------------------
FormsControls.zip
Think you need to use VBA for user interaction? If so this will prove you wrong, it's a very simple use of the CheckBox control from the Forms Toolbar combined with Conditional Formatting.
--------------------------------------------------------------------------------
Solvsamp.zip
Here is the Microsoft Workbook example of Excels Solver feature.
--------------------------------------------------------------------------------
Xl8galry.zip
Excels Chart Workbook.
--------------------------------------------------------------------------------
Samples.zip
This Workbook is priceless in more ways than one. It shows Functions, special features, ActiveX controls, VBA code for Databases and API code and more!
--------------------------------------------------------------------------------
FaceID.zip
If you create Custom Command Bars, you will find this download very handy.
Posts
No comments:
Post a Comment