Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Sergiy asks if there is any way to have a cell comment or a validation input box reflect a value from a formula in a cell. I couldn't find a good solution, other than using VBA. Episode 855 shows you how.
posted by Bill at 9:00 AM
Shawn sends in a great tip for today's podcast. Access has a Filter by Selection icon. Using a few lines of VBA code, you can add this functionality to your personal macro workbook. Episode 839 shows you how.
Labels: Filter by Selection, VBA
posted by Bill at 9:00 AM
So, you upgrade to Excel 2007 and your macros stop working. If you are incredibly lucky, it might be a situation where the macro would work, but you need to enable macros. Episode 828 shows where Microsoft hid Macro Security options and what the new words actually mean.
posted by Bill at 9:00 AM
Mark from New Hampshire notes that the macro recorder can not record the simple act of pressing the AutoSum button. In Episode 823, I show you the arcane workaround to solve the problem.
posted by Bill at 9:00 AM
After all of these episodes about using the macro recorder, Jonathan tried to record a macro to rename worksheets. The code fails sometimes and works other times. We will take a look at that code and understand how to generalize it to have it always work in Episode 814.
Labels: VBA
posted by Bill at 9:00 AM
|
0 comments
Ever wonder how MrExcel spends his free time? He watches the macro recorder write code before his eyes. In Episode 813, learn how to arrange the VBA window and the Excel window to watch which lines of code appear in response to actions in Excel.
posted by Bill at 9:00 AM
Today, a closer look at the macro from Episode 811. While it LOOKED like it worked, the recorded macro leaves a lot to be desired. Your totals will be wrong if you rely on the AutoSum button during macro recording. Episode 812 shows you the workaround.
Labels: VBA
posted by Bill at 9:00 AM
Part 1 of 2: Many times, you have to use a clever mix of relative and absolute recording to get the macro to perform certain tasks. The goal today is to navigate to the bottom of a data set, add totals, and then move back to row 1. Episode 811 will show you how to handle the relative button, but watch out, as episode 812 will reveal yet another problem.
Labels: VBA
posted by Bill at 9:00 AM
While yesterday's macro showed a simple formatting macro, it had a limitation that you can not move to a new cell. That is a fairly severe limitation. In Episode 810, we take a look at why moving the cell pointer causes a macro to fail and the simple setting to allow the macros to work.
Labels: VBA
posted by Bill at 9:00 AM
Record a simple macro in Excel 2007. In Episode 809, we take a look at a simple macro that the macro recorder can reliably record. The trick is that you never move the cell pointer during the recording of the macro.
Labels: VBA
posted by Bill at 9:00 AM
In our second Where is it Wednesday episode, we take a look at how to unlock all of the macro functionality in Excel 2007. Episode 808 shows you how.
posted by Bill at 9:00 AM
Pat needs to find duplicate addresses. However, the addresses are typed differently, data is in different columns, it is a real mess.
Labels: VBA
posted by Bill at 9:00 AM
Go to a cell in Excel and enter July 2008 as 07/08. Unfortunately, Excel will convert this to July 8 of the current year. In Episode 798, a bit of VBA code to convert that entry back into a month and year.
Labels: VBA
posted by Bill at 9:00 AM
Episode 322 talked about adding pop-up pictures to a cell. Devin asks if there is a way to add many pictures at once. In Episode 785, I use some macro code from page 764 in the book to quickly add pop-up pictures to many cells.
posted by Bill at 9:00 AM
Do you have the world's greatest Personal.xls and want to share it with others in your company? Watch Episode 780 for the steps necessary to distribute the personal macro workbook.
Labels: Personal Macro Workbook, VBA
posted by Bill at 9:00 AM
Column I is sparsely filled in with product codes. You would like a macro to copy only the non-blank cells to a new column. It turns out that the macro recorder can actually reliably perform this task. Episode 779 will show you how.
Labels: VBA
posted by Bill at 9:00 AM
Pau asks how to take a CSV file from one website and convert it for importing to Google Calendar. In Episode 773, I turn on the macro recorder and attempt to fix the file.
posted by Bill at 9:00 AM
Telling red cells from green cells in Excel is difficult for those suffering from color blindness. In Episode 771, I take a look at a macro that will show the text name of the color in the status bar for any selected cell.
Labels: VBA
posted by Bill at 9:00 AM
Rene from Norway asks how to modify the code from podcast 730 in order to create a new workbook for every department. Episode 754 shows you how.
posted by Bill at 9:00 AM
If you want the macro from Episode 744 to always be available, you can save it as an add-in and install the add-in. Episode 745 will show you how.
posted by Bill at 9:00 AM
If you want to randomly choose from a list and never have duplicates, you can use the method discussed in this podcast. Episode 734 shows you how.
posted by Bill at 9:00 AM
Hamilton asks how to generate a column of bingo numbers, one at a time. In Episode 733, a tiny macro will add random numbers down column A.
posted by Bill at 9:00 AM
J.B. needs to create a new worksheet for every customer listed on the summary worksheet. He asks if there is an Insert Worksheets command. While there isn't, you can create one with a few lines of VBA code. Episode 730 shows you how.
Labels: VBA
posted by Bill at 9:00 AM
Back in Episode 695, I used the InputBox function in VBA to ask for a couple of answers. In today's podcast, I will show you how to convert that macro to use a custom user form in Excel. Episode 701 shows you how.
posted by Bill at 9:00 AM
In today's podcast, a five line macro that will allow you to scale a selected range. This macro was inspired by engineers in one of my recent VBA seminars. They had been using a similar tool for years and we were surprised it was a five line macro to replicate the tool. Episode 695 shows you how.
Labels: Macro, Scale Numbers, VBA
posted by Bill at 9:00 AM
Microsoft hides some of the VBA controls in Excel 2007. Episode 687 will show you how to access the Developer tab in the Excel 2007 ribbon.
Labels: Excel 2007, VBA
posted by Bill at 9:00 AM
Marshall from Pennsylvania watched Episode 680 from last Friday and came back with a cool use for the same technology: scoring runners in a 5K or 10K race. In Episode 685, we take a look at a simple system in Excel to track the finishing times as you type in bib numbers.
Labels: event handlers, time calculations, VBA
posted by Bill at 9:00 AM
Florian wants to time stamp a record every time someone types a new name in column A, but the NOW function is not working. In Episode 680, I will show you a few lines of VBA code to solve this problem.
Labels: Event Handler, NOW, timestamp, VBA, Worksheet_Change
posted by Bill at 9:00 AM
Paul uses the macro recorder to record the action of naming the current region. The macro recorder never gets this correct. There is a far simpler version of code to create named ranges. Episode 676 will show you how to replace the recorded code.
Labels: Named Ranges, VBA
posted by Bill at 9:00 AM
