Make your own free website on Tripod.com
Rub the Buttons to Adjust the Background Color
0 to 255
  
Rub the Buttons to Adjust the Text Color
0 to 255
  
Microsoft Excel is a versatile, easy and yet, quite complex at times, program to use. The ease of use can be simplified with the use of macros and formulas. Repetitive tasks and data manipulating can be time consuming. At certain times such tasks are required.
Below are some Userform Utilities and macros that are some of my handiwork. The utilities are for demonstrative purposes only.
My macros work by using a logistical and analytical methodology. I am not sponsored nor have any relation to Microsoft or its subsiduaries apart from using their products.
Disclaimer: There are no guarantees nor warranties. Not all computers are the same and there are different versions of Excel and Windows to contend with. The utilities are for demonstrative purposes only.

Cell Formatting  Colors  Functions  Games Protection   OnSheet Changes and Macros  Userform Utilities Web Page Create 

Cell Formatting

Cell_Viewer.xls Created 27 January 2006 XP
This utility shows the activecell address and formula. Use the Arrow Buttons to move around the worksheet. Good for viewing long formulas.

CellCustomFormat.xls  Excel 2000
This is a list of custom cell formats that will hide cells, make fractions of whole numbers or decimals, negatives to positives, hide values but not text and more ...

RowCol.xls  10 Nov 2004 Excel 2000
This macro wil select and color highlight - interior cell - the column and row of the cell you select either by a click or arrow key. A predefined Table area with data is the selection area. nothing happens outside of this area.


Colors

RandomRGB.xls Excel 2000
This utility generates random RGB Colors into a label on the Form. The RGB color value is shown in a textbox as RGB (RED value,GREEN value, BLUE value). 09-Aug- 2004

CellColorsRGB.xls Excel 2000
This utility has scroll bars for each RGB color: Red, Green, Blue. Tweak a color to your liking then you can change the Font color or Cell color to the chosen color. You can still change the font size etc without quitting the utility. 09-Aug-2004


Functions
Some functions may require the Analysis ToolPak and/or Analysis ToolPak - VBA. These can be found in Tools > Add Ins..

ConvertMeasures.xls Excel 2000
A chart showing the measures and the conversions.

OnSheetFunctions.xls Excel 2000
Details and some samples of some of the functions available.


Games

Hangman.xls Excel XP Wednesday, 15 February 2006
This is based after the Hangman game. There are 10 words. They are words common to Excel Users. The words are randomly selected after each RESET. You have 8 chances to guess the word. Select the Option button Letter to select that Letter. Click the "Try Selected Letter" button. If you guess correctly, the selected letter is put into the word. If you guess incorrectly, a body part is revealed.


LottoMaker.xls Excel XP 26 Feb 2006
Random selection of six numbers utility. each selection can be pasted to the worksheet. It just might select your six lucky numbers!
Protection

PasswordProtected.xls Excel 2000
When you open the workbook, an input box asks for a password (it is already in the answer box). If you press cancel or do not enter the correct password, the workbook closes. There is a hidden sheet that will not be revealed till the correct password is entered. If you have successfully opened the workbook, when you close it, the hidden sheet (now revealed) will also be rehidden.


OnSheet Changes

CellAddress.xls  10 nov 2004 Excel 2000
This macro provides a message box of the cell address details of any nonblank cell. Example, if cell AB1 ="hello" then the message is:
The Row number is 1
The Column Number is 28
The Column Letter is AB
The Cell Address is $AB$1

SportsFixtures.xls  10 November 2004 Excel 2000
This macro will provide a list of team versus team. The team number can be any quantity. For example Team A, Team B. Team A versus Team B, Team B versus Team A

RandomTeams.xls  27 January 2005 Excel 2000
This utility makes a team fixture based on the number of teams entered. Any number of Teams.

DeleteDuplicates.xls 1st December 2004 Excel 2000
Finally I have created the masterpiece of deleting duplicates. This macro will delete duplicate rows. Remember that large databases will take a long time to process. Try my sample data first to ensure it works properly. Also, certain links and formulas in your sheet (if applicable) may be affected due to rows being deleted. A Viewer is included to identify rows that you may want to view first and mark for deletion.

