Learn Excel from Bill Jelen with this daily 2 minute video podcast.

Wednesday, November 25, 2009

DoubleClick Fill Macro - 1152 - Learn Excel from MrExcel Video Podcast

Harold from Tulsa asked how to write a macro to simulate double-clicking the fill handle. Episode 1152 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Monday, November 23, 2009

Word DataBars - 1150 - Learn Excel from MrExcel Video Podcast

In Episode 1150, I wanted to add data bars to a series of words. Episode 1150 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , ,

Friday, November 20, 2009

Duel: Eval Text Formulas - 1149 - Learn Excel from MrExcel Video Podcast

Someone put a bunch of mathematical calculations in column A as text and we want to evaluate those formulas. Mike and Bill provide dueling ways to solve this problem in Episode 1149.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

Monday, November 09, 2009

Avoid a Loop - 1140 - Learn Excel from MrExcel Podcast

A cool way to streamline a VBA loop using SpecialCells. Episode 1140 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Friday, October 09, 2009

Duel: Rejoin WordWrapped Cells - 1119 - Learn Excel MrExcel Podcast

External data pasted into Excel shows up with 1 row in columns A & C, but multiple rows in B. Today's dueling Excel Episode 1119 shows how to deal with this data.


This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Thursday, October 08, 2009

No Save Until Complete - 1118 - Learn Excel from MrExcel Podcast

Roy asks how to prevent a workbook from being saved until the workbook is complete. Episode 1118 will show you the four lines of VBA needed to make this happen.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Thursday, September 24, 2009

VBA Popup Pictures - 1108 - Learn Excel from MrExcel Podcast

John asks if there is a way to automate the process from Episode 322 of adding pop-up pictures to a cell. Today's Episode 1108 takes a look at the VBA code to add pop-up pictures to many cells.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Monday, September 07, 2009

VBA Sheet Name - 1096 - Learn Excel from MrExcel Podcast

Your recorded macros might not work if someone renames a worksheet. Rather than use the worksheet name, use the code name for the worksheet. This name can never be changed and is more reliable. Episode 1096 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Thursday, August 06, 2009

Quarters or Weeks - 1074 - Learn Excel from MrExcel Podcast

LearnAccessByCrystal sends in a cool tip that will allow you to format dates to show quarters, weeks, and more. Episode 1074 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Tuesday, August 04, 2009

Create Worksheets - 1072 - Learn Excel from MrExcel

Uma wants to create many worksheets in a new workbook that are named after values in range B2:B20. Episode 1072 shows a VBA macro that makes all of this possible.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Monday, August 03, 2009

Move Right Macro - 1071 - Learn Excel from MrExcel Podcast

Terry from Springfield, MO asks about a quicker, one-click way to change the Move Selection After Enter direction. Episode 1071 show you how to solve this problem by recording a couple of quick macros.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Tuesday, June 30, 2009

Rounding Correctly - 1047 - Learn Excel from MrExcel Podcast

After yesterday’s podcast about ASTM E29 rounding, I produce a function in VBA that will correctly do the bankers rounding algorithm in Excel. Episode 1047 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Friday, June 26, 2009

Dueling: Percentage Scrollbar - 1045 - Learn Excel from MrExcel Podcast

Izhak asks how to add a scrollbar to a worksheet for selecting a percentage. In this dueling Excel podcast Episode 1045, Bill and Mike will show you two ways to solve the problem.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Wednesday, June 10, 2009

Fiscal Quarter UDF - 1033 - Learn Excel from MrExcel Podcast

If your fiscal year does not end on December 31, then all of the cool date grouping functions available in pivot tables will not work for you. In Episode 1033, you will see how to add a couple of user-defined functions to your personal macro workbook to simplify the conversion of date to Fiscal Quarter or Fiscal Year.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Wednesday, June 03, 2009

Generalize Recorded Macro - 1028 - Learn Excel from MrExcel Podcast

While yesterday's podcast was created with the macro recorder, you can switch over to the VBA editor to change 4 lines of the macro. This will create a macro that will work without requiring you to rename the pivot table. Episode 1028 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Tuesday, June 02, 2009

Format Pivot Macro - 1027 - Learn Excel from MrExcel Podcast

Cheryl asks how to format all new pivot tables that she creates. The macro recorder can handle this, if you change the name of the pivot table before recording the macro. Episode 1027 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Wednesday, May 20, 2009

Elapsed Days - 1018 - Learn Excel from MrExcel Podcast

Figure out the number of billable days between two dates. Episode 1018 looks at ways to count the number of days, number of workdays, or number of Monday-Wednesday-Friday dates between two dates.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Tuesday, May 19, 2009

