Camera: Takes a linked picture of the current selection and pastes it in a new location. The picture is linked by a formula that refers to the copied cells, so it is updated when the copied cells change. Click the Camera button to take the picture, and then click where you want to paste the upper-left corner of the selection.
The Camera Tool in Excel is a very useful tool when it comes to capturing data in Excel that you want to display in another area in Excel or another application. Using copy/paste doesn't always give you the results you want so try the camera tool.
First, add the camera tool to a toolbar in Excel :
1. Choose View...Toolbars, Customize
2. Click on the Commands tab
3. Select Tools from the Categories list and then scroll down the commands list until you located the Camera tool
4. Click and drag the camera onto any toolbar
5. Click on close
Using the Camera Tool
To make a linked Camera picture of a range:
* Select a range
* Press Camera Icon, a + cursor appears
* Move to destination location and click once - the selected range is copied
* Move, manipulate your live picture as desired
Since your picture is linked, any changes in the original cells will appear in your picture.
You can move it anywhere you want, it floats over the rows/columns.
FOR Excel 2007
Click office Button-->Excel Options -->Customize-->Commands not in ribbon--> Camera
Regards,
Praveen KVC
31 December 2009
Thursday, December 31, 2009
Wednesday, December 30, 2009
How to Recover a Corrupt Excel File
Microsoft has some great tips for recovering data from Excel files that won't open or are otherwise corrupt although they are sometimes a little difficult to try. See the tips section for them. You may want to try them first, or a combination with the list below.
Steps
1.Close down Excel, reopen it and try to open the file again.
2.If this doesn't work, restart the computer, open up Excel and try to open the file again.
3.If this doesn't work, delete the contents of your c:\windows\temp directory then reboot. Try again.
4.If this doesn't work, open Excel in Safe Mode. This will disable VBA and Add-ins. Click on Start then Run then enter (including the quotes and the /s extension): "C:\Program Files\Microsoft Office\Office\excel.exe" /s ["C:\Program Files\Microsoft Office\Office10\excel.exe" /s (for Excel 2002, Office XP, click cancel if the MS Office Installer starts, Excel in safe mode will still start)]. Try to open the file.
5.If this doesn't work, be sure to scan the file for viruses. Make sure your macro heuristics scanning option is turned on. Ask your IT administrator how to do this.
6.If you don't find a virus, find the file in Explorer, right click on it and rename it, replacing the .xls extension with the .doc extension. Try to open it as Word document.
7.If this doesn't work, try to determine if the file is unrecoverable on the disk. Open the file in Explorer and try to copy it to another location. If you can copy the file to another location skip to step 9, if not the actual sectors on the disk may be corrupted. If the data is valuable enough to you, there are several labs that can recover files off damaged disks, you can find a lab here: http://www.disasterrecoverygroup.com/। There are other such services too)। Damaged hard disk file recovery is not for the faint at heart! If you would like to try there are a bunch of free tools starting here:http://www।s2services.com/baddisk.htm. There are several programs you can buy, one for as little as 40$. A good list is here:http://www.webattack.com/shareware/system/swdatarecovery.shtml. The File Recovery program at the top of the list appears to be the best. For recovery from a bad floppy disk download or use a freeware program to recover the file from the floppy. Some freeware for this purpose can be found here:http://www.simtel.net/pub/pd/60018.html and here: http://www.s2services.com/cdzipandfloppyreapir.htm.
8.Try to open any damaged disk recovered file immediately, miracles do happen.
9.If the recovered file won't open or you could copy the file to another location, try to open the file in a more recent version of Excel. As the version numbers increase, their ability to recover corrupt files increases. If this doesn't work or such a version of Excel is not available, see if some other spreadsheet application is and try to open the file in that program.
10.A hopefully exhaustive list of Office Suites is available from Google at: http://directory.google.com/Top/Computers/Software/Office_Suites/. but here is one too: Microsoft Works Spreadsheet, Lotus 123, Quattro Pro, Star Office Calc, ThinkFree Office Calc, Ability Office Spreadsheet, Gobe Productive Spreadsheet, EI Office's Spreadsheet Module, Xoom Office Calc, Open Office Calc (part of the freeware Open Office Suite - similar to Star Office, free business use!) - available at: http://www.openoffice.org/dev_docs/source/1.0.1/index.html, 602 Tab (part of the freeware 602Pro PC Suite 2001, also free for business use!), available at: http://www.software602.com/products/pcs/download.html, Easy Spreadsheet (part of the freeware Easy Office 2001 - $39.95 for business use), available at: http://www.e-press.com/demo_downloads.html, standalone spreadsheets: http://directory.google.com/Top/Computers/Software/Spreadsheets.
11.If Microsoft can't help you (see the tips section) and you've exhausted all the free methods above for damaged disk recovered or copiable files there are a number of excellent commercial applications which will recover your files almost immediately. Try the free demos first (see "External Links" below).
12.If you are adventurous, go into Explorer, locate the file again, and rename it with a .txt extension instead of an .xls extension. Try opening the file, and if Windows says it can't open it Notepad but will open it in WordPad, agree. Be sure to turn on word wrapping in either program, in Notepad it's under the Format menu; in WordPad it's under the View menu, choose Options, choose the Text Tab, and choose Wrap to window. Next look for where your data stops, and is followed by a bunch of spacer characters they are little squares. These actually begin to tell Excel where cells, columns and rows are. When you find your last bit of data, delete the rest of the file. After this rename it back to an .xls extension, try opening the file in the oldest version of a spreadsheet you can find, or one of the freeware spreadsheets programs mentioned in Step 9. You may be able to recover your data in some semblance of a spreadsheet.
Regards,
Praveen KVC
December 30 2009
Steps
1.Close down Excel, reopen it and try to open the file again.
2.If this doesn't work, restart the computer, open up Excel and try to open the file again.
3.If this doesn't work, delete the contents of your c:\windows\temp directory then reboot. Try again.
4.If this doesn't work, open Excel in Safe Mode. This will disable VBA and Add-ins. Click on Start then Run then enter (including the quotes and the /s extension): "C:\Program Files\Microsoft Office\Office\excel.exe" /s ["C:\Program Files\Microsoft Office\Office10\excel.exe" /s (for Excel 2002, Office XP, click cancel if the MS Office Installer starts, Excel in safe mode will still start)]. Try to open the file.
5.If this doesn't work, be sure to scan the file for viruses. Make sure your macro heuristics scanning option is turned on. Ask your IT administrator how to do this.
6.If you don't find a virus, find the file in Explorer, right click on it and rename it, replacing the .xls extension with the .doc extension. Try to open it as Word document.
7.If this doesn't work, try to determine if the file is unrecoverable on the disk. Open the file in Explorer and try to copy it to another location. If you can copy the file to another location skip to step 9, if not the actual sectors on the disk may be corrupted. If the data is valuable enough to you, there are several labs that can recover files off damaged disks, you can find a lab here: http://www.disasterrecoverygroup.com/। There are other such services too)। Damaged hard disk file recovery is not for the faint at heart! If you would like to try there are a bunch of free tools starting here:http://www।s2services.com/baddisk.htm. There are several programs you can buy, one for as little as 40$. A good list is here:http://www.webattack.com/shareware/system/swdatarecovery.shtml. The File Recovery program at the top of the list appears to be the best. For recovery from a bad floppy disk download or use a freeware program to recover the file from the floppy. Some freeware for this purpose can be found here:http://www.simtel.net/pub/pd/60018.html and here: http://www.s2services.com/cdzipandfloppyreapir.htm.
8.Try to open any damaged disk recovered file immediately, miracles do happen.
9.If the recovered file won't open or you could copy the file to another location, try to open the file in a more recent version of Excel. As the version numbers increase, their ability to recover corrupt files increases. If this doesn't work or such a version of Excel is not available, see if some other spreadsheet application is and try to open the file in that program.
10.A hopefully exhaustive list of Office Suites is available from Google at: http://directory.google.com/Top/Computers/Software/Office_Suites/. but here is one too: Microsoft Works Spreadsheet, Lotus 123, Quattro Pro, Star Office Calc, ThinkFree Office Calc, Ability Office Spreadsheet, Gobe Productive Spreadsheet, EI Office's Spreadsheet Module, Xoom Office Calc, Open Office Calc (part of the freeware Open Office Suite - similar to Star Office, free business use!) - available at: http://www.openoffice.org/dev_docs/source/1.0.1/index.html, 602 Tab (part of the freeware 602Pro PC Suite 2001, also free for business use!), available at: http://www.software602.com/products/pcs/download.html, Easy Spreadsheet (part of the freeware Easy Office 2001 - $39.95 for business use), available at: http://www.e-press.com/demo_downloads.html, standalone spreadsheets: http://directory.google.com/Top/Computers/Software/Spreadsheets.
11.If Microsoft can't help you (see the tips section) and you've exhausted all the free methods above for damaged disk recovered or copiable files there are a number of excellent commercial applications which will recover your files almost immediately. Try the free demos first (see "External Links" below).
12.If you are adventurous, go into Explorer, locate the file again, and rename it with a .txt extension instead of an .xls extension. Try opening the file, and if Windows says it can't open it Notepad but will open it in WordPad, agree. Be sure to turn on word wrapping in either program, in Notepad it's under the Format menu; in WordPad it's under the View menu, choose Options, choose the Text Tab, and choose Wrap to window. Next look for where your data stops, and is followed by a bunch of spacer characters they are little squares. These actually begin to tell Excel where cells, columns and rows are. When you find your last bit of data, delete the rest of the file. After this rename it back to an .xls extension, try opening the file in the oldest version of a spreadsheet you can find, or one of the freeware spreadsheets programs mentioned in Step 9. You may be able to recover your data in some semblance of a spreadsheet.
Regards,
Praveen KVC
December 30 2009
Hide the Ribbon in Office 2007
Press Control + F1 and ribbon disappears and press again to reappear ribbon. You can also have it disappear so it will come back with a single click on any tab name. To do this, double click a tab name and the ribbon disappears. Single click a tab name and it reappears - click again on the tab or in the document, and it disappears. Repeat until you're tired of the magic! Double click or Control + F1 to go back to how it was.
Regards,
Praveen KVC
December 29 2009
Regards,
Praveen KVC
December 29 2009
Sunday, December 27, 2009
Find First or Last Populated Column in a sheet
Code:
Option Explicit
Sub Test_xlFirstLastCols()
' Target Application: MS Excel
' Demonstration: display first and last non-blank columns in the active sheet
' and one target sheet
Dim SheetName As String
'
' display sheet name and results from xlFindFirstCol and xlFindLastCol
' for the active sheet. Since activesheet is assumed if procs are called
' without a passed arguement, use that method here
'
MsgBox "Worksheet name = " & ActiveSheet.Name & vbCrLf & _
"First non-blank col = " & xlFirstCol() & vbCrLf & _
"Last non-blank col = " & xlLastCol(), vbInformation, _
"Active Sheet Demonstration"
'
' display sheet name and results from xlFindFirstCol and xlFindLastCol
' for "Sheet4". Since this is not the active sheet, the sheet must
' be defined via the passed arguement.
'
SheetName = "Sheet4"
MsgBox "Worksheet name = " & SheetName & vbCrLf & _
"First non-blank col = " & xlFirstCol(SheetName) & vbCrLf & _
"Last non-blank col = " & xlLastCol(SheetName), vbInformation, _
"Passed Sheet Name Demonstration"
End Sub
Function xlFirstCol(Optional WorksheetName As String) As Long
' finds the first populated col in a worksheet
If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlFirstCol = .Cells.Find("*", .Cells(.Cells.Count), xlFormulas, _
xlWhole, xlByColumns, xlNext).Column
If Err <> 0 Then xlFirstCol = 0
End With
End Function
Function xlLastCol(Optional WorksheetName As String) As Long
' finds the last populated col in a worksheet
If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlLastCol = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByColumns, xlPrevious).Column
If Err <> 0 Then xlLastCol = 0
End With
End Function
How to use:
Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
In the left side window, select the target spreadsheet [it will likely be called VBAProject(name.xls) where name is the name of the spreadsheet]
Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.
Test the code:
In the attached example, there are 5 sheets with varying amounts of data.
Select any of these sheets (or create another sheet)
Go to Tools Macro Macros (or Alt+F8) and double-click on Test_xlFirstLastCols
The results for the active sheet and Sheet4 will be displayed.
Each sheet has a text box with info on what you should expect from the demo. You may also click on any of these text boxes to execute the demo.
(N.B. if you select Sheet4 as the active sheet the same result will be displayed twice.
xlFirstCol and xlLastCol are functions to be called by a higher level procedure, further testing will depend on how the functions are used).
Regards,
Praveen KVC
December 28 2009
Option Explicit
Sub Test_xlFirstLastCols()
' Target Application: MS Excel
' Demonstration: display first and last non-blank columns in the active sheet
' and one target sheet
Dim SheetName As String
'
' display sheet name and results from xlFindFirstCol and xlFindLastCol
' for the active sheet. Since activesheet is assumed if procs are called
' without a passed arguement, use that method here
'
MsgBox "Worksheet name = " & ActiveSheet.Name & vbCrLf & _
"First non-blank col = " & xlFirstCol() & vbCrLf & _
"Last non-blank col = " & xlLastCol(), vbInformation, _
"Active Sheet Demonstration"
'
' display sheet name and results from xlFindFirstCol and xlFindLastCol
' for "Sheet4". Since this is not the active sheet, the sheet must
' be defined via the passed arguement.
'
SheetName = "Sheet4"
MsgBox "Worksheet name = " & SheetName & vbCrLf & _
"First non-blank col = " & xlFirstCol(SheetName) & vbCrLf & _
"Last non-blank col = " & xlLastCol(SheetName), vbInformation, _
"Passed Sheet Name Demonstration"
End Sub
Function xlFirstCol(Optional WorksheetName As String) As Long
' finds the first populated col in a worksheet
If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlFirstCol = .Cells.Find("*", .Cells(.Cells.Count), xlFormulas, _
xlWhole, xlByColumns, xlNext).Column
If Err <> 0 Then xlFirstCol = 0
End With
End Function
Function xlLastCol(Optional WorksheetName As String) As Long
' finds the last populated col in a worksheet
If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlLastCol = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByColumns, xlPrevious).Column
If Err <> 0 Then xlLastCol = 0
End With
End Function
How to use:
Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
In the left side window, select the target spreadsheet [it will likely be called VBAProject(name.xls) where name is the name of the spreadsheet]
Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.
Test the code:
In the attached example, there are 5 sheets with varying amounts of data.
Select any of these sheets (or create another sheet)
Go to Tools Macro Macros (or Alt+F8) and double-click on Test_xlFirstLastCols
The results for the active sheet and Sheet4 will be displayed.
Each sheet has a text box with info on what you should expect from the demo. You may also click on any of these text boxes to execute the demo.
(N.B. if you select Sheet4 as the active sheet the same result will be displayed twice.
xlFirstCol and xlLastCol are functions to be called by a higher level procedure, further testing will depend on how the functions are used).
Regards,
Praveen KVC
December 28 2009
Other Shortcut Keys in Excel
Key Description
ARROW KEYS Move one cell up, down, left, or right in a worksheet.
CTRL+ARROW KEY moves to the edge of the current data region (data region: A range of cells that contains data and that is bounded by empty cells or datasheet borders.) in a worksheet.
SHIFT+ARROW KEY extends the selection of cells by one cell.
CTRL+SHIFT+ARROW KEY extends the selection of cells to the last nonblank cell in the same column or row as the active cell.
LEFT ARROW or RIGHT ARROW selects the menu to the left or right when a menu is visible. When a submenu is open, these arrow keys switch between the main menu and the submenu.
DOWN ARROW or UP ARROW selects the next or previous command when a menu or submenu is open.
In a dialog box, arrow keys move between options in an open drop-down list, or between options in a group of options.
ALT+DOWN ARROW opens a selected drop-down list.
BACKSPACE Deletes one character to the left in the Formula Bar.
Also clears the content of the active cell.
DELETE Removes the cell contents (data and formulas) from selected cells without affecting cell formats or comments.
In cell editing mode, it deletes the character to the right of the insertion point.
END Moves to the cell in the lower-right corner of the window when SCROLL LOCK is turned on.
Also selects the last command on the menu when a menu or submenu is visible.
CTRL+END moves to the last cell on a worksheet, in the lowest used row of the rightmost used column.
CTRL+SHIFT+END extends the selection of cells to the last used cell on the worksheet (lower-right corner).
ENTER Completes a cell entry from the cell or the Formula Bar, and selects the cell below (by default).
In a data form, it moves to the first field in the next record.
Opens a selected menu (press F10 to activate the menu bar) or performs the action for a selected command.
In a dialog box, it performs the action for the default command button in the dialog box (the button with the bold outline, often the OK button).
ALT+ENTER starts a new line in the same cell.
CTRL+ENTER fills the selected cell range with the current entry.
SHIFT+ENTER completes a cell entry and selects the cell above.
ESC Cancels an entry in the cell or Formula Bar.
It also closes an open menu or submenu, dialog box, or message window.
HOME Moves to the beginning of a row in a worksheet.
Moves to the cell in the upper-left corner of the window when SCROLL LOCK is turned on.
Selects the first command on the menu when a menu or submenu is visible.
CTRL+HOME moves to the beginning of a worksheet.
CTRL+SHIFT+HOME extends the selection of cells to the beginning of the worksheet.
PAGE DOWN Moves one screen down in a worksheet.
ALT+PAGE DOWN moves one screen to the right in a worksheet.
CTRL+PAGE DOWN moves to the next sheet in a workbook.
CTRL+SHIFT+PAGE DOWN selects the current and next sheet in a workbook.
PAGE UP Moves one screen up in a worksheet.
ALT+PAGE UP moves one screen to the left in a worksheet.
CTRL+PAGE UP moves to the previous sheet in a workbook.
CTRL+SHIFT+PAGE UP selects the current and previous sheet in a workbook.
SPACEBAR In a dialog box, performs the action for the selected button, or selects or clears a check box.
CTRL+SPACEBAR selects an entire column in a worksheet.
SHIFT+SPACEBAR selects an entire row in a worksheet.
CTRL+SHIFT+SPACEBAR selects the entire worksheet.
If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing CTRL+SHIFT+SPACEBAR a second time selects the entire worksheet.
When an object is selected, CTRL+SHIFT+SPACEBAR selects all objects on a worksheet.
ALT+SPACEBAR displays the Control menu for the Excel window.
TAB Moves one cell to the right in a worksheet.
Moves between unlocked cells in a protected worksheet.
Moves to the next option or option group in a dialog box.
SHIFT+TAB moves to the previous cell in a worksheet or the previous option in a dialog box.
CTRL+TAB switches to the next tab in dialog box.
CTRL+SHIFT+TAB switches to the previous tab in a dialog box.
Regards,
Praveen KVC
December 27 2009
ARROW KEYS Move one cell up, down, left, or right in a worksheet.
CTRL+ARROW KEY moves to the edge of the current data region (data region: A range of cells that contains data and that is bounded by empty cells or datasheet borders.) in a worksheet.
SHIFT+ARROW KEY extends the selection of cells by one cell.
CTRL+SHIFT+ARROW KEY extends the selection of cells to the last nonblank cell in the same column or row as the active cell.
LEFT ARROW or RIGHT ARROW selects the menu to the left or right when a menu is visible. When a submenu is open, these arrow keys switch between the main menu and the submenu.
DOWN ARROW or UP ARROW selects the next or previous command when a menu or submenu is open.
In a dialog box, arrow keys move between options in an open drop-down list, or between options in a group of options.
ALT+DOWN ARROW opens a selected drop-down list.
BACKSPACE Deletes one character to the left in the Formula Bar.
Also clears the content of the active cell.
DELETE Removes the cell contents (data and formulas) from selected cells without affecting cell formats or comments.
In cell editing mode, it deletes the character to the right of the insertion point.
END Moves to the cell in the lower-right corner of the window when SCROLL LOCK is turned on.
Also selects the last command on the menu when a menu or submenu is visible.
CTRL+END moves to the last cell on a worksheet, in the lowest used row of the rightmost used column.
CTRL+SHIFT+END extends the selection of cells to the last used cell on the worksheet (lower-right corner).
ENTER Completes a cell entry from the cell or the Formula Bar, and selects the cell below (by default).
In a data form, it moves to the first field in the next record.
Opens a selected menu (press F10 to activate the menu bar) or performs the action for a selected command.
In a dialog box, it performs the action for the default command button in the dialog box (the button with the bold outline, often the OK button).
ALT+ENTER starts a new line in the same cell.
CTRL+ENTER fills the selected cell range with the current entry.
SHIFT+ENTER completes a cell entry and selects the cell above.
ESC Cancels an entry in the cell or Formula Bar.
It also closes an open menu or submenu, dialog box, or message window.
HOME Moves to the beginning of a row in a worksheet.
Moves to the cell in the upper-left corner of the window when SCROLL LOCK is turned on.
Selects the first command on the menu when a menu or submenu is visible.
CTRL+HOME moves to the beginning of a worksheet.
CTRL+SHIFT+HOME extends the selection of cells to the beginning of the worksheet.
PAGE DOWN Moves one screen down in a worksheet.
ALT+PAGE DOWN moves one screen to the right in a worksheet.
CTRL+PAGE DOWN moves to the next sheet in a workbook.
CTRL+SHIFT+PAGE DOWN selects the current and next sheet in a workbook.
PAGE UP Moves one screen up in a worksheet.
ALT+PAGE UP moves one screen to the left in a worksheet.
CTRL+PAGE UP moves to the previous sheet in a workbook.
CTRL+SHIFT+PAGE UP selects the current and previous sheet in a workbook.
SPACEBAR In a dialog box, performs the action for the selected button, or selects or clears a check box.
CTRL+SPACEBAR selects an entire column in a worksheet.
SHIFT+SPACEBAR selects an entire row in a worksheet.
CTRL+SHIFT+SPACEBAR selects the entire worksheet.
If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing CTRL+SHIFT+SPACEBAR a second time selects the entire worksheet.
When an object is selected, CTRL+SHIFT+SPACEBAR selects all objects on a worksheet.
ALT+SPACEBAR displays the Control menu for the Excel window.
TAB Moves one cell to the right in a worksheet.
Moves between unlocked cells in a protected worksheet.
Moves to the next option or option group in a dialog box.
SHIFT+TAB moves to the previous cell in a worksheet or the previous option in a dialog box.
CTRL+TAB switches to the next tab in dialog box.
CTRL+SHIFT+TAB switches to the previous tab in a dialog box.
Regards,
Praveen KVC
December 27 2009
Saturday, December 26, 2009
Function key shortcuts in Excel
Key Description
F1 Displays the Help task pane.
CTRL+F1 closes and reopens the current task pane.
ALT+F1 creates a chart of the data in the current range.
ALT+SHIFT+F1 inserts a new worksheet.
F2 Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula Bar when editing in a cell is turned off.
SHIFT+F2 edits a cell comment.
F3 Pastes a defined name into a formula.
SHIFT+F3 displays the Insert Function dialog box.
F4 Repeats the last command or action, if possible.
CTRL+F4 closes the selected workbook window.
F5 Displays the Go To dialog box.
CTRL+F5 restores the window size of the selected workbook window.
F6 Switches to the next pane in a worksheet that has been split (Window menu, Split command).
SHIFT+F6 switches to the previous pane in a worksheet that has been split.
CTRL+F6 switches to the next workbook window when more than one workbook window is open.
Note When the task pane is visible, F6 and SHIFT+F6 include that pane when switching between panes.
F7 Displays the Spelling dialog box to check spelling in the active worksheet or selected range.
CTRL+F7 performs the Move command on the workbook window when it is not maximized. Use the arrow keys to move the window, and when finished press ESC.
F8 Turns extend mode on or off. In extend mode, EXT appears in the status line, and the arrow keys extend the selection.
SHIFT+F8 enables you to add a non-adjacent cell or range to a selection of cells by using the arrow keys.
CTRL+F8 performs the Size command (on the Control menu for the workbook window) when a workbook is not maximized.
ALT+F8 displays the Macro dialog box to run, edit, or delete a macro.
F9 Calculates all worksheets in all open workbooks.
F9 followed by ENTER (or followed by CTRL+SHIFT+ENTER for array formulas) calculates the selected a portion of a formula and replaces the selected portion with the calculated value.
SHIFT+F9 calculates the active worksheet.
CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.
CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.
CTRL+F9 minimizes a workbook window to an icon.
F10 Selects the menu bar or closes an open menu and submenu at the same time.
SHIFT+F10 displays the shortcut menu for a selected item.
ALT+SHIFT+F10 displays the menu or message for a smart tag. If more than one smart tag is present, it switches to the next smart tag and displays its menu or message.
CTRL+F10 maximizes or restores the selected workbook window.
F11 Creates a chart of the data in the current range.
SHIFT+F11 inserts a new worksheet.
ALT+F11 opens the Visual Basic Editor, in which you can create a macro by using Visual Basic for Applications (VBA).
ALT+SHIFT+F11 opens the Microsoft Script Editor, where you can add text, edit HTML tags, and modify any script code.
F12 Displays the Save As dialog box.
Regards,
Praveen KVC
December 26 2009
F1 Displays the Help task pane.
CTRL+F1 closes and reopens the current task pane.
ALT+F1 creates a chart of the data in the current range.
ALT+SHIFT+F1 inserts a new worksheet.
F2 Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula Bar when editing in a cell is turned off.
SHIFT+F2 edits a cell comment.
F3 Pastes a defined name into a formula.
SHIFT+F3 displays the Insert Function dialog box.
F4 Repeats the last command or action, if possible.
CTRL+F4 closes the selected workbook window.
F5 Displays the Go To dialog box.
CTRL+F5 restores the window size of the selected workbook window.
F6 Switches to the next pane in a worksheet that has been split (Window menu, Split command).
SHIFT+F6 switches to the previous pane in a worksheet that has been split.
CTRL+F6 switches to the next workbook window when more than one workbook window is open.
Note When the task pane is visible, F6 and SHIFT+F6 include that pane when switching between panes.
F7 Displays the Spelling dialog box to check spelling in the active worksheet or selected range.
CTRL+F7 performs the Move command on the workbook window when it is not maximized. Use the arrow keys to move the window, and when finished press ESC.
F8 Turns extend mode on or off. In extend mode, EXT appears in the status line, and the arrow keys extend the selection.
SHIFT+F8 enables you to add a non-adjacent cell or range to a selection of cells by using the arrow keys.
CTRL+F8 performs the Size command (on the Control menu for the workbook window) when a workbook is not maximized.
ALT+F8 displays the Macro dialog box to run, edit, or delete a macro.
F9 Calculates all worksheets in all open workbooks.
F9 followed by ENTER (or followed by CTRL+SHIFT+ENTER for array formulas) calculates the selected a portion of a formula and replaces the selected portion with the calculated value.
SHIFT+F9 calculates the active worksheet.
CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.
CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.
CTRL+F9 minimizes a workbook window to an icon.
F10 Selects the menu bar or closes an open menu and submenu at the same time.
SHIFT+F10 displays the shortcut menu for a selected item.
ALT+SHIFT+F10 displays the menu or message for a smart tag. If more than one smart tag is present, it switches to the next smart tag and displays its menu or message.
CTRL+F10 maximizes or restores the selected workbook window.
F11 Creates a chart of the data in the current range.
SHIFT+F11 inserts a new worksheet.
ALT+F11 opens the Visual Basic Editor, in which you can create a macro by using Visual Basic for Applications (VBA).
ALT+SHIFT+F11 opens the Microsoft Script Editor, where you can add text, edit HTML tags, and modify any script code.
F12 Displays the Save As dialog box.
Regards,
Praveen KVC
December 26 2009
Thursday, December 24, 2009
Find the Last used row in a particular sheet
There are several methods to accomplish this, some more exact than others, but they can all serve your needs.
One common method
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
which is not very exact, because Excel doesn't keep track of the last cell in a very adequate form.
Another method to find the last used row in a particular column is:
LastRowColA = Range("A65536").End(xlUp).Row
but this doesn't tell you FOR SURE the last used row in the entire sheet, unless you can be certain that Column A holds the data.
A couple extra methods are more reliable.
LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row
or
LastRow = ActiveSheet.UsedRange.Rows.Count
This methods can be used on any sheet, not just the active sheet.
Regards,
Praveen KVC
December 25 2009
One common method
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
which is not very exact, because Excel doesn't keep track of the last cell in a very adequate form.
Another method to find the last used row in a particular column is:
LastRowColA = Range("A65536").End(xlUp).Row
but this doesn't tell you FOR SURE the last used row in the entire sheet, unless you can be certain that Column A holds the data.
A couple extra methods are more reliable.
LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row
or
LastRow = ActiveSheet.UsedRange.Rows.Count
This methods can be used on any sheet, not just the active sheet.
Regards,
Praveen KVC
December 25 2009
Wednesday, December 23, 2009
CTRL combination shortcut keys in Excel
Key Description
CTRL+( Unhides any hidden rows within the selection.
CTRL+) Unhides any hidden columns within the selection.
CTRL+& Applies the outline border to the selected cells.
CTRL+_ Removes the outline border from the selected cells.
CTRL+~ Applies the General number format.
CTRL+$ Applies the Currency format with two decimal places (negative numbers in parentheses).
CTRL+% Applies the Percentage format with no decimal places.
CTRL+^ Applies the Exponential number format with two decimal places.
CTRL+# Applies the Date format with the day, month, and year.
CTRL+@ Applies the Time format with the hour and minute, and AM or PM.
CTRL+! Applies the Number format with two decimal places, thousands separator, and minus sign (-) for negative values.
CTRL+- Displays the Delete dialog box to delete the selected cells.
CTRL+* Selects the current region around the active cell (the data area enclosed by blank rows and blank columns).
In a PivotTable, it selects the entire PivotTable report.
CTRL+: Enters the current time.
CTRL+; Enters the current date.
CTRL+` Alternates between displaying cell values and displaying formulas in the worksheet.
CTRL+' Copies a formula from the cell above the active cell into the cell or the Formula Bar.
CTRL+" Copies the value from the cell above the active cell into the cell or the Formula Bar.
CTRL++ Displays the Insert dialog box to insert blank cells.
CTRL+1 Displays the Format Cells dialog box.
CTRL+2 Applies or removes bold formatting.
CTRL+3 Applies or removes italic formatting.
CTRL+4 Applies or removes underlining.
CTRL+5 Applies or removes strikethrough.
CTRL+6 Alternates between hiding objects, displaying objects, and displaying placeholders for objects.
CTRL+7 Displays or hides the Standard toolbar.
CTRL+8 Displays or hides the outline symbols.
CTRL+9 Hides the selected rows.
CTRL+0 Hides the selected columns.
CTRL+A Selects the entire worksheet.
If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
When the insertion point is to the right of a function name in a formula, displays the Function Arguments dialog box.
CTRL+SHIFT+A inserts the argument names and parentheses when the insertion point is to the right of a function name in a formula.
CTRL+B Applies or removes bold formatting.
CTRL+C Copies the selected cells.
CTRL+C followed by another CTRL+C displays the Microsoft Office Clipboard.
CTRL+D Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.
CTRL+F Displays the Find dialog box.
SHIFT+F5 also displays this dialog box, while SHIFT+F4 repeats the last Find action.
CTRL+G Displays the Go To dialog box.
F5 also displays this dialog box.
CTRL+H Displays the Find and Replace dialog box.
CTRL+I Applies or removes italic formatting.
CTRL+K Displays the Insert Hyperlink dialog box for new hyperlinks or the Edit Hyperlink dialog box for selected existing hyperlinks.
CTRL+L Displays the Create List dialog box.
CTRL+N Creates a new, blank file.
CTRL+O Displays the Open dialog box to open or find a file.
CTRL+SHIFT+O selects all cells that contain comments.
CTRL+P Displays the Print dialog box.
CTRL+R Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.
CTRL+S Saves the active file with its current file name, location, and file format.
CTRL+U Applies or removes underlining.
CTRL+V Inserts the contents of the Clipboard at the insertion point and replaces any selection. Available only after you cut or copied an object, text, or cell contents.
CTRL+W Closes the selected workbook window.
CTRL+X Cuts the selected cells.
CTRL+Y Repeats the last command or action, if possible.
CTRL+Z Uses the Undo command to reverse the last command or to delete the last entry you typed.
CTRL+SHIFT+Z uses the Undo or Redo command to reverse or restore the last automatic correction when AutoCorrect Smart Tags are displayed.
Regards,
Praveen KVC
December 24 2009
CTRL+( Unhides any hidden rows within the selection.
CTRL+) Unhides any hidden columns within the selection.
CTRL+& Applies the outline border to the selected cells.
CTRL+_ Removes the outline border from the selected cells.
CTRL+~ Applies the General number format.
CTRL+$ Applies the Currency format with two decimal places (negative numbers in parentheses).
CTRL+% Applies the Percentage format with no decimal places.
CTRL+^ Applies the Exponential number format with two decimal places.
CTRL+# Applies the Date format with the day, month, and year.
CTRL+@ Applies the Time format with the hour and minute, and AM or PM.
CTRL+! Applies the Number format with two decimal places, thousands separator, and minus sign (-) for negative values.
CTRL+- Displays the Delete dialog box to delete the selected cells.
CTRL+* Selects the current region around the active cell (the data area enclosed by blank rows and blank columns).
In a PivotTable, it selects the entire PivotTable report.
CTRL+: Enters the current time.
CTRL+; Enters the current date.
CTRL+` Alternates between displaying cell values and displaying formulas in the worksheet.
CTRL+' Copies a formula from the cell above the active cell into the cell or the Formula Bar.
CTRL+" Copies the value from the cell above the active cell into the cell or the Formula Bar.
CTRL++ Displays the Insert dialog box to insert blank cells.
CTRL+1 Displays the Format Cells dialog box.
CTRL+2 Applies or removes bold formatting.
CTRL+3 Applies or removes italic formatting.
CTRL+4 Applies or removes underlining.
CTRL+5 Applies or removes strikethrough.
CTRL+6 Alternates between hiding objects, displaying objects, and displaying placeholders for objects.
CTRL+7 Displays or hides the Standard toolbar.
CTRL+8 Displays or hides the outline symbols.
CTRL+9 Hides the selected rows.
CTRL+0 Hides the selected columns.
CTRL+A Selects the entire worksheet.
If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.
When the insertion point is to the right of a function name in a formula, displays the Function Arguments dialog box.
CTRL+SHIFT+A inserts the argument names and parentheses when the insertion point is to the right of a function name in a formula.
CTRL+B Applies or removes bold formatting.
CTRL+C Copies the selected cells.
CTRL+C followed by another CTRL+C displays the Microsoft Office Clipboard.
CTRL+D Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.
CTRL+F Displays the Find dialog box.
SHIFT+F5 also displays this dialog box, while SHIFT+F4 repeats the last Find action.
CTRL+G Displays the Go To dialog box.
F5 also displays this dialog box.
CTRL+H Displays the Find and Replace dialog box.
CTRL+I Applies or removes italic formatting.
CTRL+K Displays the Insert Hyperlink dialog box for new hyperlinks or the Edit Hyperlink dialog box for selected existing hyperlinks.
CTRL+L Displays the Create List dialog box.
CTRL+N Creates a new, blank file.
CTRL+O Displays the Open dialog box to open or find a file.
CTRL+SHIFT+O selects all cells that contain comments.
CTRL+P Displays the Print dialog box.
CTRL+R Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.
CTRL+S Saves the active file with its current file name, location, and file format.
CTRL+U Applies or removes underlining.
CTRL+V Inserts the contents of the Clipboard at the insertion point and replaces any selection. Available only after you cut or copied an object, text, or cell contents.
CTRL+W Closes the selected workbook window.
CTRL+X Cuts the selected cells.
CTRL+Y Repeats the last command or action, if possible.
CTRL+Z Uses the Undo command to reverse the last command or to delete the last entry you typed.
CTRL+SHIFT+Z uses the Undo or Redo command to reverse or restore the last automatic correction when AutoCorrect Smart Tags are displayed.
Regards,
Praveen KVC
December 24 2009
Tuesday, December 22, 2009
convert a numeric value into English words in Excel
# Start Microsoft Excel.
# Press ALT+F11 to start the Visual Basic Editor.
# On the Insert menu, click Module.
# Type the following code into the module sheet.
Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Dollars
Case ""
Dollars = "No Dollars"
Case "One"
Dollars = "One Dollar"
Case Else
Dollars = Dollars & " Dollars"
End Select
Select Case Cents
Case ""
Cents = " and No Cents"
Case "One"
Cents = " and One Cent"
Case Else
Cents = " and " & Cents & " Cents"
End Select
SpellNumber = Dollars & Cents
End Function
' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function
' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function
' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function
Regards,
Praveen KVC
December 23 2009
# Press ALT+F11 to start the Visual Basic Editor.
# On the Insert menu, click Module.
# Type the following code into the module sheet.
Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
' String representation of amount.
MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")
' Convert cents and set MyNumber to dollar amount.
If DecimalPlace > 0 Then
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Dollars
Case ""
Dollars = "No Dollars"
Case "One"
Dollars = "One Dollar"
Case Else
Dollars = Dollars & " Dollars"
End Select
Select Case Cents
Case ""
Cents = " and No Cents"
Case "One"
Cents = " and One Cent"
Case Else
Cents = " and " & Cents & " Cents"
End Select
SpellNumber = Dollars & Cents
End Function
' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If
' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function
' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function
' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function
Regards,
Praveen KVC
December 23 2009
Monday, December 21, 2009
Hide Cell Contents in Excel
Start Microsoft Excel and open an existing workbook that contains cells you would like to hide the contents of, or start a new workbook and enter some content into a cell that you would like to hide.
Select the cells that contain data you would like to hide. You can select cells by clicking on them and using the SHIFT or CTRL keys on your keyboard to select additional cells.
Choose the "Format" menu and click "Cells" to open the "Format Cells" dialog box.
Click on the "Number" tab in the "Format Cells" dialog box to display the formats that can be applied to Excel cells.
Select "Custom" from the "Category" text box so you can type your own custom formatting to hide your selected cells.
Type 3 semicolons (;;;) in the "Type" text box to tell Excel that you want to hide the cells.
Click the "OK" button to close the "Format Cells" dialog box and hide the selected cells.
Regards,
Praveen KVC
December 22 2009
Sunday, December 20, 2009
Lock and Protect Cells Containing Formulas
This is taken from the Excel Hacks by Orielly. Very useful one to protect only few cells in a worksheet.
When we create a spreadsheet, most of us need to use formulas of some sort. Sometimes, however, you might not want other users to tamper/delete/overtype any formulas you included on your spreadsheet. The easiest and most common way of barring people from playing with your formulas is to protect your worksheet. However, protecting your worksheet doesn't just prevent users from tampering with your formulas, it also stops users from entering anything at all. Sometimes you do not want to go this far.
By default, all cells on a worksheet are locked; however, this has no effect unless worksheet protection has been applied. Here is a very easy way to apply worksheet protection so that only formula cells are locked and protected.
Select all cells on your worksheet, either by pressing Ctrl/&command;-A or by clicking the gray square at the intersecting point of column A and row 1. Then select Format → Cells → Protection and uncheck the Locked checkbox to remove the tick. Click OK.
Now select any single cell, select Edit → Go To... (Ctrl-G or F5), and click Special. You'll see a dialog box such as that in .
Select Formulas from the Go To Special dialog and, if needed, limit the formulas to the subtypes underneath. Click OK. With only the formula cells selected, select Format → Cells → Protection and check the Locked checkbox to insert a tick. Select OK. Now select Tools → Protection → Protect Worksheet to protect your worksheet and apply a password if required.
The preceding method certainly saves a lot of time and eliminates possible errors locating formulas so that you can protect them. Unfortunately, it can also prevent users from using certain features, such as sorting, formatting changes, aligning text, and many others you might not be concerned with, even when in an unlocked cell. You can overcome this problem in two ways.
The first approach doesn't use worksheet protection at all, and uses data validation instead.
WARNING
Data validation is far from bulletproof when it comes to preventing users from entering nonvalidated data into cells. Users can still paste into a validated cell any data they want and, in doing so, remove the validation from that cell unless the copied cell also contains data validation, in which case this validation would override the original validation.
To see what we mean, select any single cell, select Edit → Go To... (Ctrl-G or F5), and click Special. Now select Formulas from the Go To Special dialog and, if needed, limit the formulas to the subtypes underneath. Click OK.
With only the Formula cells selected, select the Data → Validation → Settings page tab, select Custom from the Allow: box, and in the Formula box, enter ="", as shown in . Click OK.
This method will prevent a user from accidentally overtyping into any formula cells — although, as stressed in the earlier warning, it is not a fully secure method and should be used only for accidental overtyping, etc. However, the big advantage to using this method is that all of Excel's features are still usable on the worksheet.
The last method also will enable you to use all of Excel's features, but only when you are in a cell that is not locked. To start, ensure that only the cells you want protected are locked and that all other cells are unlocked. Right-click the Sheet Name tab, select View Code from the pop-up menu, and enter the following code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then
Me.Protect Password:="Secret"
Else
Me.Unprotect Password:="Secret"
End If
End Sub
If no password is used, omit Password:="Secret". If a password is used, change the word Secret to your password. Press Alt/&command;-Q or click the X in the top righthand corner to get back to Excel and save your workbook. Now, each time you select a cell that is locked, your worksheet will automatically protect itself. The moment you select any cell that is not locked, your worksheet will unprotect itself.
WARNING
This hack doesn't work perfectly, though it usually works well enough. The keyword used in the code, Target, will refer only to the cell that is active at the time of selection. For this reason, it is important to note that if a user selects a range of cells (with the active cell being an unlocked cell), it is possible for him to delete the entire selection because the target cell is unlocked and, therefore, the worksheet automatically will unprotect itself.
Regards,
Praveen KVC
December 21 2009
When we create a spreadsheet, most of us need to use formulas of some sort. Sometimes, however, you might not want other users to tamper/delete/overtype any formulas you included on your spreadsheet. The easiest and most common way of barring people from playing with your formulas is to protect your worksheet. However, protecting your worksheet doesn't just prevent users from tampering with your formulas, it also stops users from entering anything at all. Sometimes you do not want to go this far.
By default, all cells on a worksheet are locked; however, this has no effect unless worksheet protection has been applied. Here is a very easy way to apply worksheet protection so that only formula cells are locked and protected.
Select all cells on your worksheet, either by pressing Ctrl/&command;-A or by clicking the gray square at the intersecting point of column A and row 1. Then select Format → Cells → Protection and uncheck the Locked checkbox to remove the tick. Click OK.
Now select any single cell, select Edit → Go To... (Ctrl-G or F5), and click Special. You'll see a dialog box such as that in .
Select Formulas from the Go To Special dialog and, if needed, limit the formulas to the subtypes underneath. Click OK. With only the formula cells selected, select Format → Cells → Protection and check the Locked checkbox to insert a tick. Select OK. Now select Tools → Protection → Protect Worksheet to protect your worksheet and apply a password if required.
The preceding method certainly saves a lot of time and eliminates possible errors locating formulas so that you can protect them. Unfortunately, it can also prevent users from using certain features, such as sorting, formatting changes, aligning text, and many others you might not be concerned with, even when in an unlocked cell. You can overcome this problem in two ways.
The first approach doesn't use worksheet protection at all, and uses data validation instead.
WARNING
Data validation is far from bulletproof when it comes to preventing users from entering nonvalidated data into cells. Users can still paste into a validated cell any data they want and, in doing so, remove the validation from that cell unless the copied cell also contains data validation, in which case this validation would override the original validation.
To see what we mean, select any single cell, select Edit → Go To... (Ctrl-G or F5), and click Special. Now select Formulas from the Go To Special dialog and, if needed, limit the formulas to the subtypes underneath. Click OK.
With only the Formula cells selected, select the Data → Validation → Settings page tab, select Custom from the Allow: box, and in the Formula box, enter ="", as shown in . Click OK.
This method will prevent a user from accidentally overtyping into any formula cells — although, as stressed in the earlier warning, it is not a fully secure method and should be used only for accidental overtyping, etc. However, the big advantage to using this method is that all of Excel's features are still usable on the worksheet.
The last method also will enable you to use all of Excel's features, but only when you are in a cell that is not locked. To start, ensure that only the cells you want protected are locked and that all other cells are unlocked. Right-click the Sheet Name tab, select View Code from the pop-up menu, and enter the following code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Locked = True Then
Me.Protect Password:="Secret"
Else
Me.Unprotect Password:="Secret"
End If
End Sub
If no password is used, omit Password:="Secret". If a password is used, change the word Secret to your password. Press Alt/&command;-Q or click the X in the top righthand corner to get back to Excel and save your workbook. Now, each time you select a cell that is locked, your worksheet will automatically protect itself. The moment you select any cell that is not locked, your worksheet will unprotect itself.
WARNING
This hack doesn't work perfectly, though it usually works well enough. The keyword used in the code, Target, will refer only to the cell that is active at the time of selection. For this reason, it is important to note that if a user selects a range of cells (with the active cell being an unlocked cell), it is possible for him to delete the entire selection because the target cell is unlocked and, therefore, the worksheet automatically will unprotect itself.
Regards,
Praveen KVC
December 21 2009
Password Breaker for Excel sheets
I Got this code from Sharat in a Excel Discussion Forum.
Sub Button2_Click()
' Breaks worksheet and workbook structure passwords.
' probably originator of base code algorithm modified for coverage
' of workbook structure / windows passwords and for multiple passwords
' Reveals hashed passwords NOT original passwords
Const DBLSPACE As String = vbNewLine & vbNewLine
Const AUTHORS As String = DBLSPACE & vbNewLine & _
"Adapted from Bob McCormick base code by" & _
"Norman Harker and JE McGimpsey"
Const HEADER As String = "AllInternalPasswords User Message"
Const VERSION As String = DBLSPACE & "Version 1.1.1 2003-Apr-04"
Const REPBACK As String = DBLSPACE & "Please report failure " & _
"to the microsoft.public.excel.programming newsgroup."
Const ALLCLEAR As String = DBLSPACE & "The workbook should " & _
"now be free of all password protection, so make sure you:" & _
DBLSPACE & "SAVE IT NOW!" & DBLSPACE & "and also" & _
DBLSPACE & "BACKUP!, BACKUP!!, BACKUP!!!" & _
DBLSPACE & "Also, remember that the password was " & _
"put there for a reason. Don't stuff up crucial formulas " & _
"or data." & DBLSPACE & "Access and use of some data " & _
"may be an offense. If in doubt, don't."
Const MSGNOPWORDS1 As String = "There were no passwords on " & _
"sheets, or workbook structure or windows." & AUTHORS & VERSION
Const MSGNOPWORDS2 As String = "There was no protection to " & _
"workbook structure or windows." & DBLSPACE & _
"Proceeding to unprotect sheets." & AUTHORS & VERSION
Const MSGTAKETIME As String = "After pressing OK button this " & _
"will take some time." & DBLSPACE & "Amount of time " & _
"depends on how many different passwords, the " & _
"passwords, and your computer's specification." & DBLSPACE & _
"Just be patient! Make me a coffee!" & AUTHORS & VERSION
Const MSGPWORDFOUND1 As String = "You had a Worksheet " & _
"Structure or Windows Password set." & DBLSPACE & _
"The password found was: " & DBLSPACE & "$$" & DBLSPACE & _
"Note it down for potential future use in other workbooks by" & _
"the same person who set this password." & DBLSPACE & _
"Now to check and clear other passwords." & AUTHORS & VERSION
Const MSGPWORDFOUND2 As String = "You had a Worksheet " & _
"password set." & DBLSPACE & "The password found was: " & _
DBLSPACE & "$$" & DBLSPACE & "Note it down for potential " & _
"future use in other workbooks by same person who " & _
"set this password." & DBLSPACE & "Now to check and clear " & _
"other passwords." & AUTHORS & VERSION
Const MSGONLYONE As String = "Only structure / windows " & _
"protected with the password that was just found." & _
ALLCLEAR & AUTHORS & VERSION & REPBACK
Dim w1 As Worksheet, w2 As Worksheet
Dim i As Integer, j As Integer, k As Integer, l As Integer
Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer
Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As Integer
Dim PWord1 As String
Dim ShTag As Boolean, WinTag As Boolean
Application.ScreenUpdating = False
With ActiveWorkbook
WinTag = .ProtectStructure Or .ProtectWindows
End With
ShTag = False
For Each w1 In Worksheets
ShTag = ShTag Or w1.ProtectContents
Next w1
If Not ShTag And Not WinTag Then
MsgBox MSGNOPWORDS1, vbInformation, HEADER
Exit Sub
End If
MsgBox MSGTAKETIME, vbInformation, HEADER
If Not WinTag Then
MsgBox MSGNOPWORDS2, vbInformation, HEADER
Else
On Error Resume Next
Do 'dummy do loop
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
With ActiveWorkbook
.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If .ProtectStructure = False And _
.ProtectWindows = False Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
MsgBox Application.Substitute(MSGPWORDFOUND1, _
"$$", PWord1), vbInformation, HEADER
Exit Do 'Bypass all for...nexts
End If
End With
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
If WinTag And Not ShTag Then
MsgBox MSGONLYONE, vbInformation, HEADER
Exit Sub
End If
On Error Resume Next
For Each w1 In Worksheets
'Attempt clearance with PWord1
w1.Unprotect PWord1
Next w1
On Error GoTo 0
ShTag = False
For Each w1 In Worksheets
'Checks for all clear ShTag triggered to 1 if not.
ShTag = ShTag Or w1.ProtectContents
Next w1
If ShTag Then
For Each w1 In Worksheets
With w1
If .ProtectContents Then
On Error Resume Next
Do 'Dummy do loop
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If Not .ProtectContents Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
MsgBox Application.Substitute(MSGPWORDFOUND2, _
"$$", PWord1), vbInformation, HEADER
'leverage finding Pword by trying on other sheets
For Each w2 In Worksheets
w2.Unprotect PWord1
Next w2
Exit Do 'Bypass all for...nexts
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
End With
Next w1
End If
MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADER
End Sub
Regards,
Praveen KVC
December 20 2009
Sub Button2_Click()
' Breaks worksheet and workbook structure passwords.
' probably originator of base code algorithm modified for coverage
' of workbook structure / windows passwords and for multiple passwords
' Reveals hashed passwords NOT original passwords
Const DBLSPACE As String = vbNewLine & vbNewLine
Const AUTHORS As String = DBLSPACE & vbNewLine & _
"Adapted from Bob McCormick base code by" & _
"Norman Harker and JE McGimpsey"
Const HEADER As String = "AllInternalPasswords User Message"
Const VERSION As String = DBLSPACE & "Version 1.1.1 2003-Apr-04"
Const REPBACK As String = DBLSPACE & "Please report failure " & _
"to the microsoft.public.excel.programming newsgroup."
Const ALLCLEAR As String = DBLSPACE & "The workbook should " & _
"now be free of all password protection, so make sure you:" & _
DBLSPACE & "SAVE IT NOW!" & DBLSPACE & "and also" & _
DBLSPACE & "BACKUP!, BACKUP!!, BACKUP!!!" & _
DBLSPACE & "Also, remember that the password was " & _
"put there for a reason. Don't stuff up crucial formulas " & _
"or data." & DBLSPACE & "Access and use of some data " & _
"may be an offense. If in doubt, don't."
Const MSGNOPWORDS1 As String = "There were no passwords on " & _
"sheets, or workbook structure or windows." & AUTHORS & VERSION
Const MSGNOPWORDS2 As String = "There was no protection to " & _
"workbook structure or windows." & DBLSPACE & _
"Proceeding to unprotect sheets." & AUTHORS & VERSION
Const MSGTAKETIME As String = "After pressing OK button this " & _
"will take some time." & DBLSPACE & "Amount of time " & _
"depends on how many different passwords, the " & _
"passwords, and your computer's specification." & DBLSPACE & _
"Just be patient! Make me a coffee!" & AUTHORS & VERSION
Const MSGPWORDFOUND1 As String = "You had a Worksheet " & _
"Structure or Windows Password set." & DBLSPACE & _
"The password found was: " & DBLSPACE & "$$" & DBLSPACE & _
"Note it down for potential future use in other workbooks by" & _
"the same person who set this password." & DBLSPACE & _
"Now to check and clear other passwords." & AUTHORS & VERSION
Const MSGPWORDFOUND2 As String = "You had a Worksheet " & _
"password set." & DBLSPACE & "The password found was: " & _
DBLSPACE & "$$" & DBLSPACE & "Note it down for potential " & _
"future use in other workbooks by same person who " & _
"set this password." & DBLSPACE & "Now to check and clear " & _
"other passwords." & AUTHORS & VERSION
Const MSGONLYONE As String = "Only structure / windows " & _
"protected with the password that was just found." & _
ALLCLEAR & AUTHORS & VERSION & REPBACK
Dim w1 As Worksheet, w2 As Worksheet
Dim i As Integer, j As Integer, k As Integer, l As Integer
Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer
Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As Integer
Dim PWord1 As String
Dim ShTag As Boolean, WinTag As Boolean
Application.ScreenUpdating = False
With ActiveWorkbook
WinTag = .ProtectStructure Or .ProtectWindows
End With
ShTag = False
For Each w1 In Worksheets
ShTag = ShTag Or w1.ProtectContents
Next w1
If Not ShTag And Not WinTag Then
MsgBox MSGNOPWORDS1, vbInformation, HEADER
Exit Sub
End If
MsgBox MSGTAKETIME, vbInformation, HEADER
If Not WinTag Then
MsgBox MSGNOPWORDS2, vbInformation, HEADER
Else
On Error Resume Next
Do 'dummy do loop
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
With ActiveWorkbook
.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If .ProtectStructure = False And _
.ProtectWindows = False Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
MsgBox Application.Substitute(MSGPWORDFOUND1, _
"$$", PWord1), vbInformation, HEADER
Exit Do 'Bypass all for...nexts
End If
End With
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
If WinTag And Not ShTag Then
MsgBox MSGONLYONE, vbInformation, HEADER
Exit Sub
End If
On Error Resume Next
For Each w1 In Worksheets
'Attempt clearance with PWord1
w1.Unprotect PWord1
Next w1
On Error GoTo 0
ShTag = False
For Each w1 In Worksheets
'Checks for all clear ShTag triggered to 1 if not.
ShTag = ShTag Or w1.ProtectContents
Next w1
If ShTag Then
For Each w1 In Worksheets
With w1
If .ProtectContents Then
On Error Resume Next
Do 'Dummy do loop
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If Not .ProtectContents Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
MsgBox Application.Substitute(MSGPWORDFOUND2, _
"$$", PWord1), vbInformation, HEADER
'leverage finding Pword by trying on other sheets
For Each w2 In Worksheets
w2.Unprotect PWord1
Next w2
Exit Do 'Bypass all for...nexts
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
End With
Next w1
End If
MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADER
End Sub
Regards,
Praveen KVC
December 20 2009
Subscribe to:
Posts (Atom)