How to Check Each Control of a Group in Excel VBA

1. Click the 'Developer' tab's 'Visual Basic' button to enter the VBA programming environment, then click the 'Insert' menu's 'Userform' item to create a new form on which to place controls. If the 'Developer' tab does not appear among your menu choices in Excel, you may need to turn on its display in Excel's options.
2. Click the 'Button' control on the tool box, then drag on the userform to create a button. Click the 'OptionButton' control from the tool box, then drag on the userform to create an option button. Right click this button and click 'Properties' to display a list of attributes for the control.
3. Click in the 'Enabled' row of the 'Properties' window, then type 'False' to indicate that the option button is unable to process input. Note that the command button, which you created in Step 2, is enabled by default.
4. Click the 'Frame' control on the tool box, then drag on the userform. Click the button control you made in Step 1, then press 'Control' and 'X' simultaneously to cut this control to the clipboard.
5. Click the 'Frame' control, then press 'Control' and 'V' simultaneously to paste the button inside the frame. Repeat the cut and paste with the option button to paste it into the frame as well. By placing both controls into the frame, you've made one group that contains both controls. You'll now write code that checks a property of each control.
6. Double click the userform to display the programming window, then paste the following program into the window. This program iterates through each control in the frame, reading the 'Enabled' property you set in step 3, and printing the value of that property.Private Sub UserForm_Click()Dim ct As ControlDim s As StringFor Each ct In Frame1.ControlsIf ct.Enabled = True Thens = ct.Name ' is enabled.'Elses = ct.Name ' is disabled.'End IfMsgBox sNextEnd Sub
7. Click the 'Run' menu's 'Run' command to display your userform, then click anywhere on the form that doesn't have a control. Your program will run, displaying message about the 'Enabled' property described in Step 3.

Read more ►

How to Stop Rounding in Excel

1. Open the Excel workbook that currently uses rounded numbers. Click on the affected worksheet.
2. Highlight the cell(s) that hold the numbers. Do so by clicking a single cell, dragging the cursor across several cells, clicking a row number or a column letter.
3. Right-click on the highlighted cell(s). Select 'Format Cells' from the menu, which opens the Format Cells dialog box.
4. Select the 'Number' tab. Click 'Number' in the Category pane. Press the up arrow next to the Decimal Places box to increase the number of digits after the decimal, such as '5' or '7' places after the point.
5. Click the 'OK' button to save the changes to number-rounding.

Read more ►

How to Plot a Titration Curve on Excel

1. Open a new worksheet in Excel, and create two column headings with the titles 'Titrant Amount' and 'pH Level.' Fill out the two columns with the data you have measured.
2. Highlight your entire data set, including your column headings, by clicking and dragging your mouse across it.
3. Click the 'Chart Wizard' button in the toolbar. Click the XY (Scatter) chart type to select it, then select a 'Scatter with data points connected by smoothed Lines' chart. Click 'OK'; Excel will generate the chart with the smoothed titration curve on top of the data series.

Read more ►

How to Unlock Spreadsheets

1. Click on the 'Start' button, and then click on 'All Programs.'
2. Click on 'Microsoft Excel.' The Microsoft Excel program will open.
3. Click on 'Review,' and then click on 'Unprotected Sheet.' You may be prompted to input your password if you used one to lock your spreadsheet.

Read more ►

How to Add a Footer in Excel 2003

1. Launch Excel 2003 and open the file to which you want to add a footer. Click once on the worksheet.
2. Click the 'View' menu and select the 'Header Footer' option. This action will launch a separate dialogue window.
3. Click the 'Header Footer' tab on the Page Setup window. Click the 'Custom Footer' button. This action will launch another dialogue window.
4. Click once in one of the three available footer sections (left, center and right). Type the desired text in any of the fields or use the appropriate buttons to add the date, page number or other spreadsheet information, as desired.

Read more ►

How to Uninstall an Excel 2007 Add