DelBlankRows.xls 1st December 2004 Excel 2000
This macro will delete existing blank rows. If there are any cell-interior colored cells but no value, the row will still be deleted. It tests the length of the String not if the row is Empty.

HideXrow.xls 11 April 2005 Excel 2000
Hide rows in Column 1 that have "X" in the cell. you can change the strX constant in module1 to any word or sentence you choose.

FilterDelete.xls 6th April 2005 Excel 2000
This utility allows the user to determine the filter cells. Filters can be 1 and up to 256. The rows to be deleted are based upon the cell values you have pre-selected. Remember, long databases will take some time to process as rows are being deleted and the worksheet has to recalibrate itself. Expect up to or more than 3 minutes for more than 10,000 rows. I have included sample data and a toolbar button.

Book_Sheet_Selector_Pro.xls Created: 5th December 2005 Excel XP
This little form allows the user to change sheets or workbooks. The user can add or delete sheets, add or open or close workbooks and the lists will update when the user clicks the selection list. The utility only allows the user to change workbooks/sheets. It does not open, add, close or delete. Its main purpose is for users with more than 10 sheets in a workbook and/or more than 3 workbooks opened.
Bug: It is not totally perfected but is 99% okay. Closing a workbook does not always show the current active sheet as the one in the textbox. The user will have to select it from the list. There is no path property for a New Workbook. The New Workbook must be saved before the lists will update.


Userform Utilities

DateTimeMaker.xls 11 April 2005 Excel 2000
This utility uses dropdowns for the user to determine a Date value and or Time value. You also have a choice of formats. The results can be clicked into a cell.

NumbersToLongText.xls  10 August 2004 Excel 2000
This utility converts numbers to its Long Text Name. Example: 21 converts to twenty-one, 4002 converts to four thousand and two. It only converts whole numbers. The following is exempt: Currency, Decimals, Negatives. You can also send the result to a cell. May require VBA Addin.

SheetMenu.xls  Excel 2000
This utility sets a bar over the commandbars and menu bars. Access to sheets is via the tabstrip on the form. Although theSheet Tabs are hidden, the sheets are still visible. WARNING: Any hidden or very hidden sheets will now be visible. You can access the menu bars by clicking a cell then pressing the Alt key. Use the arrows to move through the menus. You cannot move the bar unless you click the X in the top corner to terminate the form. The password is 123. You have access to all features.

RandomGif.xls Excel XP
created: Tuesday, 08 November 2005Uses a Workbook_Open event to display a userform with a randomly selected Gif picture in an image box. The Gifs must be located in the same folder as the ActiveWorkBook. You can view the next picture by clicking the "Next Picture" button. A text box also displays the full path of your Gif. Click the picture to close the Display Form. Here are some sample Gifs. 8xGif.zip

RandomJpg.xlsExcel XP
created: Tuesday, 08 November 2005
Uses a Workbook_Open event to display a userform with a randomly selected Jpg picture in an image box. The Jpg must be located in the same folder as the ActiveWorkBook. You can view the next picture by clicking the "Next Picture" button. A text box also displays the full path of your Jpg. Click the picture to close the Display Form. Here are some sample Jpgs. 5xJpg.zip

PicText_Maker_Pro.xls created: 29 November 2005 Excel XP
Capture your worksheet area! Select your range of cells, one or many, then name that range "BigArea". Use the Nane Box for easy range naming (located to the left of the Function bar).
In a sample test I created a range of text and numbers. Four characters per cell, no columns auto-fit, range ("A2:HZ100"). This produced a gif 1.2 mb in size. The picture gif is saved in the Path of the activeworkbook, as the current time and date: Time_17_53_37_Date_20_Nov_2005.gif

