Monday, April 25, 2022

The Best FREE Template to Automatically Create Folders From Excel

One topic I've been discussing for more than ten years, and keep coming back to with improvements, is how to automatically create folders from Excel. I originally shared a simple macro to make folders back in 2011. More recently, I showed how to add hyperlinks back to the newly created folders. But I was never quite happy with these programs. So now I'm back with a better, more powerful, and easy to use template that will instantly create folders for you.

This Excel template to create folders will teach you:

  • How to use a macro to automatically create folders from Excel (duh)
  • How to change the color of a toggle button (and how to use a toggle button in Excel)
  • How to add hyperlinks to folders
  • Conditional formatting to change cell color based on combo box selection
  • How to copy existing folder names into Excel
  • How to duplicate a file structure

create folders from excel file

As I've done with all my recent templates, I try to make them look as clean as possible when the user first opens the sheet so as not to overwhelm them. I like to use yellow to clearly show the cells that require user input. Command buttons make running your macros very easy.

I envision this template being used in two primary ways: to create a set of new folders from scratch (their names manually typed in), or to quickly duplicate an existing file structure.

First, enter the location in the first yellow box where you want the new folders to be created. Next, type the folder names in the column. Or, if you want to duplicate existing folders, select the folders in explorer, hold down shift and right click, copy as Path. Use find and replace to remove the path. See in this example video:

One unique feature about this template is it allows the user to choose from one of four different options about how they want the folders to be named.

You can download this Folder Generator template for free, just enter a 0 here, an examine the VBA macro code yourself:

Quick VBA tip: To change the color of a toggle button once it is clicked (toggled on or toggled off), use this code:

Private Sub ToggleButton1_Click()

    If ToggleButton1.Value = True Then ToggleButton1.BackColor = vbGreen

    If ToggleButton1.Value = False Then ToggleButton1.BackColor = vbWhite

End Sub

Do you find this template to be useful? If so, please let me know in the comments below!

Monday, March 28, 2022

Free Meeting Scheduling Excel Template

How often do you ask friends, family, or coworkers which day is the best to get together? Whether it’s a meeting, party, trip, or some other occasion I find myself in these situations all the time. There are several apps and online calendars available to help you coordinate which day works for everyone involved. I used to use a site called Doodle all the time.  The problem with these services is they are not always free, they’re constantly changing and you have to keep relearning how to use them, you have to make an online account, and so on. So I did what I always do – I decided to make an Excel template to determine which day works best for meetings and events!

I used a lot of the same concepts and macro code from my Super Bowl Squares spreadsheet. When you first open the spreadsheet you are greeted by a simple, clean setup page. Here you’ll manually input the number of participants, meeting subject and description.

Next, you’ll enter the start and end dates of the days you want the participants to choose from. It’s very important that these two cells ONLY contain dates. To restrict a user to only being able to enter a date in a cell, go to Data > Data Validation. Under Allow select Date. Be sure to enter a custom error message so if a user makes a mistake they understand what needs to be entered.

When the user clicks Generate Schedule the Schedule sheet is unhidden. The schedule can handle up to 100 participants and up to 365 days. However, you probably won’t need all that so the macro will automatically hide all the rows and columns not needed to make it easy for the user to input their information.


A lot of good Excel tips can be gleaned from examining the event timing spreadsheet. Here's a quick summary of what can be learned by dissecting this free Excel template:
  • How to add and use Option buttons
  • How to use data validation to restrict entry in a cell to a date
  • How to use data validation to restrict entry in a cell to an email address
  • How to get the day of the week from a date
  • How to use command buttons and assign specific macros to them
  • How to send emails from Excel with hyperlinks
  • How to use a formula to show only weekends in Excel
  • How to use conditional formatting to change cell color based on cell value
  • How to hide command buttons by macro
Download the Meeting Scheduler Template here.

Watch How to Coordinate Meetings with Excel

If you want to see how this spreadsheet works and some tips like how to limit a cell where a user can only input a date then watch the video below:


Try it out and let me know if you think it’s a legitimate replacement for Doodle, Calendly or whatever meeting scheduling apps you currently use. 

Monday, February 14, 2022

Weighted Olympic Medal Count 2022

In honor of the 2022 Winter Olympic Games currently being held  Beijing, China, I decided to create a Microsoft Excel spreadsheet template for the medal count as I did for the 2020 (2021) Summer Olympic Games, 2018 Winter Olympics, 2016 Summer Olympic Games, 2014 Winter Olympics and 2012 Summer Olympics

There are two primary methods most websites appear to be ranking the 2022 medal count. Most sites rank countries by the total number of Olympic medals won. Other sites, like the International Olympic Committee (or IOC) rank countries by their gold medal count. And others rank by other factors like per capita or GDP.

