Posts by tejas57

    I currently create an outlook express email msg from vba:

    HLink = "mailto:" & Recipient & "?" & "cc=" & Recipientcc & "&" & "bcc=" & Recipientbcc & "&"
        HLink = HLink & "subject=" & Subj & "&"
        HLink = HLink & "body=" & msg
    ActiveWorkbook.FollowHyperlink (HLink)

    I'd like to define the "From" field in the email. Is there a way to do this?

    If not, are there other techniques to create/send a vba outlook express email which allow me to define the "From" field?


    My prior post may have been confusing, I apologize for that. I've provided a link to a full page snapshot (file too large to load here):…/sketch_example_jul02.jpg

    My worksheet "table" uses the excel cell data validation method with a list value to provide the user a selection list for a cell. I can select a range of cells (ie column) and assign the data validation method in one step. The selection list is maintained in a cell range which can be edited by the user. The data validation technique is similar to a control toolbox, but much easier to work with. I have vba functions which read/write the "table" cell contents.

    The remainder of my controls are from the control toolbox. These include command buttons, combo box, text box, etc. I do not use the older controls.

    As seen in the attachment, the drawing area, "table" and command buttons take up a lot of display space. I'd like to consider moving the "table" and command buttons to another window to improve the drawing area view and usability.


    I currently have the attached "table" implemented on a worksheet. The "table" has multiple rows which can be added/deleted by the user. Each column in the table has a different behavior. Column 1 (Index) can be selected but not changed by the user, the value is assigned by the system. Column 2 (Level) is a list box from which a user makes a selection. Column 3 (Area) is a combo box the user selects from or enters a value. Column 4 (Name) is a text box (or could possibly be setup as a combo box). Column 4 (Adjust) is a list box. Column 5 (Sq Ft) is filled in by the system and not modifiable by the user.

    The table was easy enough to setup on a worksheet using controls. I select a column range, setup the characteristics and all cells in that column have the desired behavior. I now want to move the "table" to a userform since it is difficult to view my excel drawing area and the "table" in the same window.

    Is there a control or method to create a userform "table" in which each column has different characteristics?

    The only way I've found to create the userform "table" so far is to create a separate control for each "table cell", ie List box, combo box, etc. This will work but will require more work to setup/maintain. The generic "table" with customizable columns would work much better if available.

    I'm open to any other suggestions you may have.



    • table_example.jpg

    Thanks for the response.

    I agree caculating the area is difficult, but not impossible. Most 3rd party sketch packages have this capability. Some work well, some work poorly. The general algorithm is split the area into smaller geometric shapes for which you can calculate the area (ie rectangles, triangles, etc), compute the area for each of these shapes and add them together. I think Excel uses a similar approach when filling in a freeform shape.

    Excel freeform generates x,y coordinates for each point or 3 sets of x,y coordinates for a curved line. This info should be adequate to compute the area.

    One of the possible sources I've been investigating is a grad student phd or similar research. I'm sure this has been done and it's available somewhere, just need to track it down.

    Is there a tool to calculate an Excel freeform shape's area?

    I'm trying to use the Excel drawing tools as a simple sketch tool, draw a floor plan and calculate the area. I'd like to find an addin which provides the sketch functions, but would settle for an area calculation tool.

    Fyi, I've also posted this on the MS Excel VBA Programming and the the MS Excel Misc forums. Several suggestions, no solutions so far.


    Thanks for the response. I was not aware of the cross posting policy, will try to follow it in the future.

    After further investigation, there does not appear to be a clear method to obtain the Excel Product Id Key from the registry due to the many variations in the registry format for the different Excel installations.

    Due to the variations in the registry format, I will follow your suggestion and use the C: drive serial number for the licensing. This will require a new license whenever the C: drive is swapped out but that is acceptable for my usage.

    I also noticed the drive serial number obtained programmatically does not match the serial number on the disk drive box. This may be a result of converting the text number on the box to a 9-10 digit decimal number. As long as the serial number is a unique identifier then it should be ok for my usage. I'll provide a small utility to the users which they can run to obtain their disk serial number.


    I'm trying to obtain the Excel Product Id key via VBA to use in a simple workbook licensing system. I want the Excel Product Id shown by the Excel Help. The workbook usage will be limited to licensed machines.

    I've looked at the earlier thread "Excel Product Key via VBA" and picked up the code to read the registry.

    The problem is the organization of the registry. Where do I look in the registry to find the Excel Product Id Key for all Excel installations?

    I have MS Office 2003 installed. To obtain the Excel key, I use:

    "Software\Microsoft\Office\11.0\Registration" & Application.ProductCode

    and the "ProductId".

    I doubt this will work if the user has a different version of MS Office installed or only Excel. I need the general method to find the Excel key.

    Does anybody know how to find the Excel Product Id key in the registry for all (most?) Excel installations?

    Also, Please submit any suggestions you have for better machine specific key(s).


    I've developed an extensive Excel/VBA system for generating real estate related forms. It consists of several Excel workbooks with embedded VBA to simplify many functions. Other people have seen my work and want to use it. I'd like to provide the functionality to these people, but I don't want to hand over all the source code and have somebody else begin selling it. Is it possible to:

    - Is there a technique to allow users runtime access to the VBA code but not access to the source? Possibly locking the VBA with a password or compiling the VBA and distribute only the compiled version.

    - Is there a simple licensing technique for an Excel workbook? I'm thinking something like a new user has to provide his machine name/id/or something similar which is added to a license list. When the workbook starts, it checks for a license match and aborts if none. I'm looking for a simple solution that will handle 99% of the cases and not worry about a bulletproof solution for all cases.



    I use Excel to create reports. I use Excel names, references, VBA and controls extensively to generate the reports. I have an Excel workbook which contains the basic report. Additional forms are contained in separate Excel workbooks. When I create a report, I copy the needed forms from other Excel workbooks into my basic report workbook. I am running Excel 2003, MS XP Pro.

    The process works, but I would like to make it more user friendly and automated. I would appreciate any utilities and/or code that does the following. Any suggestions are also welcome.

    The following utilities are needed, I anticipate they will be invoked from my base report workbook:

    - File selector. From Excel, select a "File Selector" control button which invokes a File Selector dialog box. The user selects which form(s) (ie workbooks) will be copied to the base report.

    - Worksheet copy. Copy the worksheets from the selected workbook and insert them into the base report workbook, preferably after a specified worksheet.

    - Remove local Name references. After I import the worksheet, I currently use the NameManager to remove the local name references contained in the imported worksheet(s). When the local names are removed, the workbook global names are used, which is what I want. I've tried to remove the local names when I create the form worksheet, but have not been able to do so.

    Any and all suggestions appreciated.

    muchas gracias.

    Ivan, thanks for the suggestion.

    I do have references to the "IsMissing" lib in my code. I was trying to upgrade to the vendor's new sw version. Unfortunately, we encountered a problem and I could not use the new version. I currently have the old and new interface code in my VB code, with only the old interface accessible thru the HI. This will allow me to easily activate the new interface if the vendor can resolve the remaining issue. I'm the only person using the sw at this time so the situation is semi-manageable.

    I also may have mistakenly stated earlier the "IsMissing" lib has existed for a couple months. This may have happened recently due to a license limitation. I'm trying to clarify this with the vendor.

    I'm still a bit confused why the "MISSING" 3rd party lib would cause a problem with the std Excel lib functions.

    After further investigation, I think the problem was related to the 3rd party missing lib. I registered it again via "regsvr32" and Excel started working. There appears to be some type of dependency between the VB std lib functions and VB "MISSING" lib references.

    There may have been a license limitation or similar issue with the 3rd party sw which caused it to un-register itself recently.


    I've been using Excel 2002 on XP for approx 9 months to develop a fairly complex Excel real estate appraisal system. I have an extensive list of worksheets, cell name references, menus, vb code, etc.

    Things were working fine til this evening. I now receive a "Can't find project or library" popup dialog error when I open my workbook or try to do certain things. This was working yesterday. It cannot find functions like "Chr" and "Left". I believe these functions are in the "Visual Basic for Applications" reference.

    As a test, I created a brand new excel workbook and tried a vb function with "Chr", it worked.

    It looks like my complex workbook has dropped some library references. Any idea how I get them back? Are there upper limits on the Excel Workbook complexity that I could be running into?

    I've looked at the VB - Tools - References and I have one "MISSING" entry for a 3rd party sketch package which I backed out due to some problems with their sw. I've been running with this Missing entry for several months with no problem.

    Any suggestions?


    I found a better way to insert the worksheet so I'll pass it on:

    To copy an Excel Worksheet with many references into a Workbook:
    1. Copy the Worksheets to a new Workbook before you start, ie new_book.

    2. When starting a new assignment, copy new_book to the work folder.

    3. Open new_book and report_book in the same Excel session.
    Use window-arrange-horizontal to view.

    4. Foreach worksheet to be added:
    Drag the worksheet from new_book to report_book.
    Specify "No Update" to the pop up prompt.

    Delete the worksheet specific references using the NameManager add on (identified elsewhere in this forum).
    Start the NameManager.
    Select the newly inserted sheet.
    Select "Multi" select option in NameManger.
    Select "Local to active sheet" option in Name Manager.
    May need to select "All" then "Local to active sheet" to update the Name Manager display.
    Delete the local references.
    All references from report_book should now be used in the newly inserted sheet.
    Confirm this by looking for any local sheet names in the newly added sheet.

    ExcelPower, I have the same problem you described. I tried your solution and encountered several issues:

    - When I did the paste, I received a prompt for each reference asking if I wanted to use the reference in the new workbook. This is good, but not practical if you have many references in your copied worksheet.

    - When past'ing, I selected "All" to obtain all options. The row/column formatting did not work. The row/col did work if I did these in a separate step, but again very cumbersome.

    - I didn't understand your last post about the "wildcard =". After I perform copy-paste-all and I answer the prompt for each reference, the references are updated to the new workbook. Maybe this is a result of the different version of Excel we are running, I'm running ver 2002.

    As an alternative, I noticed you can drag a worksheet from one workbook to another and maintain all the formatting. The drawback is it maintains references to the original workbook.

    Dragging the worksheet to the new workbook appears to work the best if you can update all the references in the copied worksheet in a single operation? Any suggestions how to do this?

    If somebody has a better method for handling many Excel fill-in forms, then please submit it. I was using a single workbook, but I'm creating too many worksheets and need to split them up and be able to easily combine based on the user's task.

    I have several worksheets at the beginning of a workbook which I use as a db. I then have a variety of fill-in forms, with references to the db worksheets, which the user needs to select and combine into a single report.

    I also noticed you need to do the copying within a single Excel session. Copying worksheets between Excel sessions creates other issues.



    I'm using an excel worksheet as a db. I import a text file (File1) to the worksheet via the Excel Data Import option.

    I now want to import a 2nd text file (File2). File2 has similar information as File1, but the corresponding columns are in a different order and may contain a different column label. How do I import File2 and specify the mapping from the File2 columns to the worksheet columns?

    I thought Data Import would allow you to specify the incoming->worksheet column mapping, but I can't find this.

    Do I have to import File2 into a separate worksheet, then setup a macro to copy to the 1st worksheet? If yes, I'd appreciate any macro examples to perform the column copy.


    Right Button your checkbox and a menu will appear. Select "View Code" The VB
    Editor should appear with the function shell in place. Complete the function with whatever action you want to take place.

    In the VB Editor, you also can select other events for your checkbox and define functions for them.

    I'm adding images to an Excel worksheet via Sheet.Shapes.AddPicture. All the images are added with an Excel name of "Picture nn". AddPicture does not return a handle to the loaded picture.

    Since all the images are loaded with a name of "Picture nn", how do I determine which picture is which? I may want to delete and reload some pictures, but I have to identify which "Picture nn" is the image of interest.


    There are several options available. I downloaded the Name Manager from Charles Williams. Appears to work well with many options. Thanks Charles.

    I've included Charles msg below:

    From: Charles Williams
    Date Posted: 2/19/2003 12:16:00 PM

    Hi Alan,

    You could use the Name Manager developed by myself and Jan Karel Pieterse.
    Download from

    That will handle very long names.

    There is also an API solution and a COM addin available at

    But I prefer Name manager <vbg&gt;

    Decision Models
    The Excel Calculation Site