PicExporter.xls Excel XP
created: Friday, 28 October 2005
Updated :Tuesday, 08 November 2005 Errors and bugs fixed.
This routine is a cheat-way of exporting WordArt, an inserted picture, or a drawing object from Excel. It uses a blank ChartArea which has the capabilities of exporting the chart area as a GIF. Make sure the worksheet is blank of text and values. Insert your clipart, wordArt, shape, object etc.. then click the Toolbar button which apears when the Workbook is opened. It is designed for only one object. More than one object in the sheet will affect the size proportions of the resulting gif. The ChartArea is resized to the size of the object. The gif is saved in the same folder as the workbook. There is minor whitespace in the border of the gif.

PictureBoxImporter.xls Created: 24 October 2005 Excel XP
Click the Open Folder Picture button to show the Open Dialog.Click Cancel when you have found the Folder you want. The Open Dialog does not open any files should you click the OPEN button. The file types have been preset in the Open Dialog. The Folder Location textbox shows your selected Folder of Picture Files. The File Type combobox shows you the type of file types allowed for the image control. The Files in the Current Folder listbox shows the compatible files. Click an item in the listbox to load it into the image control. Insert Picture button inserts the selected picture into the current active worksheet. The Hide Pic Box button allows you to hide the main form so you can get a better view of the inserted picture. A small form is shown at the Top-Left of your screen so you can reload the main form when you are ready. Also, I have resized the picture to approx. one-third its original size. Good for extra large pics, not so good for icons or small pics. Needs to be tested on small pics.

DataPanels.xls  26 November 2004 Excel 2000
This utility allows the user to scroll around the worksheet based on the row and column headings. It is like a mini worksheet. You can edit any cell via a text box.

DataPanels_XP.xls created: Wednesday,9 November 2005 Excel Xp
Similar to DataPanels.xls but a bit more sophisticated. The user uses the scrollers to navigate around the Active Worksheet. You can enter text in any cell, except Column 1 and Row 1. I have incorporated the use of Dynamic Text & Interior Color Scrollers. The User cannot change anything in Column A or Row 1 unles you use the Builtin Dialogs buttons. The Builtin Dialogs buttons can also be used to change the Font and Interior and Pattern Colors.

SelectWorkbooks.xls 16 Feb 2006 Excel XP
The Folder Open Dialog (Folder Open icon button) does not open any files should you click the Open button. It is used as a vehicle to determine the Folder path. Navigate to the folder where the files you want are located. Click Open or Cancel to close the dialog. If any files are in the Target Folder, they will be listed in the first listbox. You can select a single file or multiple files - use shift or ctrl key in unison with the mouse. Click ADD to move the selected filenames to the second listbox. If there are any files you do not want to open, you can select a single file or multiple files (use shift or ctrl key in unison with the mouse) to REMOVE back to the first listbox.You cannot list files from more than one location (Folder). The lists are cleared each time you click the Folder Open (icon) button. If a filename is listed and that workbook is already opened then the filename is removed from the list and returned to the first listbox. The other files in the second listbox will be opened. When you end the program, the file path will change back to the location of this workbook.

XdataDialog.xls created: Tuesday, 25 October 2005 Excel XP (Updated 25 Jan 2006 so the list box clears if the start button is pressed repeatedly)
This utility shows information about the drives on your computer.Drive Letter, Drive Type, Volume Name, Ready?, Free Space, and Serial Number.

Window_Watcher_Pro.xls created: Tuesday, 26 January 2006 Excel XP
This utility allows the user to "Watch" certain cells in any workbook in any sheet. It is not restricted to the active sheet like the built-in Watcher provided by Excel.

SumUps.xlsCreated: Friday, 28 October 2005 Excel XP
Click the Toolbar button to summarise the values from the other sheets to the Summary sheet. It assumes there are no totals in the sheets to be summed, ie, values only. There is no error checking. This is only a sample test.

EasyText.xls  15 January 2005 Excel 2000
This utility allows the user to use the mouse to click the keyboard keys (buttons). There are two listboxes with easy words. The list is taken from columns P and Q. There is limited formatting as the output is put into a Textbox. When you are done, select All Text, then copy, click hide Easy Text, then double-click or press F2 (edit mode), then right-click or click paste button on worksheet toolbar or ctrl-v to paste the selected text into the cell. The 255 character limit is ignored. If you paste to the worksheet instead of into the cell itself, the selection pastes to rows. if more than one workbooks are open, EasyText calibrates the sheets names into a dropdown but ignores hidden and very hidden sheets. for more detailed information please read the Read Me in the form.