Pictured below is a bar chart showing all medals won for the top countries (as of the time of this posting on 2-14-22). The bar chart is created in Excel by highlighting the data then going to Insert>Bar>Stacked Bar chart. Change the colors of the bars by right clicking on them then use the drop down menu to select the data you want to change.


You can update the chart yourself by download the Excel file here.


As of 2/14/22

 

I’ve devised my own ranking system to give each Olympic medal a weight where the silver is worth half a gold medal and a bronze is worth only a quarter of the gold. Based on this new scoring system, previous Olympic results suddenly became quite interesting. However, for the 2020 Summer Games not too much actually changes (so far, will revisit after more events are completed).




If you’ve been reading my blog for awhile, you’ll probably know I’m a roller coaster enthusiast, so you probably won’t be surprised that the Olympic event that intrigues me the most is the bobsled and luge. As they said on the broadcast of the monobob following the Super Bowl, “this track more than any other is similar to a roller coaster” as it has 4 uphill sections and a helix. It’s over 4,000 feet long, has a total elevation difference of 117 meters (383 feet), and bobsleders were hitting 75 miles per hour while sustaining 4gs up to 7 seconds at a time. That’s intense! A ride on a bobsled would scare me more than any roller coaster that’s secured to the rails, as you could legit crash! What Winter Olympic sport do you like to follow?




Download the spreadsheet and see for yourself. 

I’ve shared my Olympic Medal Count spreadsheet and listed out the Olympic medals by country. How would you weight each medal against the others? Comment below and share any of your Olympic medal rating systems!

Sunday, January 30, 2022

2022 Super Bowl Squares Spreadsheet - one size fits all

I’ve been creating Super Bowl Squares spreadsheets for over ten years now but this is just the second year of using my new and improved one-size fits all generator sheet. When you first open my free grid game template, you’re met with a simple setup screen where the user will decide how to play the game:
  • How do you want to assign names to the boxes: manually pick and choose or automatic randomized?
  • Do you want to use the same numbers of the Super Bowl score, rotate by quarter or by each half?
  • Do you want to earn points per quarter, per minute, or every time the score changes?
No matter what you choose, after you click the “Generate Squares” button the sheet automatically updates the Super Bowl boxes and the leaderboard to reflect only the version of the game you want to play. There 54 different ways to play, all contained within this one template. 

2022 super bowl squares template in excel

After the Super Bowl, you can keep using this sheet for regular season NFL games. Simply use the drop down lists in the Squares sheet to select the team from the list (broken up by AFC and NFC) and their helmets will update automatically! Or make a few modifications and use this template for other sports. The possibilities are endless!

Watch me demo the new Super Bowl board in the video below. Also be sure to subscribe to my email list and YouTube channel as I'll be showing off all the tricks and tips I used to make this spreadsheet work.


As you can see, the new sheet allows more ways to play than a fixed style but is simple and easy to setup and is automated as much as possible.

Get the Super Bowl pool template here.

Even if you’re not into football, you can still use the template to learn how to do all of these cool Excel tricks:
  • Lookup pictures based on cell values
  • Generate random numbers
  • Use the NameManager
  • Create drop down lists
  • Use index and match formulas
If you dissect the macros in the spreadsheet you’ll learn how to:
  • Hide rows and columns
  • Hide or unhide sheets
  • Generate random numbers between 0 and 9 with no duplicates
  • How to hide command buttons by VBA
  • How to change cell fill color
  • How to send an email from Excel
  • How to attach an image to an email using VBA

New Features for 2022

A new feature I added for 2022 is the ability for Outlook users to easily share the Super Bowl squares via email. Enter all the player’s emails in Column A on the Send Scoreboard sheet. Just click the button and an email will automatically be composed from Excel complete with an image of the grid. I explained how to insert images into emails from a spreadsheet in a previous article.



Another way to share, copy and paste the board into a Google doc. This sheet won’t work in Google due to the macros but once you’ve got the grid filled in and the score numbers have been randomly generated, you could share it with your colleagues by copying and pasting as an image into a Google sheet. The manager would still keep score in the template but that way everyone can see what numbers they have. Let me know if you use this feature or if it isn't useful for you.

Get the Super Bowl squares spreadsheet here (enter a 0 into the price box then input an email address):

https://gumroad.com/l/superbowlsquares

*Intended for PC/Microsoft Office/Excel. I don't think it will work in Mac Numbers or Google sheets as macros are not supported*

Let me know what you think. I’d love to hear from you. Is this version as much of an upgrade and easy to use as I think it is? I welcome any and all questions, comments, suggestions, cuss words, and compliments. Let me know using the comments below or via email. Enjoy playing Super Bowl Squares!






