Author's posts
Jan 23
Three Ways to Fill Blank Cells within Excel Spreadsheets
By David H. Ringstrom, CPA
Figure 3: The Go To Special command allows you to select Blanks, while Ctrl-Enter fills multiple cells.
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
Jan 23
Homeland Security Warns of Java Vulnerability
By David H. Ringstrom, CPA
- C:\Program Files\Java\jre7\bin
- C:\Program Files (x86)\Java\jre7\bin
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
Jan 14
Using Collections in Excel to populate UserForm Controls
The following is programming code we frequently use to populate a drop-down list or listbox on an Excel UserForm with a unique list of items from a spreadsheet.
Private Sub UserForm1_Intialize() 'Creates a new collection Dim myList As New Collection 'Determines number of rows to loop through numRows = Range("A1").CurrentRegion.Rows.Count 'Optional - erases existing dropdown list from control Me.lstDropdown.Clear 'Loops through each row and adds to collection For i = 2 To numRows 'An item can only be added to a collection once, hence the on error On Error Resume Next myList.Add Cells(i, "A"), Cells(i, "A") On Error GoTo 0 Next 'Populates control with items from the collecton For i = 1 To myList.Count Me.lstDropdown.AddItem myList.Item(i) Next 'Optional - erases an existing value Me.lstDropdown.Value = "" End Sub
Jan 14
Write to CSV file
The following is programming code we frequently use when creating CSV files from Excel.
Sub WriteToCSVFile() 'Captures date/time stamp for appending to file name strTime = Replace(Format(Now(), "mm-dd-yy hh:mm"), ":", "-") 'Prompts user to specify a file name strFile = Application.GetSaveAsFilename("File Name " & strTime, "*.csv,*.csv", , "Specify File Name") 'Terminates routine if user clicks Cancel If strFile = False Then Exit Sub 'Ensures text file is closed Close #1 'Opens text file for output Open strFile For Output As #1 'Determines number of rows to write numRows = Range("A1").CurrentRegion.Rows.Count 'Determines number of colums to write numCols = Range("A1").CurrentRegion.Columns.Count 'Loops through all rows... For i = 1 To numRows '...as well as all except for last column For j = 1 To numCols - 1 'Include semi-colon to prevent Excel from adding a line-break Write #1, Cells(i, j).Value; Next 'Write last columnn for given row, omit semi-colon so Write adds a line-break. Write #1, Cells(i, numCols).Value Next 'Close text file Close #1 'Notify user MsgBox "The upload file " & strFile & " has been created.", vbInformation, "Upload file created" End Sub
Jan 09
Hide and Unhide Multiple Excel Worksheets with Ease
By David Ringstrom, CPA
- Excel 2007 and later: As shown in Figure 1, on the Home tab choose Format, Hide and Unhide, and then Hide Sheet. Or, to save a couple of steps, right-click on the worksheet tab and then choose Hide.
- Excel 2003 and earlier: Choose Format, Sheet, Hide.
- Excel 2011 (Mac): Ctrl-left-click on a worksheet tab and choose Hide from the shortcut menu.
- Click on the first worksheet tab and then hold down the Shift key as you click on the last worksheet tab within the group that you wish to hide.
- Hide the sheets as discussed above.
- Excel 2007 and later: On the Home tab, you can choose Format, Hide and Unhide, Unhide Sheet, and then unhide a single sheet. You must repeat this action for each worksheet that you wish to unhide. You can save a couple of steps by right-clicking on a visible worksheet tab and choose Unhide.
- Excel 2003 and earlier: From any worksheet tab, choose Format, Sheet, and then Unhide. Select the sheet you wish to display and then click OK. Repeat as needed for each worksheet you wish to unhide.
- Excel 2011 (Mac): Ctrl-left-click on a worksheet tab and choose Unhide from the shortcut menu. Select the sheet you wish to display and then click OK. Repeat as needed for each worksheet you wish to unhide.
- Choose Custom Views on the View tab or menu.
- Click Add, and then type a name for your custom view, such as All Sheets, and then click OK.
- Next, hide any worksheets as needed and then create a second view titled Presentation View, or a name of your choosing.
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
Jan 09
Resolve to Learn and Use 14 Excel Keyboard Shortcuts in 2014
By David Ringstrom, CPA
Continue reading on www.accountingweb.com
About the author:
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
Dec 22
Verifying Dates within Excel UserForms
The following code is a routine we frequently use in our Excel projects to verify that the user has entered a date properly within a text box in a userform. In addition, dates such as 0704 get transformed to 07/04/yy where yy is the current year. Valid date inputs include 0704, 070412, 07/04/12, or 07/04/2012. All four will be validated and transformed to 07/04/12.
Function CheckDate(ctrl As Control) With ctrl strMonth = Left(.Value, 2) strDay = Mid(.Value, 3, 2) If Len(.Value) = 4 Then strYear = Year(Now()) Else strYear = Right(.Value, 2) End If On Error Resume Next strDate = DateValue(strMonth & "/" & strDay & "/" & strYear) On Error GoTo 0 If IsDate(strDate) Then .Value = Format(strDate, "mm/dd/yy") CheckDate = False ElseIf IsDate(.Value) Then .Value = Format(.Value, "mm/dd/yy") CheckDate = False ElseIf .Value = "" Then 'Do nothing CheckDate = True Else MsgBox "You entered an invalid date!", vbExclamation, "Caution!" .SetFocus CheckDate = True End If End With End Function
Dec 17
Utilizing Excel’s COUNTIF Function to Break Ties
By David Ringstrom, CPA
- range – This is a range of cells in which we wish to look for a specified value.
- criteria – This represents the value that we're seeking.
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
Dec 10
Identifying Largest and Smallest Values in an Excel List
By David Ringstrom, CPA
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
Nov 30
Plug and Print
Click here to access Plug and Print. Plug and Print adds a dialog box to your spreadsheet that automatically lists every visible worksheet.
Print all worksheets by choosing Select All, or selectively print one or more worksheets. You can also choose whether to preview before printing.
Nov 30
First Impressions of Windows 8
By David Ringstrom, CPA
- Move your cursor to either corner on the right-hand side of your screen to display the Charms bar.
- Within the Charms bar click Settings.
- Within the Settings panel click Power.
- Within the Power section choose Sleep, Shut Down, Restart, or Update.
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
Nov 30
Microsoft Excel: Use Color to Identify Variances
By David Ringstrom, CPA
- The ABS function returns the absolute value of a number – in other words ($17,396) converts to $17,396.
- The AND function allows you to test for up to 255 conditions at once. In this case, I'm testing for the absolute value of the variance in column D being greater than $2,000, and the absolute value of the variance percentage in column E being greater than 10%. If both tests are true, then conditional formatting will be applied.
- The dollar signs before the column letters are critical when applying Conditional Formatting in this fashion since in effect we're copying and pasting the format to a second column. If you omit the dollar signs before the column references, Excel will adjust the column references, meaning that your Conditional Formatting won't return the desired result.
A previous version of this article first appeared on www.accountingweb.com .
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
Nov 30
Microsoft Outlook: Disabling the Send without a Location Prompt
By David Ringstrom, CPA
- Outlook 2010 and later: Turn on the Developer tab by choosing File, Options, and then Customize Ribbon. Click the Developer checkbox, and then click OK, as shown in Figure 2. Click the Design a Form button on the Developer tab, as shown in Figure 3.
- Outlook 2007 and earlier: Choose Tools, Forms, and then Design a Form.
- Outlook 2007 and later: Click Publish and then Publish Form, as shown in Figure 4.
- Outlook 2003 and earlier: Choose Tools, Forms, and then Publish Form.
- Right-click on your calendar (underneath “My Calendars” along the left-hand side of Outlook) and choose Properties, as shown in Figure 6.
- Select the form that you just created from the “When posting to this folder, use” drop-down list, and then click OK. The built-in Outlook forms have a prefix of IPM; any forms that you customize will not have this prefix.
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
Nov 30
Managing Protected View in Excel 2010/2013
By David H. Ringstrom, CPA
- Choose File, and then Options.
- Within the Options window, choose Trust Center, and then click the Trust Center Settings button.
- Within the Trust Center Settings, choose Protected View, and make any adjustments necessary, and then click OK twice.
- Press Ctrl+O to launch Excel's Open dialog box.
- Click once on the name of a document.
- Choose Open in Protected View from the Open button's menu, as shown in Figure 4.
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
Nov 29
Building an Amortization Table in Microsoft Excel
In this one-hour presentation for Lawline.com, David Ringstrom, CPA uses an amortization table exercise in Microsoft Excel to demonstrate numerous ways to use Microsoft Excel more effectively.