|
|
By Debra Dalgleish, on May 2nd, 2013 A few years ago, Ron Coderre created his PivotPlay PLUS Add-in that you can download from my Contextures site.
This free add-in was designed for Excel 2003, and lets you view and edit the connection strings for pivot tables and query tables that are based on external data queries.
Testing in Excel 2010
Several people have asked about an update, so I installed the PivotPlay PLUS add-in in Excel 2010, to test it. Instead of showing up on the menu bar, the start button appears on the Excel Ribbon’s Add-Ins tab.
If the active worksheet has a pivot
Continue reading Counting Query Tables in Excel
By Debra Dalgleish, on April 18th, 2013 Your challenge for today is to come up with a real-life use for this nifty (do the kids still say nifty?) Excel scrolling tool.
Here’s how the tool works:
Click a button to select a group of cells that you want to work with, like this list of months. Then, click the up and down arrows, to scroll the list through the selected cells. When you’re done, click the Exit button, to “release” the scroll area.
In this example, the scrolling list is in cells D8:E19 – months and numbers. The original list had January at the top, and February
Continue reading Click to Move Excel List Items Up or Down
By Debra Dalgleish, on March 7th, 2013 In an Excel UserForm, you can add combo boxes to make data entry easier. The UserForm in this example is used to enter parts information onto a hidden inventory sheet.
Before selecting a Part ID, you have to select a Part Type. When the Part Type combo box is updated, code runs, and creates a list of parts, for the selected part type.
How It Works
In the background, an Advanced Filter runs, to create a worksheet list of parts for the selected part type.
That list is set up as a named range, and the range name
Continue reading Excel UserForm with Dependent Combo Boxes
By Debra Dalgleish, on February 21st, 2013 When you’re setting up data validation on a worksheet, you can include an Input Message, to help anyone who’s using the workbook.
You’ll have to get to the point quickly though – the message is limited to 255 characters.
There are other limitations too – you can’t control the size of the text box, and you can’t change its font size or fill colour, unless you change your Windows settings.
Show Input Message in a Text Box
As an alternative to the Input Message popup, you can show a message in a text box, at the top of the
Continue reading Show Data Validation Message in Text Box
By Debra Dalgleish, on February 12th, 2013 There is a sample file on my Contextures site, in which you can enter invoice details, then print all the new invoices by clicking a button.
I’ve updated the file, and you can now download the xlsm version, if you’re using Excel 2007 or a later version.
Store Invoice Details in Table
In the new version, the invoice data is stored in a named Excel table, and a named range – Database – is based on that table. The range is dynamic, because it is based on the named table, so it will include new rows as they are
Continue reading Print Invoices With Excel Macro
By Debra Dalgleish, on February 7th, 2013 If you’d like a quick and easy way to add dates in a worksheet, you can use this handy date picker tool, from Jim Cone. It opens to the current date, and you can scroll through months and years, by using the scrollbars at the top of the date picker form.
Just select a cell, and click the insert button, to add the date. If you hold the Shift key, and click the Insert button, it will append the date to the cell’s contents.
Create Calendars on a Worksheet
In addition to inserting the date, Jim’s date picker
Continue reading Excel Date Picker Tool
By Debra Dalgleish, on January 29th, 2013 Yes, I know that it’s a bad idea to use the SendKeys method in Excel, because strange things can happen.
However, it’s handy in a few situations, and I use SendKeys in a few of my Comments macros.
For example, in this macro to insert a blank comment, without a user name, the comment opens for editing, at the end of the macro.
Sub CommentAddOrEdit() Dim cmt As Comment Set cmt = ActiveCell.Comment If cmt Is Nothing Then ActiveCell.AddComment text:="" End If SendKeys "+{F2}" End Sub Send Keyboard Shortcuts
In that example, the SendKeys line simulates using the keyboard shortcut
Continue reading Problems With SendKeys in Excel
By Debra Dalgleish, on January 22nd, 2013 Last week, you saw how to open and edit the Ribbon code in an Excel file that has a custom tab. This week, you can see how to create a custom tab in an Excel workbook, and add buttons to run your macros.
This example is based on an Order Form workbook, and the buttons run macros to clear the data entry cells, and to view the file before printing.
You’ll see how to set the custom tab labels and icons, and change your macros so they run from a click on the Ribbon. Exciting, right?
Watch the
Continue reading Add Custom Ribbon Tab For Workbook
By Debra Dalgleish, on January 15th, 2013 Have you created any custom tabs for your Excel files? I avoided that, as long as possible, but have finally started dabbling with the Ribbon code. And, the good news is that it’s not as dark and scary as I imagined.
Here is a screen shot of a sample file that I created, as an update to my Parts Database which has a UserForm for data entry.
When the file opens, it adds a custom tab to the Ribbon, with buttons to run the workbook’s macros.
You can download the sample file, and see the written instructions here: Getting
Continue reading Custom Ribbon Tab for Excel File
By Debra Dalgleish, on December 18th, 2012 In the old days, if you opened a file that contained macros, a warning message popped up on the screen. Here is the message from Excel 2003, giving me the option to Enable Macros or Disable Macros.
Of course, you only saw that warning if you had your security level set to Medium, which allowed you to choose whether or not to run macros.
Security Bar in Excel 2007
Things changed in Excel 2007, and there was a security bar, instead of a popup warning. This caused problems, because many people didn’t notice the bar, and complained that
Continue reading Excel 2010 Runs Macros Without Enabling
|
|
Recent Comments