Thursday, January 6, 2022

How to Add an Excel Shape to Outlook Mail by VBA

Two spreadsheets I am most proud of are my Super Bowl SquaresGame Generator and my College Football Bowl Prediction Pool Manager (Bowl Pick’em Game). I’ve put a lot of time and features into these free templates. However, I still get many requests to add even more features. One of the most asked questions is “how can I share the leaderboard results when all the players aren’t connected to the same network?” I would normally respond with how I do it: “I take a screenshot of the scoreboard and manually email it to the players.” Then it dawned on me – why not automate this process to make it easy for everyone to use? Why not automatically add a picture of the Excel sheet into an email?

There are two methods I can think of off the top of my head for attaching an image into an email with a macro, and here are the pros and cons of each:

  1. The picture is saved on your PC (or needs to be saved by the macro before inserting into the email)  – but either way you have to know the location of the file.
  2. Copy and paste an image already in your spreadsheet into an email. Does not require saving the image. But you must know the shape name so the macro can find it.

For today’s tutorial, I am going to show you how to use the #2 method. 

A thread on method #1 can be found here: https://stackoverflow.com/questions/44869790/embed-picture-in-outlook-mail-body-excel-vba

You can read along or scroll down to watch the video below. Again, for this method to insert an image from a spreadsheet into an email, the image must already be created and named manually so the macro knows what image within the sheet to use.

Name the Shape or Image You Want to Copy and Paste From Excel into Email

First, I need to have a linked image in my spreadsheet that will be copied to the email. Highlight the area (the cells) that you want to have an image of, in my example the scoreboard of my Super Bowl Squares sheet. Next, I created a new sheet within my workbook where I will collect the emails of all the players. I right click, paste special, linked picture. Select the image. Under page layout go to Selection Pane. Rename the picture “Preview1” or some other descriptive name. This is what the macro will use to identify which picture to attach to the email.

I also want to allow the user the option to include a hyperlink to the Excel workbook in the email or not. To do this, I create a checkbox in Excel by going to the developer tab, insert, ActiveX controls, Check Box.

 

How to Add an Excel Shape to Outlook Mail by VBA

Attach Image to Email Excel Macro Code

Now it’s time to write the VBA macro that will automatically send an email to all the players with a picture of the latest scoreboard – all at the click of a button!

I’ve previously shared how to send an email from an Excel sheet but this is my first time attaching an image. Below is the full code with my comments explaining what is happening along the way.

Sub SendEmailUpdate()

'Optimize Macro Speed

  Application.ScreenUpdating = False

  Application.EnableEvents = False

  Application.Calculation = xlCalculationManual

 

'define the workbook, location, and name

Dim Wb1 As Workbook

Set Wb1 = ThisWorkbook

 

Dim OwnerName As String

OwnerName = Application.UserName

 

Dim FileLoc As String

FileLoc = Wb1.FullName

 

Dim WorkbookName As String

WorkbookName = Wb1.Name

 

'SendEmailTo will count the number of people who the email will be sent to

Dim SendEmailTo As Integer

SendEmailTo = 0

 

'we will store all the email addresses in one long string then insert them into the TO line of the email later

Dim ToPerson As String

ToPerson = ""

 

'loop through all players in column A of the Send Scoreboard sheet (up to 100 players max)

Dim x As Integer

For x = 2 To 101

 

    ' get the emails to fill in the TO line

    If Not IsEmpty(Wb1.Worksheets("Send Scoreboard").Range("A" & x).Value) Then

    ToPerson = Wb1.Worksheets("Send Scoreboard").Range("A" & x) & "; " & ToPerson

    SendEmailTo = SendEmailTo + 1

    Else

    'MsgBox "email is blank"

    'NoSEnd = NoSEnd + 1

    End If

   

    ' get the emails to fill in the CC line

    'If Not IsEmpty(WB3.Worksheets(1).Range(CCCol & PICRow).Value) Then

    'CCPerson = WB3.Worksheets(1).Range("D" & PICRow) & "; " & CCPerson

    'CCEmail = CCEmail + 1

    'Else

    'End If

 

Next

 

MsgBox "Email will be sent to " & SendEmailTo & " recipients."

 

how to send email from excel

'get the named Image to attach to the email and copy it

Set oPreview = Wb1.Worksheets("Send Scoreboard").Shapes("Preview1")

oPreview.CopyPicture ' oPreview is now in Clipboard

 

'launch Outlook

    Dim xOutApp As Object

    Dim xOutMail As Object

    Dim xMailBody As String

  

    On Error Resume Next

   

    Set xOutApp = CreateObject("Outlook.Application")

    Set xOutMail = xOutApp.CreateItem(0)

  

'for html email

 

