|
|
To show instructions to users in your Excel files, you can add comments or text boxes with notes.
Another option is to put the notes in an Excel Userform, and add a Help button on each worksheet. The button can open the Help form to a specific page, and show the relevant Help information.
Download the Sample File
You can download the sample file from the Contextures website: http://www.contextures.com/excelfiles.html In the UserForms section, look for UF0014 - MultiPage Excel UserForm
Watch the Video
To see the steps for creating the UserForm and Help buttons, you can watch this Excel video tutorial.
Or watch the video on YouTube: Show Specific Info in Excel UserForm
__________
Continue reading Show Specific Info in Tabbed Excel UserForm
Someone emailed me for help with an Excel AutoFilter last week. He wanted to type the criteria onto a worksheet, and have the filtered results shown automatically. There are some built-in options for filtering by text, and keep reading to see a worksheet version that Roger Govier designed.
AutoFilter Search in Excel 2010
There is a new feature in Excel 2010 that provides easy searching, though not on the worksheet. You can see an example here, for the Excel 2010 AutoFilter search feature.
AutoFilter Search in Earlier Versions
In earlier versions of Excel, you can filter for text, but it's a bit more work. In Excel 2007 you can use a text filter, which opens the Custom AutoFilter dialog box
In Excel 2003, use the Custom option on the AutoFilter drop down.
Roger Govier's FastFilter
If you'd like to enter the AutoFilter criteria on
Continue reading Excel AutoFilter By Typing Criteria
Have you ever written an Excel proficiency exam? Maybe you'll have some advice or tips for the person who wrote to me this week, asking for help with the Excel Expert 2007 exam. He's having trouble with the macros and custom functions that will be part of the test.
It's been a long time since I wrote the Excel Expert exam, that was part of the old Microsoft Office User Specialist series. The exam has probably changed many times since then, but back then it was a mixture of multiple choice questions and simulated workbooks (if I'm remembering correctly!)
Anyway, I passed, and the certificate is still proudly displayed on my office wall. Well, it's pinned to the wall, behind the door, but it's still in good shape! Wow, June 1999 – that was a long time ago.
The Excel Expert Test
The Microsoft website has a list
Continue reading Preparing for an Excel Expert Exam
Someone emailed me this week, about a problem he was having with my sample Part Data Entry UserForm.
When I took a look at the workbook, everything seemed okay, and the code had been copied and altered correctly.
Then I noticed that there was a formatted Excel table on the data collection sheet, which wasn't in my original file. That can cause problems if you're using Excel VBA to add data to the first blank row on the worksheet.
Change the Last Row Code
In the comments for my Find First Blank Row blog post, Rick Rothstein suggested this code revision:
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
Rick mentioned that this formula ignores cells with formulas that are displaying the empty string. If your situation is such that you need to identify formula cells that might be displaying the empty string, then change the xlValues argument
Continue reading Excel UserForm Data Entry Update
If you have similar data on two or more worksheets, you might want to combine that data in a pivot table, to show the summarized results. Unfortunately, the pivot table from data on multiple sheets can be a disappointment.
Create a Pivot Table with Programming
A couple of years ago, Excel MVP, Kirill Lapin (KL), shared a sample file that he created, with amendments by Hector Miguel Orozco Diaz. It uses code to automatically create a pivot table from multiple sheets in a workbook.
You can read the details here: Create a Pivot Table from Multiple Sheets.
Revised Solution
Kirill's sample file was created as a conceptual prototype, and targeted advanced VBA users. The code has minimal error handling and compatibility checks.
However, the sample file was extremely popular, and Excel users at all skill levels wanted to adopt this solution in their own applications. To make things easier,
Continue reading Excel Pivot Table from Multiple Sheets Update
Uh-oh! It's almost Christmas and you haven't mailed any greeting cards yet. Don't worry, you can create a personalized Excel message box instead! That will warm your co-workers' hearts, and it saves paper and postage costs too.
Get the Application UserName
In Excel VBA, you can use Application.UserName to get the registration name for the Microsoft Office applications.
However, some people don't update that setting, and it might show a company name, instead of the user's name.
Get the Network UserName
Another option is to get the network user name, with a Windows API call. There is sample code in the Microsoft Knowledgebase: Visual Basic Procedure to Get Current User Name, and I've used a variation on that code in my sample file.
You can find other API code examples on the VB.Net site, and API code on The Access Web, to pull more information, such as
Continue reading Show Personalized Excel Message Box
If other people will be using the Excel files that you build, it might help them if you add some instructions in a Text Box. After you add the text, drag the handles to adjust the Text Box size, so all the instructions are visible.
If you plan to edit the text occasionally, or change the text formatting, you can set the Text Box to resize automatically.
Right-click on the Text Box, and click Format Shape, Then, click the Text Box category, and add a check mark to Resize Shape to Fit Text.
In Excel 2007, the Text Box loses its Wrap Text setting, if you turn on the auto resize. Instead of a narrow text box, you'll often end up with a really wide one.
Tip: Add a few manual line breaks, if you want the Text Box to be narrower.
Excel 2010 Auto Resize With
Continue reading Auto Resize Excel Text Boxes
Do you ever right-click on something in Excel, and the command that you wanted to use isn't on that pop-up menu? For example, if right-click on a cell, there is no command to turn off the gridlines.
If you can't find the command on the right-click menu, you have to go to the Excel Ribbon or Quick Access Toolbar (QAT) instead, and try to find the command on one of the tabs there.
What commands would you add to a right-click menu? Are there commands that you added to the QAT, that would be even better in a popup menu?
Customize the Right-Click Menus
Fortunately, Doug Glancy has created a solution to the right-click menu problem, with his MenuRighter Add-in. With Doug's free add-in, you can add commands to the pop-up menus, so the items that you need are easy to find.
The add-in is not for sissies! You
Continue reading Customize Excel Right-Click Menus
One of the best features of Excel is that it's quick and easy to sort columns of data. You can even sort data in an Excel row, left to right, by changing one of the sort options. Sort Multiple Rows of Data With a Formula In a comment on the Sort a Row in Excel 2010 blog post, Debbie asked about sorting 2000 rows, left to right. She didn't say they were lottery numbers, but her example, shown below, sure looks like that to me. One way to sort the rows is to use a formula, in columns to the right. In the screen shot below, cells H1:M1 are selected, and this SMALL formula is entered: =SMALL(A1:F1,{1,2,3,4,5,6}) Then, to array-enter the formula, press Ctrl+Shift+Enter Then, copy the formula down to the last row of numbers, to see all the rows in ascending
Continue reading Sort Lottery Number Rows in Excel
Last week I ran into an interesting problem, when trying to fix a macro button in a client's Excel file. I've created a simplified version of the workbook and button, to show you what happened. The workbook had been converted from Excel 2003 to Excel 2010, and when the macro button was clicked, it showed an error message. Cannot run the macro...The macro may not be available in this workbook or all macros may be disabled. Other buttons in the workbook were working fine, so the macros were enabled – that wasn't the problem. Assign a Macro to the Button I tried to reconnect the button to the macro, but when I tried, another error message appeared. Reference must be to a macro sheet. Well, I haven't used a macro sheet for about 10 years, so that was a bit confusing! Didn't we get rid of macro
Continue reading Excel Macro Name Conflicts
|
|
Recent Comments