Exploding Dates Macro - 1017 - Learn Excel from MrExcel Podcast

Shawn sends in today's question. The spreadsheet has billing data to audit. For each record, there is a location, a start date, and an end date. Shawn wants to find any records where the same date and location is double-billed. A quick two-loop macro solves this problem. Episode 1017 shows you how.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Wednesday, March 18, 2009

MrExcel's Learn Excel #973 - Move Rows Macro

A question from YouTube asks how to write a macro to cut selected rows to Sheet2, adding a completion date. Episode 973 shows you how to write this short macro.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Wednesday, February 25, 2009

MrExcel's Learn Excel #958 - Use Excel without the Mouse Day

Dave from San Francisco suggests you should use Excel without the mouse and challenges you to convert a formula to values in three keystrokes. Episode 958 shows various attempts to solve the problem without using the mouse.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Tuesday, February 24, 2009

MrExcel's Learn Excel #957 - 954 Redux

Episode 954 brought an avalanche of mail on three fronts. (1) SendKeys is really cool, how do you do Alt instead of Ctrl? (2) Why the macro when you could select Entire Workbook in the dialog? (3) Application.Dialogs would have worked, the dialog is misnamed. Episode 957 discusses all of these tips.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , ,

Thursday, February 19, 2009

MrExcel's Learn Excel #954 - Macro Display Dialog

Laura asks how to display the Find dialog in a macro. The macro recorder won't do it. Application.Dialogs().Show won't do it. Episode 954 shows you a kludge to solve this problem.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Thursday, February 05, 2009

MrExcel's Learn Excel #944 - Excel to Word

In Episode 943, I took data from Word and pasted to Excel for sorting. Now, I need to "type" that data back into Word, using the proper Style for each paragraph. Episode 944 shows you an Excel macro to automate this process.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Wednesday, February 04, 2009

MrExcel's Learn Excel #943 - Sorting Word Paragraphs

Bill (me) asks... is there any way to sort paragraphs in Word? I still don't know the answer, but in Episode 943, I show you how to take the data to Excel, add a few new columns and successfully sort groups of records in Excel.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Monday, January 26, 2009

MrExcel's Learn Excel #936 - Paste the Total

Pablo sends in a great question: why can't you copy a range of cells and paste only the total of those cells in a new cell? Microsoft should really add this functionality, but in the meantime, 2 lines of macro code solve the problem. Episode 936 shows you how.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Monday, December 08, 2008

Episode 904 - Deleting Names

Mike from OK City asks how to delete all names in a workbook. In Episode 904, I will show you a way to delete all names or to delete only the invalid names.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Monday, November 17, 2008

Episode 891 - AC DC Video

You've probably seen the AC/DC video running in an Excel spreadsheet. The author did some cool tricks to deliver the WAV file to your hard drive. In Episode 891, I'll take a look at the code to extract an object from a workbook.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Wednesday, October 22, 2008

Episode 873 - First Macro

Rick asks how to get started with VBA. In Episode 873, I show a horribly formatted file that someone in my Power Excel audience has to deal with daily. A macro seems like a good way to deal with the file. I will show you how to turn on the macro recorder, turn on relative reference, and record your first macro.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Monday, October 20, 2008

Episode 871 - Finding Dates

Someone tried to record a macro to find the records from a certain date. Since finding dates requires the date format to match exactly, a better way to go would be using Advanced Filter. In Episode 871, I show how to use AdvancedFilter in a macro to extract records from a certain date.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Friday, September 26, 2008

Episode 855 - Dynamic Comment?

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Thursday, September 04, 2008

Episode 839 - Filter by Selection

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.

Here is the code:
Sub Filter_by_Active_Cell()
Dim ColNum As Integer
ColNum = ActiveCell.Column - _
(ActiveCell.CurrentRegion.Column - 1)
Selection.AutoFilter Field:=ColNum, Criteria1:=ActiveCell
End Sub

Sub AutoFilterToggle()
Selection.AutoFilter
End Sub

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Wednesday, August 20, 2008

Episode 828 - 2007 Macros

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Wednesday, August 13, 2008

Episode 823 - Recording AutoSum

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Thursday, July 31, 2008

Episode 814 - Renaming Sheets

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Wednesday, July 30, 2008

Episode 813 - Code Genie

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Tuesday, July 29, 2008

Episode 812 - Recording AutoSum Don't!

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Monday, July 28, 2008

Episode 811 - Mixed Recording

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.


This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Friday, July 25, 2008

Episode 810 - Recording Moving

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Thursday, July 24, 2008

Episode 809 - Format Macro

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Wednesday, July 23, 2008

Episode 808 - Where are macros?

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Monday, July 21, 2008

Episode 806 - Fuzzy Match