If Wb1.Worksheets("Send Scoreboard").CheckBox1.Value = True Then

    'include the link to the spreadsheet

    xMailBody = "Hello everyone! <br><br>" & "The SuperBowl Squares scoreboard has been updated. You can access the sheet by clicking the link below. <br><br>" & _

    "Link: <br><br>" & "<a href=" & Chr(34) & FileLoc & Chr(34) & " > " & WorkbookName & " </a> " & "<br><br>" & _

    "Thanks for playing," & "<br><br>" & OwnerName

Else

    'false, no link

    xMailBody = "Hello everyone! <br><br>" & "The SuperBowl Squares scoreboard has been updated. Please see the below image: <br><br>" & _

    "Thanks for playing," & "<br><br>" & OwnerName

End If

 

 

    On Error Resume Next

   

    With xOutMail

        .To = ToPerson

        '.CC = CCPerson

        .BCC = ""

        .Subject = WorkbookName

        '.Body = xMailBody

        .HTMLBody = xMailBody

        .Display   'or use .Send

 Quick Note: To use the clipboard to copy and paste the picture into email, you need an Outlook mail editor which can deal with the clipboard. Here I use WordEditor for example. The WordEditor property of the Inspector class returns an instance of the Document class from the Word object model which represents the Body of your email: https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/dd492012(v=office.12)?redirectedfrom=MSD

       

        Set oInspector = .GetInspector

        Set oWdDoc = oInspector.WordEditor

   

        Set oWdContent = oWdDoc.Content

        Set oWdRng = oWdDoc.Paragraphs(1).Range

        'oWdRng.InsertBefore "This is a test"

        oWdRng.InsertParagraphAfter

        oWdRng.InsertParagraphAfter

 

        Set oWdRng = oWdDoc.Paragraphs(3).Range

        oWdRng.Paste ' paste from oPreview Clipboard

 

        olFormatHTML = 2

        .BodyFormat = olFormatHTML ' change to HTML

       

    End With

   

    On Error GoTo 0

   

    Set xOutMail = Nothing

    Set xOutApp = Nothing

'---------------------------------------------------------------

ResetSettings:

  'Reset Macro Optimization Settings

    Application.EnableEvents = True

    Application.Calculation = xlCalculationAutomatic

    Application.ScreenUpdating = True

End Sub

Monday, December 6, 2021

2021 College Football Bowl Prediction Pool

The college football conference championships were played this past weekend which means the 2021 NCAA college football bowl season is here again! Therefore, it’s time to make your picks and predictions about who you think will win each bowl game. One of the best times of the holiday season (other than giving and receiving gifts) is being able to talk trash to your relatives about their terrible bowl picks. This year has the added bonus of not just single bowl games but the eighth year of a four team playoff to determine the national champion.




Features for this year's bowl prediction pool over the previous college football bowl pool manager spreadsheets include the following:

  • Easy method to make each bowl game worth a different point value, so the national championship game and semi-finals can be worth more points, or however you want to customize it.

  • Updated leaderboard tab with new stats

  • Separate entry sheet to pass out to participants or co-workers that can be imported automatically by a built-in macro

  • Complete NCAA college football bowl schedule with game times and TV stations

  • New stat sheet to track each conference's record during bowl season. Graph shows total conference teams and total conference wins

bowl pick em excel sheet download


The bowl prediction sheets include the football helmet designs for every team (taken from the 2017 college football helmet schedule spreadsheet), their win-loss record, and the logo for all bowl games. I added the helmets so those players who aren't big college football fans can pick a winner based on their favorite helmet design!

Download the CFP Pool Manager and Single Entry Form here

I'm working on a new version where you could do confidence points that you can test out now and give me feedback.



Download the 2021 CFP Bowl Prediction Pool Manager.xlsm file here

Please let me know if you have any questions, comments, find any bugs, or have any suggestions for improvement. I love that people are using this Bowl Prediction Game to help raise money for charity, that's so awesome to hear! What team are you rooting for?


Thursday, November 18, 2021

Gift Guide for Excel Users 2021

 The 2021 holiday season is officially upon us here in the United States which means it’s time for my annual gift giving guide. I used to panic every year whenever my spouse, parents, and siblings asked me what I wanted for Christmas. I needed to give them an idea otherwise I’d end up with an ugly sweater or some random gadget I would never use.

So to help alleviate some of my stress I started compiling my own holiday gift guide. It’s kind of like the big toy catalog you used to get as a kid, only this is for adults. I’ve made a list of items I think would be very useful or exciting for your fellow Excel users, sorted by different categories (and yes, this post does contain Amazon affiliate links). Some of these items I already use on a daily basis and others are things that are on my own personal wish list. It's my biggest and best gift guide yet! Enjoy!