Prevent Users from Performing Certain Actions In Excel

Preventing Save As... in a Workbook

You can specify that any workbook be saved as read-only by choosing Office button ➝ Save ➝ Tools Button ➝ General Options and enabling the “Readonly recommended” checkbox (pre-2007, File ➝ Save As ➝ Tools [Options on the Mac] ➝ General options in the Save options dialog). Doing so can prevent a user from saving any changes he might make to the file, unless he saves it with a different name and/or in a different location.

Sometimes, however, you might want to prevent users from being able to save a copy of your workbook to another directory or folder with or without a different name. In other words, you want users to be able to save on top of the existing file and not save another copy elsewhere. This is particularly handy when more than one person is saving changes to a workbook because you do not end up with a number of different copies of the same workbook, saved with the same name in different folders. The Before Save event you’ll be using has existed since Excel 97. As its name suggests, this event occurs just before a workbook is saved, enabling you to catch the user before the fact, issue a warning, and stop Excel from saving.

To insert the code, open your workbook and choose Developer ➝ Visual Basic, then select View ➝ Code, and double-click on ThisWorkbook in the Project Explorer (pre-2007, right-click the Excel icon immediately to the left of the File menu item on the worksheet menu bar, and select View Code.

Type the following code into the VBE, and pressAlt/c-Q to get back to Excel proper, then save your workbook:

Private Sub workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim lReply As Long
If SaveAsUI = True Then
lReply = MsgBox("Sorry, you are not allowed to save this " & _
"workbook as another name. Do you wish to save this " & _
"workbook?", vbQuestion + vbOKCancel)
Cancel = (lReply = vbCancel)
If Cancel = False Then Me.Save
Cancel = True
End If
End Sub

Give it a whirl. Select Office button ➝ Save (pre-2007, File ➝ Save) and your workbook will save as expected. However, select Office button ➝ Save As (pre-2007, File ➝ Save As...) and you’ll be informed that you’re not allowed to save this workbook under any other filename, unless you’ve disabled macros.

Preventing Users from Printing a Workbook

Perhaps you want to prevent users from printing your workbook—and probably having it end up in a recycling bin or left on a desk somewhere in plain sight. Using Excel’s Before Print event, you can stop them in their tracks. Enter the following code, as before, into the VBE:

Private Sub workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox "Sorry, you cannot Print from this workbook", vbInformation
End Sub

Press Alt/c-Q when you’re done entering the code to get back to Excel, then save your workbook. Now each time users try to print from this workbook, nothing will happen. The MsgBox line of code is optional, but it’s always a good idea to include it to at least inform users so that they do not hassle the IT department, saying there is a problem with their program!

If you want to prevent users from printing only particular sheets in your workbook, use this similar code instead:

Private Sub workbook_BeforePrint(Cancel As Boolean)
Select Case ActiveSheet.Name
Case "Sheet1", "Sheet2"
Cancel = True
MsgBox "Sorry, you cannot print this sheet from this workbook", _
vbInformation
End Select
End Sub

Notice you’ve specified “Sheet1” and “Sheet2” as the only cases in which printing should be stopped. Of course, these can be the names of any sheets in your workbook; to add more sheets to the list, simply type a comma followed by the sheet name in quotation marks. If you need to prevent the printing of only one sheet, supply just that one name in quotes and drop the comma.

Excel Tips

Excel users know that workbooks are a powerful metaphor. But many users are equally aware that dealing with workbooks can cause a huge number of snags. The hacks in this chapter will help you avoid some of these snags while taking advantage of some of the more effective but often overlooked ways in which you can control your workbooks.

Before we leap into the hacks, though, it’s worth taking a quick look at some basics that will make it much easier to create effective hacks. Excel is a very powerful spreadsheet application, and you can do incredible things with it. Unfortunately, many people design their Excel spreadsheets with little foresight, making it difficult for them to reuse or update the spreadsheets they’ve so carefully built. In this section, we provide several tips you can follow to ensure that you’re creating spreadsheets that are as efficient as possible.

The 80/20 Rule

Perhaps the most important rule to follow hen designing a spreadsheet is to take a long-term view and never assume you will not need to add more data or formulas to your spreadsheet because chances are good that you will. With that in mind, you should spend about 80 percent of your time planning your spreadsheet and about 20 percent implementing it. Although this can seem extremely inefficient in the short run, we can assure you that the long-term gain will far outweigh the short-term pain and that the planning gets easier after you’ve done it for a while. Remember that spreadsheets are about making it easy for users to get correct information, not just about presenting information that looks good only once.

Structural Tips

Without a doubt, the number one mistake most Excel users make when creating their spreadsheets is that they do not set up and lay out the data in the manner in which Excel and its features expect. Here are, in no particular order, some of the most common mistakes users make when setting up a spreadsheet:

• Unnecessarily spreading data over many different workbooks
• Unnecessarily spreading data over numerous worksheets
• Unnecessarily spreading data over different tables
• Having blank columns and rows in tables of data
• Leaving blank cells for repeated data

The first three items on the preceding list add up to one thing: you should always try to keep related data in one continuous table. Time and time again we see spreadsheets that do not follow this simple rule and thus are limited in their ability to take full advantage of some of Excel’s most powerful features, including PivotTables, subtotals, and worksheet formulas. In such scenarios, you can use these features to their full potential only when you’ve laid out your data in a very basic table.

It is no coincidence that Excel spreadsheets can comprise 1,048,576 rows (65,536 pre-2007) but only 16,384 columns (256 pre-2007). With this in mind, you should set up tables with column headings going across the first rowof your table and related data laid out in a continuous manner directly underneath their appropriate headings. If you find you are repeating the same data over and over for two or more rows in one of these columns, resist the temptation to use blank cells to indicate repetition.

Make sure your data is sorted whenever possible. Excel has a rich set of lookup and reference formulas, some of which require that your data be sorted in a logical order. Sorting also will speed the calculation process of many functions significantly.

Formatting Tips

Moving beyond structure, formatting also can cause problems. Although a spreadsheet should be easy to read and follow, this should rarely be at the expense of efficiency. We are big believers in “keeping it simple.” Far too many people spend tremendous amounts of time formatting their spreadsheets. Although they don’t necessarily realize it, this time frequently comes at the expense of efficiency. Often the overuse of formatting adds size to your workbook, and although your workbook might look like a work of art to you, it might look terrible to someone else. Some very good universal colors to consider using in your spreadsheets are black, white, and gray.

It is always a good idea to leave at least three blank rows above your table (at least three, preferably more). These can then be used for criteria for features such as Advanced Filter and Database functions.

People also tinker with the alignment of cell data. By default, numbers in Excel are right-aligned and text is left-aligned, and there are good reasons to leave it this way. If you start changing this formatting, you will not be able to tell at a glance if the contents of a cell are text or numeric. It is very common for people to reference cells, which look like numbers but in reality are text. If you have altered the default alignment, you will be left scratching your head. Perhaps headings are an exception to this rule.

Format cells as text only when completely necessary. All data entered into cells formatted as text become text, even if you meant for them to be numbers or dates. Worse still, any cell housing a formula that references a text formatted cell also will be formatted as text. Generally, you do not want formula cells to be formatted as text!

Merged cells can also cause problems. The Microsoft knowledge base is full of frequently encountered problems with merged cells. As a good alternative, use “Center across selection,” found under Home ➝ Alignment Group. The arrow in the bottom right will display the Format dialog with the Alignment tab active. Use the Horizontal drop-down to select Center Across Selection or right-click and choose Format Cells from the shortcut menu (pre-2007, Format ➝ Cells).


Formula Tips

Another enormous mistake users often make in Excel formulas is referencing entire columns. This forces Excel to examine potentially thousands, if not millions, of cells it otherwise could have ignored.

Assume, for example, that you have a table of data ranging from cell A1 to cell H1000. You might decide you want to use one or more of Excel’s lookup formulas to extract the required information. Because your table might continue to grow(as you add new data), it is common to reference the entire table, incorporating all rows. In other words, your reference might look something like A:H, or possibly A1:H65536. You would use this reference so that when new data is added to the table, it will be referenced in the formulas automatically.

This is a very bad habit to form and you should almost always avoid it. You still can eliminate the need to constantly update your formula references to incorporate new data as it is added to a table by using dynamic named ranges.

Another common problem with poorly designed spreadsheets is painfully slow recalculation. Many people suggest that shifting calculation mode into Manual via the Office button ➝ Excel ➝ Formulas (pre-2007, Tools ➝ Options ➝ Calculations; Mac OS X, Excel ➝ Preferences ➝ Calculation) will solve this problem.

However, this is generally very poor advice, fraught with potential disasters. A spreadsheet is all about formulas and calculations and the results they produce. If you are running a spreadsheet in manual calculation mode, sooner or later you will read some information from your spreadsheet that will not have been updated. Your formulas might be reflecting old values and not the updated values because when you go into manual calculation mode, you must force Excel to recalculate by pressing the F9 key (c-= on Mac OS X). However, it is very easy to forget to do this! Think of it this way. If your car brakes were rubbing and slowing down your car, would you disconnect the brake pedal and rely on the hand brake instead of fixing the problem? Most of us wouldn’t dream of doing this, but many people don’t hesitate to put their spreadsheets into manual calculation mode. If you need to run your spreadsheet in manual calculation mode, you have a design problem. Address it properly and do not use a “Band-Aid” approach.

Array formulas are another common cause of trouble. They are best suited to referencing single cells. If you use them to reference large ranges, do so as infrequently as possible. When large numbers of arrays reference large ranges, your workbook’s performance will suffer, sometimes to the point where it becomes unusable and you are forced to run your spreadsheet in manual calculation mode.

Although PivotTables might seem very daunting when first encountered, we highly recommend that you familiarize yourself with this powerful Excel feature because once you master Pivot- Tables, you will wonder how you survived without them!

At the end of the day, if you remember nothing else about spreadsheet design, remember that Excel works best when all related data is laid out in one continuous table. That should make the rest of your hacking much easier.

3D Modeling Types Available in AutoCAD

AutoCAD allows you to create three different types of 3D models: wireframe, surface, and solid. Each model type has strengths and weaknesses. When you want to create a presentation drawing of some sort, surface and solid 3D models are what you need; to create a solid or surface model, you can use a wireframe model as a template.

Wireframe model

Wireframe models are often referred to as 21⁄2D drawings because they aren’t 2D drawings in the true sense; they’re created with 2D objects but in 3D space. Wireframe models aren’t true 3D models because they lack faces, which allow objects that are beyond to be hidden when a 3D visual style is applied or a rendering is done. You can think of a wireframe model as being like a wire coat hanger or a chain link fence. Wireframe models are made up of standard 2D
objects that are given an additional coordinate value for the Z direction. You can use most 2D objects when creating a wireframe model, but objects such as lines, arcs, and circles are commonly used. The 2D objects are drawn in 3D using full X, Y, and Z coordinate values rather than just X and Y coordinate values. Figure-1 shows a wireframe model of a bracket that connects two parts together. The wireframe model is shown with the 3D Hidden visual style; all the individual 2D objects used to create the model are shown.


Surface model

Surface models are created from 3D objects called faces, which are true 3D objects in the sense that they contain a surface that can hide objects beyond them when a 3D visual style is applied or a rendering is done. Faces are created with three or four sides. Although the surface of a face can hide objects that are beyond it in the current view, they require some cleanup when shared edges are involved to provide a clean look that makes individual surfaces seem like a much larger surface.
AutoCAD offers a number of commands that allow you to create individual faces or primitive objects such as boxes, spheres, and cones. Figure-2 shows two surface models of the same bracket; the left model doesn’t have any of its edges cleaned up, whereas the right model has been cleaned up. The surface model is shown with the 3D Hidden visual style, and the objects closest to you in the current view hide the objects beyond them (unlike the wireframe model). Part IV covers surface models in greater depth.

Solid model

Solid models are created from 3D objects called solids, which are true 3D objects similar to faces that are used to make up surface models. 3D solids in AutoCAD are very different from surface models; these objects aren’t flat surfaces. Also unlike surface models, solid models don’t require additional cleanup of shared edges of the model because a 3D solid object is one continuous object instead of being made up of many different faces.
AutoCAD offers a number of commands that allow you to create primitive objects such as boxes, spheres, and cones along with editing the faces, edges, and bodies of a solid in many different ways. Figure-3 shows a solid model of a bracket. The solid model is shown with the 3D Hidden visual style, and the objects closest to you in the current view hide the objects beyond them (like a surface model does).

Introduction to AUTOCAD

AutoCAD is different from many other applications that you might already know and use everyday, and that’s primarily due to how it has evolved over the past two decades.
The CAD industry is always striving for change in an attempt to improve the processes that are used to create drawings. (CAD stands for Computer-Aided Drafting or Computer-Aided Design, depending on whom you ask.) One of the latest trends that has started to take off and redefine the industry in recent years is 3D modeling.

Although AutoCAD is primarily thought of as a CAD application used for 2D designs, it has a powerful 3D modeling engine that allows you to create 3D models of buildings or small parts used in a machine. 3D modeling in AutoCAD was introduced over a decade ago with AutoCAD R13 (and even before that as an add-on called Autodesk Modeling Extension for AutoCAD R12), but creating 3D models was very time consuming. In recent releases of AutoCAD, the 3D modeling
features have been completely overhauled to allow conceptual designing in AutoCAD and to create high-quality presentation renderings with less effort.

AutoCAD 2008 not only gives you the 2D drafting tools that you’ve come to use everyday, but it also provides a robust set of tools for 3D modeling and visualizing the 3D models you can create.