• Downloads - Learning Aids

    by Published on 2011-05-05 06:13 AM     Number of Views: 31557 

    Please Note: This text is the complete reproduction of Chapter 17 - Security if Microsoft Office from my book RibbonX: Customizing the Office 2007 Ribbon, and is reproduced with permission of Wiley Publishing who retains the copyright of this work.

    This chapter was obviously written for Excel 2007, and still holds true for Office 2010.

    In addition, this chapter also discusses macro security applicable to Office 97 through Office 2003. Key portions to read for these versions are Security Prior to Office 2007 and Digital Certificates. While some of the screens may look a little different, they should be close enough to help you understand how to set up a SelfCert digital certificate and add it to your projects. These steps will allow you to avoid macro warnings in your programming environment, without having to sacrifice security by setting your security levels to low. ...
    by Published on 2008-05-01 09:32 PM     Number of Views: 19225 

    What are Styles?
    A style is pre-defined set of formatting instructions held together in one convenient package. They can be configured to apply one, two or many formatting characteristics, and can be applied to a worksheet cell or range very easily.

    Why use Styles?
    The biggest reason that you'd want to use styles is their ease of use when you want to update a cell, be it a color/colour change, number style or font. If you have a style set up, you simply need to change it in one place, and every other cell in your entire workbook that uses that style is updated with it. ...
    by Published on 2008-01-16 07:11 AM     Number of Views: 26839 

    I'm very pleased to be able to share a Chapter from my new book, RibbonX - Customizing the Office 2007 Ribbon.This chapter discusses the comboBox and dropDown RibbonX elements in detail. From using Microsoft's built in elements to creating your own with XML and programming the callbacks with VBA, it displays how to work with them. As you'll see, we provide practical examples for working with the elements in Excel, Word and Access so that you can follow along. ...
    by Published on 2006-10-21 06:28 AM     Number of Views: 46391 

    One of the biggest parts lacking in user constructed solutions is documentation in the form of help files. As coders, we usually find it easier to build the application than explain to someone how to use it properly. After all, what we write in code is rarely read by others, and most novices think it's gibberish anyway. Actually explaining what you do in plain English (or whatever language you use) can be quite difficult. Ironically, it is this very fact that tells us why help files are important... write it down once, nicely, and you should never have to explain it again.

    Office makes use of Compiled HMTL (chm) help files, which can be created by any number of applications. While writing these documents can be a difficult task of explaining yourself to the end user, the final trick is getting the compiled file to work with your application. This article was written to help ease the burden of the coding part, but unfortunately I'm going to leave the actual construction of the files to you, although I will give you some pointers to get started.

    Programs to build help files:
    First thing is first, you'll need to install a copy of Microsoft's HTML Workshop, if you don't have it installed already. This program is used by all Help authoring software, so it's not really an optional thing.

    The next step is to find a suitable HTML Help compiler. Personally, I use West Wind Help Builder. It has an excellent FAQ included on how to start using the program, and allows for creation of some very rich help files. I'm a big fan of being able to easily write my documents including screen shots, hyperlinks, and context IDs, and West Wind Help makes this fairly easy. It even integrates nicely with another fantastic product, Tech Smith's SnagIt, for capturing nice screen shots. There are trial versions of both products available at the links provided.

    Other programs do exist, such as "Help n Doc", Shalom Help, HelpScribble, Robohelp and others, but I cannot speak to their usefulness, as I have not worked with any of them extensively.

    Built in Help methods:
    Microsoft Excel does have some built in help constants and methods that you can use to deliver help ...
    by Published on 2006-09-19 07:10 AM     Number of Views: 84584 

    This article gives two methods to fill a multi column listbox on a userform with data from an Excel worksheet. ...
    by Published on 2006-08-14 06:39 AM     Number of Views: 11136 

    This article shows the method I use to maintain versions and builds in my applications and add-ins, which is to add properties to the ThisWorkbook module. These properties store their values in custom document properties, accessed from File|Properties|Custom, as shown below:
    Benefits of this approach
    Some of the benefits you get by using this method are:
    • You get a place to store a value within the workbook, but avoid the need to store it in a worksheet cell. this can potentially save you creating a worksheet specifically for this purpose.
    • You can avoid the use of code to place information in the registry, which makes a workbook more portable between users.
    • You can store a piece of variable information between procedures. (Variables go out of scope after procedures complete.)
    • You can use intellisense to easily call up a property when coding

    Drawbacks of this approach
    • Users can change or delete these values if they know that they exist, or have reason to do so.

    Other uses
    The other uses for this are numerous, but one that stands out, ...
    by Published on 2006-01-11 10:28 AM     Number of Views: 20657 

    Macro Purpose:
    • Function to show the formula in another cell as text.
    by Published on 2005-01-04 09:32 AM     Number of Views: 19693 

    Function Purpose:
    This function can be used to retrieve one of three types of usernames, and can be used in a worksheet cell or in VBA.

    The usernames that it can return are:
    • Microsoft Office username (default) as defined in Tools|Options|General
    • Microsoft Windows logon name
    • Computer name (as defined in your system properties)

    by Published on 2004-12-20 08:09 AM     Number of Views: 56525 

    Macro Purpose:
    • Exports a table of data from Excel into a database, using an ADO connection to pass SQL strings.

  • MVP Logo
  • Recent Forum Posts


    Need help with total monthly hours

    Enter times as follows: If it's 2:05 in the afternoon enter as 2:05 PM or 14:05.
    The attached should handle times across midnight, but check....

    p45cal Yesterday, 11:22 PM Go to last post

    Need help with total monthly hours

    I took the 12 out and if you key in 0:30 it row 12 it still subtracts from row 16 which is what it is supposed to do. I've tried every format I could...

    dcope7 Yesterday, 07:05 PM Go to last post

    Need help with total monthly hours

    It does not! it adds the value 12 and doesn't even look at row 12! In this context it adds 12 days (=288 hours). Barmy.
    Until you get formulae right...

    p45cal Yesterday, 06:02 PM Go to last post

    Need help with total monthly hours

    Ok row 16 calculates rows 11 and 15, plus it subtracts row 12 and yes I know I have it adding but if you actually add 0:30 in row 12 it will subtract...

    dcope7 Yesterday, 05:23 PM Go to last post

    Need help with total monthly hours

    I'm talking about row 16, NOT ROW 12!
    The 12 is a hard-coded 12 in the formula, and it adds (not subtracts) 12. The formula in cell D16 (yes, the...

    p45cal Yesterday, 05:10 PM Go to last post