EasyText_Rev1.xls  27 January 2005 Excel 2000
View the EasyText_rev1 Help File for details on this complex utility.


Web Page Create

WebPageDataTableMaker.xls Excel 2000
If you do not like Excels version of "view as a web page" then you will like this macro: a simple macro converting a range of data to a table in a web page.
A pre-defined macro that will create a web page based on 10 rows and 10 columns (range("A1:J10") of data. It does not do images. It uses a table structure to show your data within the range. It just uses basic html. The result will be the "sheetname".htm in the current directory and folder where the workbook exists. Warning: If you run the macro a scond time, or more, the existing "Sheet1.htm" will be overwritten. No warning or msgbox alert. Open the new htm file in excel and all cells in the range will have a table-like structure, borders and all.

WebTablesImport.xls 12 April 2005 Excel 2000
This will import web tables into the Import Sheet. Each table is preset to 40 rows. If your table is larger the 40 rows, it will be overwritten by the next table. Range Names for the External Data are deleted.

WebCreator_Simple.xls 3 December 2005 Excel XP
Create your own web pages for Excel Worksheets. This is not XML! I have only used basic HTML. Cell data is put into a Table (options for borders available). Objects such as Clipart, WordArt, Charts, onSheet Form Controls etc are saved in GIF Format into the activeworkbook Path. Any objects that have a space in their names will have that space replaced with an underscore. You can Name the web sheet, add Meta Keywords, Put in a Title. Objects are optional and you can decide from the list of current objects which to include into the web sheet or none...


Make_Workbooks_From_List.xlscreated 4 March 2006 Excel XP

Makes blank workbooks from a list in the workbook, for this instance, a list of numbers.


Make_Copies_of_Workbooks.xls Make_Copies_of_Workbooks.xls created 4 March 2006 Excel XP

If the Target or Source Folder does not exist, it will be created - Source_Folder, Target_Folder. If the Source Folder contains only one workbook, this will be the Source Workbook. Otherwise, you will be asked to accept or reject the next workbook in the Source Folder. You will be alerted if no Source Workbook is selected or if the Source Folder is empty. The source Workbook will be saved - as a copy - into the Target folder. If the Target Workbook already exists, you will NOT be alerted. It will be overwritten. The Target Workbook Name is based on the Cell Values in this workbook, starting at Cell A2 and only ending when the cell value is empty...
4 March 2006 Copyright




Converters:
Utilities that convert things from one unit type to another unit type.


NumberWordConversion
Click a Cell to evaluate or type the word number (or integer number) into the Textbox. If it is a Cell Value then click the "Cell Contents" button else click the "Textbox Contents" button to setup the number context. If it is a word number to integer (one thousand to 1,000) then Click the Right Arrow Button. If it is an integer number to a word number (1,000 to one thousand) then Click the Left Arrow Button. Only one button will be enabled, anyhow.
Currency or regular numbers are acceptable. Also, the Currency type accepted is: Dollars, Pounds, Euros, Yen, or None. Funny numbers, mixed words/numbers (one hundred dollars and 00/100) etc are not acceptable and will either not be converted or the results may not be what is expected. The maximum number is 999,999,999,999,999.99 and the minimum number is 0. Negatives are read as a postive number.
There are variations in different countries on what determines a Trillion or Billion. The following shows the determination of this Utility:
  1. 999 million 999 Thousand 999 + 1 = One Billion
  2. 999 Billion 999 Million 999 Thousand 999 + 1 = One Trillion
  1. One Trillion = 1,000,000,000,000
  2. One Billion = 1,000,000,000
  3. One Million= 1,000,000
  4. One Thousand = 1,000
  5. One Hundred = 100
  6. Ten = 10
  7. Zero = 0
Click
HERE for NumberWordConversion.xls
NumbersToLongText.xls
This is a simple number to word converter. It only accepts whole numbers.
Click
HERE for NumbersToLongText.xls