1. Start Microsoft Excel 2007.
2. Click the Microsoft Office Button located in the upper left corner.
3. Click on 'Excel Options' and choose 'Add-Ins.'
4. Choose 'Excel Add-Ins' in the 'Manage' box and click 'Go.'
5. Clear the check box next to the add-in or add-ins you wish to uninstall in the 'Add-Ins available' box. Press 'OK' when finished.
6. Close Excel and all other programs.
7. Open the 'Control Panel' from the 'Start Menu' and double-click the 'Programs/Programs and Features' icon (Vista) or the 'Add/Remove Programs' icon (XP).
8. Choose Microsoft Excel from the list and click 'Change.'
9. Follow the onscreen instructions to select the correct add-in and uninstall it from the list.

Read more ►

How to Create Multiple Formulas for the Same Space in Excel

Parenthetical Nesting
1. Locate the cell in which you would like to create multiple formulas.
2. Press the equal button on the keyboard to start a formula in that cell.
3. Combine multiple formulas into one nested formula using parenthesis organization. It is often necessary in Excel to arrive at a formula result after the processing of several intermediate formulas. While these could be split into individual cells, Excel allows many formulas to be combined into one. For example, if one formula requires that a cell range be added together, and another formula must subsequently multiply the sum by a constant, write the combined formula as '=(sum(a1:a15))*15' where the 'a1:a15' represents the cell range and '15' the constant multiplier.
4. Combine arithmetic formulas together into one formula using the standard order of mathematical operation. For example, if you need two separate formulas to calculate the products of two sets of numbers, and a third formula to find the difference in these two outcomes, combine them all in a simple formula as '=a1*3-a2*6'. This could be split into three formulas, but by using standard order of operation, the multiplication is handled first for each product before the subtraction is calculated.
'IF()' Function
5. Press an equal sign in the cell where you would like to create multiple formulas.
6. Type 'if(' to begin an 'IF()' function. These functions allows multiple separate formulas to be run depending on conditions. You can have one cell house at least two formulas that do not interact with each other or depend on each other's results.
7. Determine the condition for the 'IF()' statement. For example, perhaps you wish to calculate a number by another if that number is positive, but calculate it by a different number if the number is negative. Type the condition next, such that the formula currently reads '=if(a1>0', where 'a1' is the cell that determines which formula to use. Type a comma in the formula before proceeding.
8. Type the formula that will occur if the condition is true. The 'IF()' statement will thus currently read '=if(a1>0,a1*10' where 'a1*10' represents one of the two formulas you will include in this one single overall formula.
9. Type a comma followed by the second formula that is run if the condition is false. Close the formula with an ending parenthesis. The formula may read '=if(a1>0,a1*10,a1*12)'. The two formulas that are included in this statement are 'a1*10' and 'a1*12' based on the outcome of the test, 'a1>0'.

Read more ►

How to Remove the Blue Titlebar From UserForm in Excel 2003

