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_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.
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
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.
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.
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.
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.
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 RandomJpg.xlsExcel XP PicText_Maker_Pro.xls created: 29 November 2005 Excel XP PicExporter.xls Excel XP PictureBoxImporter.xls Created: 24 October 2005
Excel XP DataPanels.xls 26 November 2004
Excel 2000 DataPanels_XP.xls created: Wednesday,9
November 2005 Excel Xp SelectWorkbooks.xls
16 Feb 2006 Excel XP 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) Window_Watcher_Pro.xls
created: Tuesday, 26 January 2006 Excel XP SumUps.xlsCreated:
Friday, 28 October 2005 Excel XP EasyText.xls 15 January 2005 Excel 2000 EasyText_Rev1.xls 27 January 2005 Excel 2000
WebPageDataTableMaker.xls Excel 2000 WebTablesImport.xls 12 April 2005 Excel 2000 WebCreator_Simple.xls 3 December 2005 Excel XP
Makes blank workbooks from a list in the workbook, for this instance, a list of numbers. 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...
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.
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.
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
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.
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.
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.
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.
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.
This utility shows information about the drives on your computer.Drive Letter, Drive Type,
Volume Name, Ready?, Free Space, and Serial Number.
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.
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.
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.
View the EasyText_rev1 Help File for details on this complex utility.
Web Page Create
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.
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.
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
Make_Copies_of_Workbooks.xls Make_Copies_of_Workbooks.xls
created 4 March 2006 Excel XP
4 March 2006 Copyright