Pat needs to find duplicate addresses. However, the addresses are typed differently, data is in different columns, it is a real mess.

In Episode 806, we will take a look at using FuzzyMatch functions from the MrExcel Message Board to solve this problem.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Wednesday, July 09, 2008

Episode 798 - Typing Months

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Friday, June 20, 2008

Episode 785 - Macro Pictures

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Friday, June 13, 2008

Episode 780 - Distribute Personal.xls

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Thursday, June 12, 2008

Episode 779 - Copy NonBlanks Macro

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Wednesday, June 04, 2008

Episode 773 - Google Calendar

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , ,

Monday, June 02, 2008

Episode 771 - Color Blind

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Thursday, May 08, 2008

Episode 754 - Create Workbooks

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Friday, April 25, 2008

Episode 745 - Create an AddIn

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Thursday, April 10, 2008

Episode 734 - Bingo No Duplicates

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Wednesday, April 09, 2008

Episode 733 - Bingo Draw

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Friday, April 04, 2008

Episode 730 - Insert Worksheets

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

Monday, February 25, 2008

Episode 701 - VBA Userforms

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Friday, February 15, 2008

Episode 695 - Scale Selection

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Tuesday, February 05, 2008

Episode 687 - VBA in 2007

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Friday, February 01, 2008

Episode 685 - Tracking Runners

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

Friday, January 25, 2008

Episode 680 - Timestamp

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

Monday, January 21, 2008

Episode 676 - Recording Naming

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

Friday, September 14, 2007

Episode 593 - Unprotect Green Cells

Today, the questioner asks how to unprotect the green cells. There isn’t any good way to do this in the Excel user interface, so in Episode 593, I use a tiny bit of VBA code to achieve the effect.

The code used in the podcast is:

Sub UnProtectGreen
For each Cell in Selection
If Cell.Interior.ColorIndex = 4 then
Cell.Locked = False
Else
Cell.Locked = True
End If
Next Cell
End Sub
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels:

Thursday, September 06, 2007

Episode 587 - Concatenate Range in VBA

Back in Episode 578, I used an incredibly complex method using NotePad for joining cells together. Today, Jonathan from the UK points out that a few lines of VBA code would have made the solution incredibly simple. Episode 587 shows you how.


Function ConcatenateRange(rCells As Range)
Dim vTemp As Variant
Application.Volatile

For Each vTemp In rCells
ConcatenateRange = ConcatenateRange & vTemp & " "
Next vTemp
End Function



This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels:

Friday, May 25, 2007

Episode 516 - Event Handlers

In honor of Memorial Day in the United States, a question sent in from a naval aviator who has to enter a series of time values. Using a tiny bit of VBA code, we can eliminate the need to type the colon in the middle of the time value. Even if you don’t have to enter times all day, the techniques in Episode 516 can ease the redundant data entry tasks.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: , ,

Wednesday, May 02, 2007

Episode 499 - Spelling Numbers

George sends in a question – how can you convert a number to words? For example, how can you write $1234.56 as One Thousand Two Hundred Thirty Four dollars and Fifty Six cents. Although many utility packs offer this functionality, the VBA code is actually available for free from Microsoft. Episode 499 shows you how to add the code to your workbook. Be sure to stop back tomorrow for episode #500, when we will give away a treasure trove of prizes.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels:

Thursday, April 19, 2007

Episode 490 - Sum Bold

Mudit asks, is there any way to sum just the bold cells in a range? This problem is hard to solve in Excel, but easy if you use a tiny user defined function in VBA. In Episode 490, learn how to find free user defined functions on the internet and paste them into your Excel workbook.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels:

Wednesday, March 14, 2007

Episode 464 - Gathering Entries

In offices throughout the US, people are starting to turn in their NCAA bracket sheets to the office commissioner. Whether you are asking accountants to fill out an NCAA bracket or sales reps to fill out a sales forecast, it makes sense to collect the data in an Excel workbook. In Episode 464, we take a look at a macro that can be used to gather entries from the individual worksheets and combine them into a master worksheet.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: ,

Wednesday, November 15, 2006

Episode 384 - Formatting Macro

Someone posed this question in a recent Power Excel seminar - he had to format cells with a strange format, and had to do this repeatedly. Learn how to record a simple macro to automate this task. Episode 384 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: , ,

Thursday, October 19, 2006

Episode 365 - Scroll Area

Back in August, the podcast showed a trick for preventing people from going outside a certain range of the worksheet. Today, a caller points out that this setting is lost when you close and re-open the workbook. Today's postcast shows how to create a simple one-line macro using Worksheets("Sheet1").ScrollArea = "A1:J10" to ensure that the setting always comes back. Episode 365 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: ,