Results 1 to 2 of 2
  1. #1
    3 Star Lounger djmoore's Avatar
    Join Date
    Feb 2001
    Location
    New Jersey, USA
    Posts
    371
    Thanks
    21
    Thanked 1 Time in 1 Post

    Tricking out a spreadsheet (Excel 2003 SP2)

    I (still) maintain a database of printers in Excel (will eventually move it to Access when I have time to learn it) but for now I want to minimize sloppy updates by authorized users -- One of the basic rules for updating the printer list is to make sure the printer isn't already listed, and to do this just search for the IP address. If it already exists in the spreadsheet , don't add it !! Unfortunately this is easily forgotten, and although I only have one other person updating the list, once a printer has been added it can't be removed. The harm is done, and I have to do some embarrassing cleanup elsewhere.

    All I want to do is to force a splashscreen whenever this particular spreadsheet is opened, which reminds the user to check the list before adding a printer. Playing a wav file might be nice, but that's really overkill. I'd prefer something where they have to click an OK button to remove it and proceed, but for ease of use a timed splash screen is probably the best way to go.

    One more thing - I used to understand macros (well, in Symphony I did) but since that time my brain has apparently petrified considerably. Can anyone recommend a resource (book, preferably) where I can learn about macros? I'm not interested in just getting a list of cool macros to use - I'd much rather understand how they work so I can write some sensible ones for my own use.

    Thanks!
    Have a cookie -

    Don

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tricking out a spreadsheet (Excel 2003 SP2)

    A simple solution:
    - Open the workbook.
    - Press Alt+F11 to activate the Visual Basic Editor.
    - Double click ThisWorkbook in the Project Explorer (the treeview on the left hand side, resembling the left hand pane of Windows Explorer).
    - Enter or paste the following code, and modify the message text as needed:

    Private Sub Workbook_Open()
    MsgBox "Please check the list of printers carefully before adding a printer!", vbInformation
    End Sub

    It is possible to enforce unique entries without using VBA code. Let's say that IP addresses can be entered in column A.
    - Select column A.
    - Select Data | Validation...
    - Select Custom in the Allow dropdown list.
    - Enter the following formula:

    =(COUNTIF($A:$A,A1)=1)

    - Activate the Error Alert tab.
    - Make sure that Stop is selected in the Style dropdown.
    - Enter an appropriate error message in the error message box.
    - Click OK.

    I like Excel Books by John Walkenbach - he has two books about Excel 2003 VBA.
    Also see <post:=582,078>post 582,078</post:>.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •