Tek Parts Manager
This is an MS Excel WorkBook which acts like an inventory list of Tek items. Several VB Macros can be initiated to aid in adding, editing or just finding parts.
As I published this I got the question to modify the code for Agilent/HP part numbers which I did. Find hpPartsManager page here.
April 03, 2010 - New version 1.2 !
Changes from 1.1 to 1.2:
Changed Ea to Qty on all sheets and
forms
New Data:
Tabbing in Qty (Ea) box now enters a 1 if box is empty
Minor change in appearance
Find / Edit Data:
If selected from a sheet other than 000 this sheet is now default i.e. in
select box and P/N box
Selecting another main P/N (moving to another sheet) now clears Ea, Loc
and rescan
Change in any P/N box now disables Qty (ea), Loc and Apply (if third
P/N box was changed e.g. -00 to -01 and applied the the initial worksheet value
e.g. -00 was changed)
Change in third P/N box now enables Find (changed in V1.1)
Changes from 1.01 1to 1.1:
Sheet 000 (Template):
Added code to replace the old pw protected 000 sheet if version is 1.0 or
1.01,
Added code to update version and date info if ever changed to higher than 1.1,
Removed password from protection,
Changed some text and color,
Added Update/Check version web address
with hyperlink,
Added "Mail To" text with hyperlink,
Increased width of Loc Column to 10.0.
New WorkSheet:
When creating a New Sheet it did not appear empty (last cell was not on
row 2).
New Data:
Only one character accepted in Loc Textbox - Changed max length of Loc
Textbox to
10,
Loc Textbox now isn't enabled by default, it becomes enabled when a
complete P/N has been entered,
Change in Loc Textbox now doesn''t clear P/N,
Double Click in Loc Textbox now clears the Textbox
Loc Textbox now accepts lower case letters
Tab from Ea Textbox now always
sets focus on Loc Textbox
Loc Textbox width slightly increased,
Window layout slightly changed for better tabbing (frames removed).
Find / Edit Data:
Rescan Button should now disable itself,
Loc Textbox width slightly increased,
Change in P/N Textbox 3'd group should not enable FindButton,
Change in P/N TextBox 1'st group should sometimes enable Find ,
When searched value in P/N Textbox 2'nd group was found regardless of value in
P/N Textbox 1'st
group,
Ea and Loc Frames are now not enabled by selecting P/N (Sheet),
Some Tab Stops and Tab Indexes were wrong,
Apply Button now doesn't do a ReScan.
Changes from 1.0 1to 1.01:
Bugfixes:
A search for a P/N (Ctrl + Z) where the center characters are the same as the first returned all entries i.e. it search for the center characters but the search
is done on the whole P/N rather than the center numbers only (e.g. search for 151-151 will return all 151-xxxx-xx),
Pressing Ctrl+A on the Template Sheet returned a Run Time Error,
Apply button on Find / Edit Data screen could sometimes be enabled when it shouldn't i.e. changes could be made to a P/N different from the one shown in the P/N frame.
Known Issue: Enter New Data window does not accept more than one character for Location.
Workaround: Change manually on Worksheet or use Find / Edit Data window (Ctrl + Z).
Version 1.0
Initial version.
Here's a screen shot of a typical Worksheet:
P/N is the Tek Part Number with the format xxx-xxxx-xx.
ea is the number of items in stock of the particular Part Number.
Location could be a number or label of a drawer or equivalent. Limited to 10 characters but works best with three characters or less.
Price could be the actual orignal Tek price or any price of your choice.
Used in and Description/Note are free text fields.
Press Ctrl + Q to create a new Worksheet:
Select a Worksheet and press Ctrl + A to add a part:
Enter the P/N. The number of items and Location are optional (The location of the last part will show up in the Loc box when this routine is initiated.)
Pressing space bar in a blank center box will enter the last P/N on the Worksheet + 1 (e.g.if last P/N is 151-0307-00 the new will be 151-0308) and move the cursor to the last P/N box.
Pressing Enter in a blank last P/N box will enter 00 and move the cursor to the Ea box.
Pressing Enter in a blank Ea box will enter 1 and move the P/N to the Worksheet.
The entered part will be added to the end of the list.
Click on Sort and a choice of Sort Only or Sort & Combine will show up. Sort & Combine will sort the entries and add the number of items to an already existing entry of the same P/N.
On any Worksheet press Ctrl + Z to find and/or edit the properties of a part:
From the upper drop down list select a P/N, i.e. Worksheet and the sheet is selected and scanned. All entries on this sheet are then added to the bottom drop down list.
To find a part either select from the bottom drop down list or enter it manually and click Find. A partial entry in the center box, e.g. 123, will find all entries that contain
this combination. In the example 155-0123-00, 155-1230-00, 155-1232-00 etc.
Number of items and Location can be manually changed.
The entries under Price, Used In and Description/Note have to entered manually by selecting each cell.
Download TekParts.xls v1.2 here.
Download updates only here. Includes instructions on how to update.
Email me with comments. /Håkan