Go to current date upon opening Worksheet
Macro written by Ron deBruin.......with slight modifications.
Copy/paste to a General Module.
Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With ActiveSheet.Range("A:A")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If
End With
End Sub
Place the following in the sheet module.
Private Sub Worksheet_Activate()
Call Find_Todays_Date
End Sub
BTW..........A is not a row. A is a column.
Tuesday, June 8, 2010
Go to current date upon opening Worksheet
Go to current date upon opening Worksheet
Macro written by Ron deBruin.......with slight modifications.
Copy/paste to a General Module.
Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With ActiveSheet.Range("A:A")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If
End With
End Sub
Place the following in the sheet module.
Private Sub Worksheet_Activate()
Call Find_Todays_Date
End Sub
BTW..........A is not a row. A is a column.
Macro written by Ron deBruin.......with slight modifications.
Copy/paste to a General Module.
Sub Find_Todays_Date()
Dim FindString As Date
Dim rng As Range
FindString = Date
With ActiveSheet.Range("A:A")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Application.Goto rng, True
Else
MsgBox "Nothing found"
End If
End With
End Sub
Place the following in the sheet module.
Private Sub Worksheet_Activate()
Call Find_Todays_Date
End Sub
BTW..........A is not a row. A is a column.
Protect and Hide Your Formulas
Protect and Hide Your Formulas
When a cell contains a formula, the formula is visible for all to see. Just activate the cell and glance up at the Formula bar. In some cases, you many want to hide your formulas to give your worksheet a cleaner look--or to keep others from seeing how your calculations are done.
You can hide the formulas by setting the Hide option for the formula cells and protecting the sheet. But before you protect the sheet, you need to "unlock" all non-formula cells (by default, all cells are locked).
Here's how to hide the formula cells:
1. Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box.
2. Click the Special button to show the Go To Special dialog box.
3. Choose the Formulas option, and make sure the four check boxes are all checked.
4. Click OK, and Excel selects all cells that contain a formula.
5. Choose Format, Cells, and click the Protection tab.
6. Place a check mark next to the Hidden option, and make sure that there is a check mark next to the Locked option. Click OK.
Here's how to unlock the non-formula cells:
1. Repeat Steps 1 and 2 above to show the Go To Special dialog box.
2. Choose the Constants option, and make sure the four check boxes are all checked.
3. Click OK, and Excel selects all non-empty cells that don't contain a formula.
4. Choose Format, Cells, and click the Protection tab.
5. Remove the check mark next to the Locked option. Click OK.
At this point, the formula cells are set to Hide, and the non-formula cells are set to Unlock. But these settings have no effect unless the sheet is protected. Choose Tools, Protection, Protect Sheet to protect the worksheet. You can enter a password if you like.
After performing these steps, you can select any formula cell, and the formula will not be displayed in the Formula bar. Furthermore, the formula cannot be changed. But the cells that contain other information can be changed.
It's important to understand that protecting a worksheet--even if you use a password--is a very weak form of security. Password-cracking utilities are widespread on the Internet.
When a cell contains a formula, the formula is visible for all to see. Just activate the cell and glance up at the Formula bar. In some cases, you many want to hide your formulas to give your worksheet a cleaner look--or to keep others from seeing how your calculations are done.
You can hide the formulas by setting the Hide option for the formula cells and protecting the sheet. But before you protect the sheet, you need to "unlock" all non-formula cells (by default, all cells are locked).
Here's how to hide the formula cells:
1. Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box.
2. Click the Special button to show the Go To Special dialog box.
3. Choose the Formulas option, and make sure the four check boxes are all checked.
4. Click OK, and Excel selects all cells that contain a formula.
5. Choose Format, Cells, and click the Protection tab.
6. Place a check mark next to the Hidden option, and make sure that there is a check mark next to the Locked option. Click OK.
Here's how to unlock the non-formula cells:
1. Repeat Steps 1 and 2 above to show the Go To Special dialog box.
2. Choose the Constants option, and make sure the four check boxes are all checked.
3. Click OK, and Excel selects all non-empty cells that don't contain a formula.
4. Choose Format, Cells, and click the Protection tab.
5. Remove the check mark next to the Locked option. Click OK.
At this point, the formula cells are set to Hide, and the non-formula cells are set to Unlock. But these settings have no effect unless the sheet is protected. Choose Tools, Protection, Protect Sheet to protect the worksheet. You can enter a password if you like.
After performing these steps, you can select any formula cell, and the formula will not be displayed in the Formula bar. Furthermore, the formula cannot be changed. But the cells that contain other information can be changed.
It's important to understand that protecting a worksheet--even if you use a password--is a very weak form of security. Password-cracking utilities are widespread on the Internet.
Zap the Numbers, Keep the Formulas
Zap the Numbers, Keep the Formulas
One common type of spreadsheet is a fill-in-the-blanks template. Typically, such a sheet will have a number of input cells, and a number of formula cells. You enter your data in the input cells, and the formula cells kick into action and use those values to display some type of (usually) useful result.
For example, you may have a worksheet set up to calculate a loan amortization table. Input cells would include items such as loan amount, interest rate, loan term, and so on. Formulas in other cells use this information to display the calculated results.
At some point, you may want to clear the slate and delete all the values in the input cells. Rather than scroll around and look for the non-formula cells, you can take advantage of an often-overlooked Excel feature that lets you select cells in a "special" way:
1. Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box.
2. Click the Special button to show the Go To Special dialog box.
3. Choose the Constants option, then clear all of the check boxes except Numbers (if some of the input cells in the spreadsheet accept text, leave the check mark next to Text).
4. Click OK, and Excel will select all of the non-formula cells that contain a value.
5. Press the Delete key, and those numbers are history--but the formulas remain intact.
One common type of spreadsheet is a fill-in-the-blanks template. Typically, such a sheet will have a number of input cells, and a number of formula cells. You enter your data in the input cells, and the formula cells kick into action and use those values to display some type of (usually) useful result.
For example, you may have a worksheet set up to calculate a loan amortization table. Input cells would include items such as loan amount, interest rate, loan term, and so on. Formulas in other cells use this information to display the calculated results.
At some point, you may want to clear the slate and delete all the values in the input cells. Rather than scroll around and look for the non-formula cells, you can take advantage of an often-overlooked Excel feature that lets you select cells in a "special" way:
1. Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box.
2. Click the Special button to show the Go To Special dialog box.
3. Choose the Constants option, then clear all of the check boxes except Numbers (if some of the input cells in the spreadsheet accept text, leave the check mark next to Text).
4. Click OK, and Excel will select all of the non-formula cells that contain a value.
5. Press the Delete key, and those numbers are history--but the formulas remain intact.
What's the Current Date or Time?
What's the Current Date or Time?
You may be familiar with Excel's NOW function, which plugs the current date and time into your spreadsheet. Just enter =NOW() into any cell, and Excel displays the date and time formatted according to the regional options you've set for Windows. If you just want to see the date, use the TODAY function, =TODAY(), instead.
But if you want to use either of these functions as a type of "timestamp" you'll find that they don't do the job, because they're automatically updated whenever your worksheet is calculated or re-opened. The cell that displays today's date will display tomorrow's date when you open the spreadsheet tomorrow.
To insert a fixed date into a cell--a date that you don't want to change--hold down Ctrl while you press the semicolon ( ;) key. To insert the current time (without a date), press Ctrl-Shift-Semicolon.
These commands are useful if you like to document your work. For example, you might want to keep track of when you added new data to your worksheet.
You may be familiar with Excel's NOW function, which plugs the current date and time into your spreadsheet. Just enter =NOW() into any cell, and Excel displays the date and time formatted according to the regional options you've set for Windows. If you just want to see the date, use the TODAY function, =TODAY(), instead.
But if you want to use either of these functions as a type of "timestamp" you'll find that they don't do the job, because they're automatically updated whenever your worksheet is calculated or re-opened. The cell that displays today's date will display tomorrow's date when you open the spreadsheet tomorrow.
To insert a fixed date into a cell--a date that you don't want to change--hold down Ctrl while you press the semicolon ( ;) key. To insert the current time (without a date), press Ctrl-Shift-Semicolon.
These commands are useful if you like to document your work. For example, you might want to keep track of when you added new data to your worksheet.
Quick Math Without Formulas
Quick Math Without Formulas
If you want to do math with Excel, your only choice is to write formulas, right? Wrong! Get familiar with Excel's Paste Function dialog box, and learn how to perform a variety of basic math operations without a single formula. Consider this scenario: Your company's product price list stored in a worksheet. Your boss informs you that, effective immediately, all prices must be increased by 5 percent.
You could create some formulas to do the math, and then convert the formulas to values, and then copy and paste the new prices over the old prices. Or, you could use this more efficient method:
1. Enter 1.05 into any blank cell. This is the "multiplier" that will increase the prices by 5 percent.
2. Select the cell you used in Step 1, and choose Edit, Copy or press Ctrl-C.
3. Select the range of values to be changed, and choose Edit, Paste Special to display the Paste Special dialog box.
4. Choose the Multiply option and click OK.
Voilà! The values are changed in an instant. You can then delete the cell that contains the 1.05 multiplier.
Note that the Paste Special dialog box lets you carry out other mathematical operations--like add and subtract, for instance--so you can use this technique for a variety of other calculations.
If you want to do math with Excel, your only choice is to write formulas, right? Wrong! Get familiar with Excel's Paste Function dialog box, and learn how to perform a variety of basic math operations without a single formula. Consider this scenario: Your company's product price list stored in a worksheet. Your boss informs you that, effective immediately, all prices must be increased by 5 percent.
You could create some formulas to do the math, and then convert the formulas to values, and then copy and paste the new prices over the old prices. Or, you could use this more efficient method:
1. Enter 1.05 into any blank cell. This is the "multiplier" that will increase the prices by 5 percent.
2. Select the cell you used in Step 1, and choose Edit, Copy or press Ctrl-C.
3. Select the range of values to be changed, and choose Edit, Paste Special to display the Paste Special dialog box.
4. Choose the Multiply option and click OK.
Voilà! The values are changed in an instant. You can then delete the cell that contains the 1.05 multiplier.
Note that the Paste Special dialog box lets you carry out other mathematical operations--like add and subtract, for instance--so you can use this technique for a variety of other calculations.
Shade Alternate Rows
Shade Alternate Rows
Looking for a way to simulate that nerdy green-banded computer paper on screen? Look no further. Excel's Conditional Formatting feature has many uses, and here's one that's particularly handy. Conditional Formatting makes it simple to apply cell shading (green or otherwise) to every other row in a worksheet range. For a lengthy list, shading alternate rows can improve legibility.
Here's how to do it:
1. Highlight the range of cells or rows or columns that you want to format.
2. Choose Format, Conditional Formatting to display the Conditional Formatting dialog box.
3. Select Formula Is from the first drop-down list box, and enter =MOD(ROW(),2)=0 in the second box.
4. Click the Format button to bring up the Format Cells dialog box.
5. Select the Patterns tab and specify a color for the shaded rows. You'll probably want to choose a light color, so that the default black text will still be legible. Or, you can go all out and change the text color as well (do this in the Font tab of the Format Cells dialog box). For example, you might select a dark blue background, accompanied by a mellow yellow text color.
6. Click OK twice to return to your worksheet.
The best part is that the row shading is dynamic: The alternate row shading persists even if you insert or delete rows within the original range. By the way, if get tired of this new look and want to get things back to normal, just select the range, choose Format, Conditional Formatting, and click the Delete button in the Conditional Formatting dialog box.
Looking for a way to simulate that nerdy green-banded computer paper on screen? Look no further. Excel's Conditional Formatting feature has many uses, and here's one that's particularly handy. Conditional Formatting makes it simple to apply cell shading (green or otherwise) to every other row in a worksheet range. For a lengthy list, shading alternate rows can improve legibility.
Here's how to do it:
1. Highlight the range of cells or rows or columns that you want to format.
2. Choose Format, Conditional Formatting to display the Conditional Formatting dialog box.
3. Select Formula Is from the first drop-down list box, and enter =MOD(ROW(),2)=0 in the second box.
4. Click the Format button to bring up the Format Cells dialog box.
5. Select the Patterns tab and specify a color for the shaded rows. You'll probably want to choose a light color, so that the default black text will still be legible. Or, you can go all out and change the text color as well (do this in the Font tab of the Format Cells dialog box). For example, you might select a dark blue background, accompanied by a mellow yellow text color.
6. Click OK twice to return to your worksheet.
The best part is that the row shading is dynamic: The alternate row shading persists even if you insert or delete rows within the original range. By the way, if get tired of this new look and want to get things back to normal, just select the range, choose Format, Conditional Formatting, and click the Delete button in the Conditional Formatting dialog box.
Add a Drop-Down List
Add a Drop-Down List
Inserting a drop-down list to a cell is a slick trick that can add a touch of professionalism to your worksheets--and also ensure that erroneous data is not entered into the cell. And best of all: Macros are not required!
Assume that you have an input cell in which the user is supposed to enter a month name: January, February, and so on. Here's how to add a drop-down list to that cell to make data entry a breeze--and save some keystrokes.
1. Enter the items for your drop-down list into a list on the worksheet, one item per cell. In this example, I'll assume that the month names start in cell E1 and extend down to E12, but they can be in any out-of-the-way location on the worksheet. In Excel terminology, a rectangular group of cells (such as E1 to E12) is called a range.
2. Select the cell that will contain the drop-down list. If you'd like more than one cell to display the same list, just select them all now rather than setting them up one at a time. (Click and drag to select a range; hold down Ctrl while you click to select non-adjacent cells.)
3. Choose Data, Validation to display the Data Validation dialog box.
4. Click the Settings tab.
5. In the Allow field, select List.
6. In the Source field, specify the range that contains the list items. In this example, the items are in cells E1 to E12, so type =E1:E12 into the field.
7. Click OK.
After performing these steps, you'll see a drop-down arrow whenever any of the drop-down cells you just defined is "active" (that is, selected and awaiting input). Click the arrow and choose a month from the list. If you try to type something else into the cell, you'll get scolded in the form of a pop-up message.
If you'd like to provide your own wording for invalid entries, use the Error Alert tab in the Data Validation dialog box, and enter your own text in the 'Error message' field.
If your list of items is relatively small, you can bypass Step 1 and enter the list items directly into the Source field in Step 6. Just separate each list item with a comma.
Inserting a drop-down list to a cell is a slick trick that can add a touch of professionalism to your worksheets--and also ensure that erroneous data is not entered into the cell. And best of all: Macros are not required!
Assume that you have an input cell in which the user is supposed to enter a month name: January, February, and so on. Here's how to add a drop-down list to that cell to make data entry a breeze--and save some keystrokes.
1. Enter the items for your drop-down list into a list on the worksheet, one item per cell. In this example, I'll assume that the month names start in cell E1 and extend down to E12, but they can be in any out-of-the-way location on the worksheet. In Excel terminology, a rectangular group of cells (such as E1 to E12) is called a range.
2. Select the cell that will contain the drop-down list. If you'd like more than one cell to display the same list, just select them all now rather than setting them up one at a time. (Click and drag to select a range; hold down Ctrl while you click to select non-adjacent cells.)
3. Choose Data, Validation to display the Data Validation dialog box.
4. Click the Settings tab.
5. In the Allow field, select List.
6. In the Source field, specify the range that contains the list items. In this example, the items are in cells E1 to E12, so type =E1:E12 into the field.
7. Click OK.
After performing these steps, you'll see a drop-down arrow whenever any of the drop-down cells you just defined is "active" (that is, selected and awaiting input). Click the arrow and choose a month from the list. If you try to type something else into the cell, you'll get scolded in the form of a pop-up message.
If you'd like to provide your own wording for invalid entries, use the Error Alert tab in the Data Validation dialog box, and enter your own text in the 'Error message' field.
If your list of items is relatively small, you can bypass Step 1 and enter the list items directly into the Source field in Step 6. Just separate each list item with a comma.
Auto start Macro to go to today's date in worksheet
Try this macroIf you have date's in column A then this example will select the
Ron de Bruin posted on Monday, January 14, 2008 2:19 PM
Try this macro
If you have date's in column A then this example will select the cell with today's date.
Sub Find_Todays_Date()
Dim FindString As Date
Dim Rng As Range
FindString = CLng(Date)
With Sheets("Sheet1").Range("A:A")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End With
End Sub
Ron de Bruin posted on Monday, January 14, 2008 2:19 PM
Try this macro
If you have date's in column A then this example will select the cell with today's date.
Sub Find_Todays_Date()
Dim FindString As Date
Dim Rng As Range
FindString = CLng(Date)
With Sheets("Sheet1").Range("A:A")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End With
End Sub