1. Launch Microsoft Excel 2003. Press the “Alt” and “F11” keys on your computer keyboard at the same time to launch the Microsoft Visual Basic Editor application inside Excel.
2. Click “Insert” in the main navigation menu. Select “UserForm” from the drop-down menu.
3. Double-click the “UserForm1” window to open the code window. Highlight and delete any code in the window.
4. Paste the following code into the window:Option ExplicitPrivate Sub UserForm_Initialize()Call RemoveCaption(Me)End Sub
5. Click “Insert” in the main navigation menu. Select “Module” from the drop-down menu.
6. Paste the following code into the Module window:Option ExplicitPrivate Declare Function FindWindow Lib 'User32' _
Alias 'FindWindowA' ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As LongPrivate Declare Function GetWindowLong Lib 'User32' _
Alias 'GetWindowLongA' ( _
ByVal hwnd As Long, _
ByVal nIndex As Long) As LongPrivate Declare Function SetWindowLong Lib 'User32' _
Alias 'SetWindowLongA' (ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As LongPrivate Declare Function DrawMenuBar Lib 'User32' ( _
ByVal hwnd As Long) As LongSub RemoveCaption(objForm As Object)Dim lStyle As Long
Dim hMenu As Long
Dim mhWndForm As LongIf Val(Application.Version)
mhWndForm = FindWindow('ThunderXFrame', objForm.Caption) 'XL97
Else
mhWndForm = FindWindow('ThunderDFrame', objForm.Caption) 'XL2000
End If
lStyle = GetWindowLong(mhWndForm, -16)
lStyle = lStyle And Not HC00000
SetWindowLong mhWndForm, -16, lStyle
DrawMenuBar mhWndFormEnd SubSub ShowForm()UserForm1.Show FalseEnd Sub
7. Click the “x” button in the top right-hand corner of the Visual Basic Editor window to close the application and all windows. You don’t need to save the contents of the code boxes.
8. Click “Tools” in the main menu. Select “Macro” followed by “Macros.” Select “ShowForm.” Click the “Run” button. The Excel user form now displays without the title bar.

Read more ►

How to Modify Microsoft Excel

Microsoft Excel 2007
1. Modify Excel 2007 by changing the Quick Access toolbar, Themes and Styles to meet individual needs. Right-click on the three default buttons (\'Save,\' \'Undo\' and \'Redo\'), and select \'Customize.\' A two-pane window will open.
2. Select commands for the Quick Access toolbar. The left pane accesses all available Excel commands; the right pane shows the existing three buttons. Highlight a command and press the \'Add\' button to move the command to the Quick Access pane. Controls are available to move commands up and down, add the separator bar creates a group and organizes commands to meet individual needs. Only one row is possible. When finished, click \'OK.\'
3. Modify the Quick Access Gallery. From the Home Tab, the Quick Access Gallery shows a number of default styles of font weight, size, color and backgrounds. Customize styles by right-clicking on any style and selecting \'Modify.\'
4. Locate and modify Themes on the Page Layout tab. Customizing Themes adds significant flexibility for spreadsheet design in terms of colors, fonts and styles. Colors, Fonts and Effects can be individually modified, then three modified features are saved as a group in a Theme for easy future access.
5. Select the \'Fonts\' button. A window appears with font selection boxes for \'Heading\' and \'Body.\' Select the desired font pair, add a name for the pair and click \'Save.\' In the future, when the Font button is clicked, this pair will show in the \'Custom\' grouping. Creating a font pair automatically changes all of the default styles in the Quick Access Gallery to the theme's body and heading fonts.
6. Modify theme colors by pressing the \'Colors\' button and choosing \'Create new Theme colors\' from the very bottom of the drop-down menu. A window will open, showing the current theme colors for different aspects of the spreadsheet. Colors can be selected from drop-down menus. Two interactive graphics will show the results of the colors selected. The color grouping can be named and saved for future use.
7. Select an effect to be the default with the new colors and fonts. Then press the \'Theme\' button and select \'Save current Theme\' at the bottom of the drop-down window. Give it a name; it will appear in the Custom grouping in the future. Changing the spreadsheet Effects is not possible as part of the Themes. Graphic features can be modified individually when used in the spreadsheet.
Excel 2010
8. Right-click on the Ribbon and select \'Customize the Ribbon.\' Here you can select commands, place them in a new or existing Group, then add them to existing or new Tabs.
9. Use the same instructions from Section 1, Step 2 to modify the Quick Access toolbar. Select commands for the toolbar to speed program usage. When you are finished with the Ribbon and Quick Access toolbar, there will be a command to \'Import\' or \'Export\' both the Ribbon customization and the Quick Access toolbar customization. \'Export\' creates a back-up copy to be placed in a folder that is somewhere other than \'\\Programs\\Microsoft\\Office.\' This ensures that the back-up does not get erased if Office needs to be reinstalled.
10. Restore a lost Ribbon or Quick Access toolbars by importing them from the Customize screen. Both the Ribbon and toolbar modifications are saved in the same export file.

Read more ►

How to Convert Inches to Millimeters in Excel 2003

1. Enter your measurement data (in inches) into Excel, and label the column so that you know what units the data are in (for example, 'Length (inches)'). Here you can assume that the first data point is in A2, and the rest follow in the same column (A3, A4, A5).
2. Label the next column so that you know that the data is expressed in millimeters (for example 'Length (mm)').
3. Enter the following formula in cell B2:=CONVERT(A2,'in','mm')This tells Excel to look up the value in cell A2, and convert it from inches to millimeters.
4. Copy and paste this formula down so that all your data are converted. Excel will automatically look up the correct cell, so the formula in B3 will be converting the value in A3, the formula in B4 will be converting the value in A4, and so on.

Read more ►

How To: Custom Name for the X Y Axis in Excel 2007

1. Open your Excel document and click on a chart to edit it.
2. Click the 'Layout' tab on the Microsoft Office Ribbon at the top of the Excel window.
3. Click the 'Axis Titles' drop-down menu on the Layout tab, then select 'Primary Horizontal Axis Title' or 'Primary Vertical Axis Title' and click on a display option. For example, click 'Horizontal Title' to display the axis title horizontally.
4. Type a name for the axis in the 'Axis Title' text box and press 'Enter.'

Read more ►

How to Drag Sum Formulas From Cell to Cell in Microsoft Excel 2003

1. Select the cell with the sum formula that you wish to drag. To select a cell that you wish to drag a sum formula from, you must first have a cell that contains a sum formula. You will need to left-click on the cell that you wish to drag the formula on, to highlight and activate that cell.
2. Activate the formula drag corner. To drag a formula you can easily do so by using the cursor to highlight the lower right corner of the cell, where a drag box will appear.
3. Drag the formula to the new cells. To drag the sum formula across the span of other cells, left-click on this drag box and hold as you drag the box to highlight new cells. When you let go of the mouse button, the formula will appear in all of the cells that you highlighted when you used the drag box.
4. Test the formula to assure that you implemented it properly. Make sure that you dragged the formula properly by double-checking the math to assure that the formula is functioning as desired.

Read more ►

How to Make Drop

1. Open Excel. Click on the Windows icon at the top of the the window and scroll down to 'Open.' Click on the command and browse through the folders on your computer to find your list. Click on it once to select it and click on 'Open.'
2. Click on a blank sheet in the workbook (use the tabs on the bottom to select a blank sheet). Type the entries you would like to see in your drop-down list in a column with no spaces in between the entries.
3. Highlight the list so it is all selected. Click in the name box (this is a text box next to the function bar) and give your list a name (for example, you can name it which column you will be using the list in).
4. Go back to your list and click in the first cell you want the drop-down list to occur. Click on the 'Data' menu at the top of the screen and click on 'Validation.' Click on the 'Settings' tab when the 'Validation' window opens.
5. Click on 'List' in the 'Allow' box. Type '=' and your list name in the 'Source' box. Click on the box next to 'In-Cell drop down.' Click on the 'Ignore Blank' box if the cell can be left blank. Once it is complete, click 'OK.'
6. Right-click the cell you have just created the list and click on 'Copy.' Highlight the entire column, right-click, and click 'Paste' to make every cell in that column into that drop-down list.

Read more ►

How to Recover a File After I Choose Not to Save the Changes

1. Avoid closing out the program after you have edited the document but want to return it back to normal. You may click 'Ctrl' and 'Z' or 'Apple' and 'Z' (when using a Mac computer) and the document moves back to a previous step. Continue pressing this until you remove all of the edits you want. You may also click 'Edit,' 'Undo' to perform the same actions.
2. Launch the software back up if you have already closed the document but did not save the changes. Click the 'File' tab and you may find all of the 'Recent' documents displayed. Choose the document you recently have been working on and the information appears since its last auto save (the computer automatically saves the document in progress, just in case you fail to before the program is closed or freezes).
3. Select 'File,' 'Open' if you never saved any changes to the document and want the original file open. A browse window appears on the computer screen. Select the file you wish to view, then choose 'Open' and the original document loads onto the computer screen, without all of the unsaved edits.

Read more ►

How to Find Data Source for Excel 2003

1. Open your file in Excel 2003. Click the 'Data' tab near the top of the Excel window.
2. Click the 'Connections' icon right below the 'Data' tab. Click the 'Properties' button on the right side of the 'Workbook Connections' form.
3. Click the 'Definition' tab on the 'Connection Properties' form. View the 'Command text' box at the bottom of the form to see the data source for the Excel file.

Read more ►