Loading...

Follow The Spreadsheet Guru on Feedspot

Continue with Google
Continue with Facebook
or

Valid
The Spreadsheet Guru by Chris Newman - 2w ago

VBA is a coding language used by millions of people across the world to automate tasks in Microsoft Office products. It’s a language that as been around for decades and is one of the easiest coding languages to learn if you don’t have a computer science background. With it’s endless popularity in such programs as Excel, it might come as a surprise that their is this lingering fear VBA is going to be removed from Microsoft Office in the near future. I constantly see questions on forums asking “is it worth learning VBA at this point in time?” or “when will VBA stop working?” or better yet “what is replacing VBA?”.

Don’t get me wrong, these are all valid questions…especially with the way Microsoft has treated VBA over the past 10 years. But there is hope and through this article, I will present my thoughts and predictions about the so-called “inevitable death” of the VBA coding language.

Why Learn VBA Today?

To simply put it, it’s something that virtually anyone can teach themselves with very little effort.

Now before I get too far, let’s back up and talk about me for a minute. I, Chris Newman, founder of TheSpreadsheetGuru, am first and foremost a finance guy. Why is this important to clarify? Well, I want to stress I know nothing about computers, computer coding, or how it is physically possible to send information like this article through the air and into millions (ok, hundreds…) of digital devices to be read. Needless to say I not the poster child to be somewhat authoritatively writing about a computer coding language.

But I am. How? Because VBA is something that makes sense to anyone familiar with Excel (or Word or PowerPoint). There’s no weird syntax like curly brackets or a complex lexicon of phrases to memorize, you simply write what you want to do and it is done.

But the real beauty of VBA and it’s ease of learning is the availability of the Macro Recorder. This feature allows you to click a record button and it will write code for every single thing you physically carry out on your screen. While it might not be the most efficient way to write the code, it writes it in a way that in most cases automates your process without any further modifications. Teaching yourself how to automate specific tasks can be as easy as using the recorder and copying the code it spits out.

Let’s get back to the question in the header, “Why learn VBA today”?

You Can Pretty Much Do ANYTHING!

Have you ever wished you could build your own phone app or website or even a robot to clean your house? I know I have and it’s somewhat depressing because unless you have a ton of money to pay someone to build your dream invention or are ridiculously smart, you’re never going to be able to build those things. I remember back to when I was an ambitious 22 year old, fresh out of college and ready to conquer the world. Smart phones apps were in their infancy and I had an idea or two that was going to make me a millionaire. Lucky for me, Apple had a huge following and tons of educational content to help newbies like me learn to make apps. To make a short story even shorter, after a month of trying my hardest, I had gotten nowhere. There was simply too much to learn, and even basic coding concepts were not making any sense to me. I sadly had to scratch making my million dollar app from my “To Do” list.

Fast forward a year, and I’m starting to dive into VBA. With just a few lines of code I am manipulating thousands of lines of data within fractions of seconds. I start to get fancy and make interfaces with buttons and sliders. I figure out how to pull data from the internet. I finally come to the realization that I can easily figure out how to do virtually anything I want with VBA and Excel and my whole world changed.

I’m assuming many of you VBA coders out there have a similar story where the abilities you discovered via VBA brought your wildest dreams to fruition. I’ve seen some many creative solutions out there utilizing VBA code. There are examples of using VBA to build games with opponent AI, animated charting, and business management interfaces. You can truly do amazing things with this simple language!

Expert Status

Learning how to use VBA is a very quick way to differentiate yourself from the millions of other Excel users throughout the world. Excel users instantaneously associate folks who utilize VBA as an advanced user and will look to them for help. Chances are, if you were able to teach yourself VBA, you are pretty good at searching for Excel help and you more than likely can provide solutions to others quickly even if you don’t know the solution off the top of your head (thank you Google!).

Tons of Documentation and Help

VBA is a language that is been around for a long time and because of this, there is a tremendous amount of documentation and help available to us. I am still amazed at the response time in Excel forums such as Mr Excel where you can literally get help/solutions provided to you within minutes (yes I said minutes) of posting a well-written question. Forum help was a major source of education for myself as I taught myself VBA or explored for ideas on how to tackle an automation I wanted to implement. The Excel VBA community is absolutely one of the most generous and responsive online communities out there and it is because of this, that folks are able to take advantage of this language everyday!

So now that we know why Excel users have reason to learn and use VBA, what are Microsoft’s current thoughts on the language? With VBA’s popularity among the user-base, it seems like a no-brainer that they would want to see it’s capabilities and uses grow, right?

Why Does Microsoft Hate VBA?

Let’s start off by clearing the air…Microsoft doesn’t hate VBA, it’s just not as sexy as it used to be. In fact, many of the Excel developers I have talked with love VBA and use it quite frequently. But there does seem to be this communal movement inside Microsoft not to touch anything VBA unless (1) it crashes Excel or (2) a LARGE customer has a specific issue with something that used to work and now doesn’t. Proposals for new capabilities, efficiencies, and more modern functionality have all been seemingly ignored in the hope that we the users will just get tired of asking. This period of silence over the last 10 odd years has driven a lot of confusion among Excel users as to if the VBA language is even supported anymore.

Microsoft Developers have told me repeatedly that their current policy is to only maintain VBA’s object model when new objects are created so that users who want to automate creating some of the newly implemented charts like the Waterfall graph can do so. Only there’s a BIG problem! While the developers are giving us users access to new objects (think shapes, charts, tables, cells, etc…), they aren’t ensuring all the properties work properly or in some cases function at all!

Lets circle-back to those shiny new Waterfall charts Microsoft released into Excel a couple years back. I worked on a consulting project where I was building an Excel add-in that would automatically create and format of a variety of different charts. For example, the client wanted a bar chart with certain coloring and font formats. I used the macro recorder, re-created the chart in Excel to their desired specifications and I was off to the races whipping up a nice clean macro to automate the chart creation.

Now it’s time for the Waterfall Chart. I turn on the macro recorder, build the Waterfall to the exact specs, and guess what shows up in my Visual Basic Editor? Well, the written code started off with the creation of the waterfall (which was good!), but then it just recorded all my selections. There was absolutely no formatting associated with the code!

Well…..any experienced VBA enthusiast knows some things don’t get picked up with the recorder, so I use my overall VBA knowledge to start scribing the formats from scratch. I finish, run my compiler to check everything is in order and it checks out OK. Great! Now to the all important testing phase. I run my beautiful waterfall-creating code and my chart doesn’t look right. I notice the font color didn’t change. The Axis title wasn’t manipulated the way I wanted. The chart border was still showing! To my embarrassment, I had to tell the client some of their formatting requirements for the waterfall chart would still need to be done manually because it simply wasn’t possible (they loved that answer….).

Yes, that was kind of a long (and painful to relive) story, but I foresee many examples like the one I described occurring in the future as Microsoft continues to roll out more and more new features. It’s a shame, because with VBA and Excel combined you create truly amazing functionalities and with what Excel has in it’s pipeline, it will be very painful thinking it should be easy to automate something and two hours later realize the functionality is simply not possible.

So if Microsoft has plans to only minimally maintain VBA, what basket are they putting all their eggs into? Let’s explore that in the next section.

What Will Replace VBA?

Short answer: JavaScript.

JavaScript is a language Microsoft has been heavily investing in as it moves to unify Office across all devices (PC, Mac, Browser, & Mobile). Even though there is heavy resistance from pretty much all the Excel MVPs, over the past four years Microsoft has been gathering the expert communities thoughts and concerns over a full-frontal push to JavaScript for automating Excel.

So why did Microsoft pick JavaScript as their golden child over VBA and any other language out there? Why didn’t they just try to improve VBA?

First, JavaScript can work on virtually any device that is running an Office App. Currently there is no way to run VBA on Excel mobile or Excel Online. And if you’ve ever tried creating VBA code that is compatible with both PC and Mac, you know how much of a pain that is. With JavaScript, you can theoretically write a single script that would work on any device, which is a goal for all Office Apps.

Another reason is JavaScript is extremely fast at pulling data from outside sources. With more and more companies looking for seamless integration from a bunch of data sources, it is a definite bonus for Excel to be integrated with JavaScript.

It is also important to note that the Google Suite (G Suite) has been using JavaScript as their scripting language for quite some time. I’m not sure how much that played into Microsoft’s decision but it is interesting to point out.

Unfortunately (for Microsoft), the JavaScript replacement plan is going painfully slow. The API still cannot fully replace all the functionality that VBA currently gives us and adoption by the Excel community has been slow to non-existent.

When Will VBA Die?

OK, here’s the section you all have been waiting for. When is VBA going to go away? In my opinion, it will NEVER fully go away, but if I had to put a number to it’s remaining lifespan of relevance, I would say at least 15 years (that’s around 2035).

How can I be so confident it won’t be going away anytime soon? Because (1) too many companies rely on VBA to automate processes and (2) too many Excel users have invested time in learning VBA to go up and drop it and learn something completely different.

Let’s talk about company investments. Many companies both large and small rely heavily on VBA to automate tasks. I personally work with companies throughout the year implementing VBA solutions to automate tasks, making quite a big investment into the implementation of VBA. This can be anything from cleaning up data, to customizing model protection, to driving a company-specific add-in. There’s just simply too much money/time invested into VBA automation throughout the world for Microsoft to fully get rid of it.

Let’s now talk about the VBA user-base. Like I mentioned earlier in this article, most VBA code writers are not experts in code writing. The majority of VBA users are Finance, HR, Marketing, or Project Management professionals. These are professionals who were able to teach themselves how to write and implement automating scripts to make their lives easier. These folks are not full-time coders who are used to (or dare I say enjoy) learning new languages all the time. The majority of VBA coders were able to teach themselves because the language made sense to them and are not going to be very motivated to learn a whole new language in their spare time, especially with so little information/tutorials available to them currently. Hence, this population (the majority) of VBA users are more than likely going to stay within the bounds of what they know throughout their careers.

For those reasons, I believe it’s going to be the next generation of Excel users who haven’t been exposed to VBA that are going to drive the use of JavaScript into the workforce. These kids are still in high school and won’t be in the workforce any time soon. By the time they are getting jobs, it will be more beneficial for them to pursue learning JavaScript in Excel than VBA. It will more than likely be easier for them to pickup JavaScript because chances are, they took a computer coding class in High School or College as a “Gen Ed”.

What Do You Think?

I would love to hear you thoughts on this topic! Maybe you have used JavaScript in the past and can shed some more light on it’s advantages/disadvantages. Do you disagree with my thoughts on the remaining lifespan of VBA? Let me know in the comments section below. I look forward to reading and interacting with your thoughts!

Chris
Founder, TheSpreadsheetGuru.com

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I’m currently working on a consulting project where a VBA macro within an Excel file needs to run every morning before folks get into the office. As I work through getting this setup, I figured I would document my solution for everyone.

This article will go through how to utilize the Windows Task Scheduler (comes installed with most Windows PCs) to automatically open an Excel file, run it’s VBA code, save the file, and close the file. With this solution, you will be able to customize the frequency and exact time(s) this automation will need to be executed. The ONLY CATCH to this solution, is that the PC will need to be on during the scheduled time.

Sorry Mac folks, this is only a PC solution. However, if you know your way around a Mac, you may be able to figure out how to implement a similar solution on the Mac OS.

What You Will Need

To fulfill this solution, we will be utilizing a couple programs outside of Excel. You should have access to all of these programs.

  • Windows Task Scheduler

  • Notepad

  • CScript (Located: C:\Windows\System32\cscript.exe)

To launch Notepad and Windows Task Scheduler, simply type in their respective names into the search bar on your Taskbar and open the applications.

Creating the VBS File

We will be utilizing a Visual Basic Script file (.vbs) to list out a set of automation instructions. If you are familiar with VBA, this is essentially the same thing with the Sub() and End Sub statements and it will need to be written inside of Notepad.

Launch the Notepad application and paste the following code on the left into the text editor (the colors won’t display in Notepad).

'Input Excel File's Full Path
  ExcelFilePath = "C:\Users\chris\Documents\My File.xlsm"

'Input Module/Macro name within the Excel File
  MacroPath = "Module1.MyMacroName"

'Create an instance of Excel
  Set ExcelApp = CreateObject("Excel.Application")

'Do you want this Excel instance to be visible?
  ExcelApp.Visible = True  'or "False"

'Open Excel File
  Set wb = objApp.Workbooks.Open(ExcelFilePath)

'Execute Macro Code
  ExcelApp.Run MacroPath

'Save Excel File (if applicable)
  wb.Save

'Close Excel File
  wb.Close

'End instance of Excel
  ExcelApp.Quit

'Leaves an onscreen message!
  MsgBox "Your Automated Task successfully ran at " & TimeValue(Now), vbInformation

After you have pasted in the code, you will need to modify it to suite your needs.

  1. You will need to change the value of ExcelFilePath variable to point to the exact file path of the Excel file you would like to open. Make sure to include your Excel’s file name and extension at the end of the path.

  2. You will need to change the value of MacroPath variable to direct the automation to the precise macro you would like to run. Make sure you reference the Module name first and then the macro’s name.

  3. You can decide whether or not you would like the Excel application to visible show on the computer screen while all this automation is happening. Typically, I will use the True value while testing (just for a sanity check) and when everything is working smoothly, switch it to False.

  4. If you are making modifications to the Excel file itself, you may want to include a Save command within this script.

  5. Finally, you can choose whether or not you would like a message box to show, allowing the user to be notified if the automation ran successfully. Again this might be useful for testing or building up trust with your users that the job is actually running.

After you have made all your modifications to your Visual Basic script, Save the text as a .txt file via Notepad. For this example, I called the .txt file “Macro Launcher”.

Click to enlarge

Converting .txt to .vbs

Now we will finish up by converting our newly created text file into a Visual Basic Script file (or .vbs file). This is extremely easy to do!

  1. Navigate to where the text file is located using Windows Explorer

  2. Edit the name of the file by using keyboard shortcut F2

  3. Change the “.txt” to “.vbs” and hit your Enter key

  4. You will be prompted to confirm the changing of the file type. Click Yes

After following those steps, you should see your brand new .vbs file within Windows Explorer

Creating Your Windows Automation Rule

Finally, we will create an automation rule (or task) using a native Windows application called Windows Task Scheduler. To find this hidden gem, just type “Windows Task Scheduler” in the search bar of your Taskbar and launch it.

You should see a Task Scheduler dialog appear that looks something similar to the image below. If you do not see the Actions pane to the right, click the last icon in the application’s toolbar.

To create a new task, simply click Create Task.

Click to Enlarge

I will walk through each tab of the Create Task wizard, so you can get a good idea of all the settings you will most likely want to modify.

Create Task - General Tab

In the General Tab, you will be able to provide a name and a description (optional) for your automation task. Make sure to make your name descriptive so it will be easy to find in the long list of tasks that other applications on your PC have already created.

You will also be able to determine you would like the task to run if you are logged in/out of your PC. I assume most of you will want to enable the task to run while your computer is logged off, especially if your task is running on off hours.

Click to Enlarge

Create Task - Triggers Tab

In the Triggers Tab, you will be able to create in-depth scheduling rules for your automation. In the below example, I am creating a Trigger to execute the Task every day at 6:30am.

There are also options in the Advanced Settings section to repeat the task after the initial trigger. For example, you may what to refresh a stock portfolio model every 10 minutes after the stock market opens. The Advanced Settings section gives you the freedom to repeat the task every X number of minutes after the initial trigger has executed.

You do have the freedom to create multiple trigger rules if necessary.

Click to Enlarge

Create Task - Actions Tab

The Actions Tab is where you will map out which program you would like to launch. In our example, we are going to want to run the .vbs file we created that will open and run our Excel macro. We will utilize a program called CScript to execute our .vbs file. You should have this program already installed if you have a Windows OS. Below is the path to the application for your reference:

  • Program/Script: "C:\Windows\System32\cscript.exe"

Next, we will want to reference the file to open via CScript. Paste in the full file path of the .vbs file we created earlier. Mine looks something like this:

  • Add Arguments: "C:\Users\chris\Documents\Macro Launcher.vbs"

PLEASE NOTE: Both of these inputs MUST be surrounded by double quotation marks or else your task will not function!

Click to Enlarge

Create Task - Conditions Tab

With the options in the Conditions Tab, you can further customize the determining factors for when your task should run. The only one of importance to me is the option highlighted below, which will allow your task to run even if your PC goes to sleep.

Click to Enlarge

Create Task - Settings Tab

Finally, the Settings tab provides a few additional options to customize your task. I have highlighted one important option below if you are incorporating a “completion notification” message box into your Visual Basic script (FYI we are using a message box in the the example).

It is important to understand that if a message box appears on the screen, by default your task cannot repeat itself until the message box is closed by the user. To get around this, you can tell the task to kill itself if and rerun if no one ended up closing the message box during the time between triggers.

Click to Enlarge

Modifying Your Task In The Future

If you ever need to modify or delete you task in the future, you will need to select it from the list of tasks create on your PC (see #1). After your select your desired task, you should see a plethora of options available to you with the Actions Pane. To modify your task, simple select the Properties option and you will be taken back through the Create Task wizard.

Click to Enlarge

You Now Have An Automation!

There you have it. With the utilization of a couple programs, you now have a Windows task setup to execute your macro(s) automatically. This will save you time and hassle going forward…and needless to say make you look extremely smart to all your co-works! I hope this article helped you get the solution you were looking for.

Let me know what you were able to automate in the comments section below!

Cheers!

Chris
Founder of TheSpreadsheetGuru.com

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
The Need For A Modern UI

Let’s face it, VBA Userforms haven’t had much love from Microsoft over the past 10 or so years…..and it shows! Unfortunately, while Excel’s UI gets a fresh paint coat every 3-4 years, the userform controls still look like they were built back in the 90s.

Dell Computers Website in the 1990s (click to enlarge)

Why do we care? It’s simple, appearance can lead to credibility.

Let me put it into perspective for you. Would you buy a new Dell computer from a website that looked like this? —>>
My answer would be “hell no!”
, but this very website is what thousands of customers used back in the 90s to design and purchase their own PCs.

If you are anything like me, the first site of a webpage like the one shown in my example triggers an immediate search for the broswer’s back button. Let’s take a second to think about this: if we ourselves don’t like re-experiencing the lackluster design of the nineties, why on Earth would we want to give ourselves or our users that very same experience within Excel?

Since Microsoft doesn’t seem to care, I’m taking it into my own hands and am going to provide you with the exact formatting requirements to bring your userform’s appearance back to present day and match the current UI implementations of Excel online.

Note: The color of a userform’s Title Bar is determined by a setting within the operating system, not within Excel or Office

Formatting The Userform Object

It’s very important to keep in mind what your overarching formats will be when first building a Userform. Properties that are shared between all controls (such as Font color and BackColor), default to whatever the Userform Object is currently set to prior to inserting a new control.

We can leverage these inter-workings to our benefit and knock out a large amount of the formatting needs. If you are performing a makeover to an existing Userform, you’re unfortunately going to have to apply these formats to all the controls individually (but make sure to select multiple controls by holding down your Ctrl key and “bulk change” the formats to save time.

Here are the key property changes you should make at the Userform level and therefore be applied to every control on your Userform where applicable.

Font Size: 10
Font Type: Calibri
Font ForeColor: &H00464646& (Dark Gray)
BackColor: &H00FFFFFF& (White)
BorderColor: &H00A9A9A9& (Light Gray)

Formatting TextBox Controls

TextBox controls allow users to enter information into them and are controls I see quite frequently while interacting with userforms. To modernize these controls, you’ll want to tweak the below properties for them. Though these changes may be subtle, they do just enough to bring the default sunken textbox appearance to a more current and familiar look.

Border Effect: Flat (I would recommend changing this on all controls where possible)
Border Style: fmBorderStyleSingle
BorderColor: &H00A9A9A9& (Light Gray)

Create Your Buttons In Excel

All the property changes so far have been rather painless, but here is where the extra effort will come into play. But don’t worry, this time spent will be well worth it!

I will typically make buttons by simply drawing a textbox object (Insert tab > Textbox) on the spreadsheet. From there, you can manipulate the following properties to that textbox to create both the white and green formats of the buttons. These two button styles will be used on the backend with your VBA code to toggle between a “hovered state” and an “inactive state”.

Generic Formats

Textbox Size: 0.25H x 0.80W
Font Color: RGB(70, 70, 70)
Font Name: Calbri
Font Size: 10

Hovered State

Fill Color: RGB(211, 240, 224)
Border Color: RGB(134, 191, 160)
Border Weight: 1pt

Inactive State

Fill Color: RGB(255, 255, 255)
Border Color: RGB(169, 169, 169)
Border Weight: 1pt

PRO TIP: If you do not know how to manually add a custom RGB color code, THIS ARTICLE will show you how.

Adding The Hovering Affect With VBA

I am an absolute sucker for hovering effects on buttons. I think they are so simple and elegant but give the user the most subtle of clues of where one might want to click. This affect has erased the need to change the cursor icon from pointer to the pointing hand (in my opinion) and I HIGHLY RECOMMEND you only implement one or the other within your userforms. Remember, you are going for a subtle effect and don’t want to overkill it with too many changes when you user is hovering over buttons.

I dedicated an entire article to explaining the concept of this effect so I won’t re-hash the contents of that post entirely, but if the following code doesn’t make sense, you should definitely consider reading my article entitled: Creating Userform Buttons That Highlight Mouse Hovering.

The general concept equates to you using Image Controls to toggle the two button images created in the prior section (green and white buttons). A green button will hide directly behind a white button.

You can use the OnMouse VBA event to determine when the user’s cursor is hovering over one of your white buttons and trigger the white button to be invisible (revealing the green button which will be the button the user actually clicks). Consequently, when the user moves their mouse away from a button, the OnMouse VBA event can trigger the white button to reappear and hide the green button. Hopefully, that makes some sense and if not, definitely check out my more in-depth article.

VBA Example For The Create Button

Going along with the simple userform we’ve been working with throughout this article, there will be three VBA subroutines you will need to add to your Userform in order to make both buttons have a proper hover effect.

This first snippet of VBA code handles hiding the Save button’s white button when the user hovers over it. Notice the code is also ensuring the Cancel button’s white button is visible. This guarantees that only one button can appear green at a time.

Sub SaveButtonInactive_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'PURPOSE: Make Save Button Green when hovered on

  CancelButtonInactive.Visible = True
  SaveButtonInactive.Visible = False

End Sub

This next snippet of VBA code addresses the functionality for the Cancel Button.

Sub CancelButtonInactive_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'PURPOSE: Make Cancel Button Green when hovered on

CancelButtonInactive.Visible = False
SaveButtonInactive.Visible = True

End Sub

This last snippet of VBA code handles the MouseMove event from the Userform’s perspective. This is essentially your reset code for when the mouse cursor leaves any of your buttons with the hover effect impletemented.

Sub Userform_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'PURPOSE: Reset Userform buttons to Inactive Status

CancelButtonInactive.Visible = True
SaveButtonInactive.Visible = True

End Sub

Your Userform Is Now Current!

It’s amazing what a few little steps can do to bring your userform’s experience past nearly 30 years of aged design techniques. I hope you were able to easily follow along and now have some techniques under your belt that you can start to implement as you build or refresh the look of your userforms.

If you are more of a visual learner, I have put together an example file with the userform and all the various components I described throughout this article. Like all of the available example files on this site, you can get instant (and free) access to them after you have signed up for my free Excel tips email newsletter. You can sign up by clicking the green button below and get sent a password to the Subscribers-Only area of my website.

 

Already Subscribed? Click HERE to log-in to the "Example Files" section

 Let Me Hear From You!

I’d love to hear your thoughts on Userform and anything you might do to bring your forms into the modern age. Let me know in the comments section below!

- Chris

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In this article, I am going to explain the process I use to create an emphasis effect on my userforms when the user hovers their mouse over a specific button. You will be able to do this without the need for any additional software and it is very easy to implement once you understand the concept.

Understanding the Hover Concept

Since the built-in CommandButton object for Userforms is highly dated in appearance and has many restrictions in terms of how it can be formatted, I have opted to work with Image Controls for buttons instead. Since images are static and cannot for example change color during run-time, we will be using two images per single button. One image will represent the un-hovered state and the other will represent the hovered state.

The concept is fairly straightforward: we will enact VBA code that will toggle the visibility of the two images based on the location of the users mouse. This will essentially create a toggle button. You can think of it as a curtain we are opening and closing to reveal the actual button behind. The cursor’s position will dictate whether the curtain parts or closes.

Creating The Button Images In Excel

Let’s first create the buttons inside Excel using a textbox. You should already have in mind how you would like your buttons to look. For this example, we will be using “Excel Green” and a basic white/grey format to indicate whether the user is hovering over a particular button.

Things to keep in mind while creating your buttons:

  • What font type and size is your userform using?

  • What font color is your userform using?

  • How big are the controls within your userform?

For the purposes of this tutorial, I am going to assume you are using the default sizes/formatting for the userform control. Below outlines the formats I have chosen to apply to my buttons made out of regular Excel Textboxes.

One thing to keep in mind is that the buttons need to be the EXACT SAME SIZE. This means if you have a button with a border, all your buttons will need to possess a border. In my example above, the green buttons do have a 1pt border included, it is just the same color as the fill color, so no visible border appears to the user. This ensures all four buttons will be exactly the same size at the pixel level when we add them to our userform.

Adding Image Buttons To Userforms

In this section, I will walk you through how to get one of the buttons you made in the previous section into a Userform. Throughout this section, you will be interacting a lot with the Properties Pane. If you do not see the Properties Pane when you open the Visual Basic Editor, you can hit the F4 key on your keyboard to make it appear (or the long way would be to navigate to View > Properties Window)

Step 1

Before we begin, you should already have your userform build. I also recommend making the userform’s BackColor white and turning the BackStyle for labels to 0-fmBackStyleTransparent. Those two options can be accessed within the Properties Pane when you select a specific control.

Step 2

First, we will open up the Toolbox dialog (shown to the right) and insert an Image control. If you do not see the Toolbox floating around your screen within the Visual Basic Editor, you can access it via the View menu and selecting Toolbox. The Image control will appear as a grey box with a dark border once place on your userform.

Step 3

Next, you will want to insert one of the textbox buttons you created into the Image Control. To do this, simply go to your spreadsheet and copy your desired textbox (ctrl + c). Then navigate back to the Visual Basic Editor and select the Image Control. Finally, go to the Properties Pane and click inside the field labeled Picture. Use the keyboard shortcut Ctrl + v and the fields value should change from “(None)” to “(Bitmap)”. You should now see the image appear within the control.

Step 4

To clean up the appearance a little further, go back to the Properties Pane with the Image Control still selected and make the following changes:

  • BorderStyle = 0-fmBorderStyleNone

  • BackStyle = 0-fmBackStyleTransparent

Finally, I recommend tightening up the size of the control to get it as close to the actual image size as possible. I typically leave 1-2 pixels of whitespace between the Image Control’s border and the edges of the image within it.

Step 5

Next, we will begin to make our second button which will represent the inactive (un-hovered) state. Go ahead and copy the Image Control (ctrl + c) and paste it (ctrl +v) somewhere else on your userform.

Step 6

To change the image of this second Image Control, simply redo Step 3 and copy/paste the White Button into it.

Step 7

Place the White OK button exactly on top of the Green OK button. You can do this by using your mouse or you can input the Top and Left properties within the Properties Pane so that the location of both buttons are exactly the same. If you have Align to Grid turned on (Tools menu > Options > General > Align Controls to Grid), it should be fairly easy to get them on top of each other with the mouse.

Creating A Hover With VBA

To illustrate the hovering technique more clearly, I went ahead and added another button to the userform (shown below). Circling back to the concept of what we are going to be doing here from a VBA front, note we have essentially two white buttons covering up our green, actionable buttons (the buttons the user will actually be clicking with their mouse).

If you think about it, all we need to do is write some VBA that hides the white buttons when the user’s mouse hovers over them. This reveals the desired green button and gives the user the opportunity to click it (think back to the curtain analogy from earlier).

So let’s get into a little VBA coding!

MouseMove Event

If you have done any work with Userforms, you know that events are vital to making the user experience feel “normal”. For this effect, we are going to be using the MouseMove event which tracks which pixel on the screen the user’s cursor is currently touching. The moment the cursor moves to a pixel within a specific control, that control’s MouseMove event fires and the code attributed to it begins to run. This is precisely how we will know when to toggle our button images to create the hover effect!

Image Control Names

Before we write any VBA macros, let’s be clear on what I have named the Image Controls for this example. Below are the four names I have attributed to the controls via the Properties Pane > Name field.

CancelButtonInactive = White Cancel Button
OKButtonInactive = White OK Button
CancelButton = Green Cancel Button (This is the real button that will call your VBA macro)
OKButton = Green OK Button (This is the real button that will call your VBA macro)

The OK Button

Below is the VBA code we will attribute to the OKButtonInactive (white OK button) image control. Note that the MouseMove event has a bunch of inputs (Button, Shift, X, Y). We will not be using any of these, however, the will need to be explicitly written as shown below in order for the VBA code to compile properly.

Essentially what the VBA code does is when the user’s cursor hovers over the OKButtonInactive control, it will hide itself, revealing the green OK button underneath of it.

You’ll also notice that the code is also making sure the CancelButtonInactive control is visible. This prevents any risk of the user hopping from the Cancel button to the OK button “turning” both buttons green. This is a very important concept as you will need to be sure you account for all buttons on your userform that are using this hover technique.

Sub OKButtonInactive_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'PURPOSE: Make OK Button appear Green when hovered on

  CancelButtonInactive.Visible = True
  OKButtonInactive.Visible = False

End Sub

The Cancel Button

I won’t go into as much detail as the previous section, but the below VBA code is essentially do the exact same thing except for the Cancel Button.

Sub CancelButtonInactive_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'PURPOSE: Make Cancel Button appear Green when hovered on

CancelButtonInactive.Visible = False
OKButtonInactive.Visible = True

End Sub

Accounting For No Hovering Action

Up until now, we have assumed either the user’s cursor is on the OK button or the Cancel button. But what if the user has their cursor somewhere else? In that case, we don’t want any of the buttons to appear as green. By using the Userform_MouseMove event, we can essentially reset the userform by making all the white buttons visible. The precise moment the cursor moves off a control and onto the Userform’s background, this VBA code will trigger and change the visibility state of our buttons.

Sub Userform_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'PURPOSE: Reset Userform buttons to Inactive Status

CancelButtonInactive.Visible = True
OKButtonInactive.Visible = True

End Sub

Be Careful About Control Proximity

Early on when I was perfecting this technique, I ran into issues when my controls were too close together. Based on your userform’s layout, there may be a risk that you perform what I have coined as “control hopping”. This is where your cursor moves directly from one control to the other without triggering the Userform_MouseMove event. This theoretically shouldn’t happen since the cursor has to move onto the Userforms background if there is even one pixel of space between your control.

What I recommend doing is testing your userform by moving your mouse over it quickly to see if you can trigger a state where multiple buttons appear to be highlighted at the same time. To fix this you can either add extra space between the controls on the screen or add a MouseMove event to that control that runs the exact same code as the Userform_MouseMove subroutine.

You Now Have Hover-Buttons!

If you’ve followed along all the steps, you should now have a functioning userform with buttons that change format every time the cursor hovers over them. If you are still unclear or would like to see these results in an actual Excel workbook, I have put together a downloadable spreadsheet you can comb through. As with all my downloadable files here on TheSpreadsheetGuru, you need to be a member of my Excel Tips Newsletter. You can get signed up quickly by clicking the button below.

 

Already Subscribed? Click HERE to log-in to the "Example Files" section

 How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it! 

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris
Founder of TheSpreadsheetGuru

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Why Is This Important?

When you are writing VBA macros that rely on a user’s selection, you want to make sure they have selected the proper object within Excel. Running code that is meant for a Chart when the user currently has a cell range selected could spell disaster for your routine. In this article, I am going to attempt to share with you as many methods as I can think of to determine if a specific object is selected.

Is A Range Selected?

Since Excel is filled with TONS of cells, chances are you will run into lots of scenarios where you may want to automate cell formats. The following VBA snippet shows you how to ensure you user has selected a cell range an not something else like a Chart or Shape prior to you modifying the selection.

Sub StoreSelectedRange()
'PURPOSE: Store user-selected range to a variable

Dim rng As Range

'Ensure the user has selected a cell range
  If TypeName(Selection) "Range" Then
    MsgBox "Please select a cell range before continuing"
    Exit Sub
  Else
    Set rng = Selection
  End If

End Sub

Is A Chart Selected?

Sub StoreSelectedChart()
'PURPOSE: Store user-selected Chart to a variable

Dim cht As Chart

If ActiveChart Is Nothing Then
  MsgBox "Please select a Chart before continuing"
  Exit Sub
Else
  Set cht = ActiveChart
End If

End Sub

Is A Chart Series Selected?

How about if you would like modify a specific chart series within the currently selected chart? I have needed to do this in my AutoChart Excel add-in and myBrand Excel add-in where I allow users to easily change the color of a specific Chart Series. The following code will show you how to ensure a series within a chart is selected.

Sub StoreSelectedChartSeries()
'PURPOSE: Store user-selected Chart Series to a variable

Dim srs As Series

If TypeName(Selection) = "Series" Then
  Set srs = Selection
Else
  MsgBox "Please select a Chart Series before continuing"
  Exit Sub
End If

End Sub

Is A Table Selected?

Sub StoreSelectedTable()
'PURPOSE: Store user-selected table to a variable

Dim tbl As ListObject

On Error Resume Next
  Set tbl = ActiveCell.ListObject
On Error GoTo 0

If tbl Is Nothing Then
  MsgBox "Please select a cell within a table before continuing"
  Exit Sub
End If

End Sub

Is A Shape Selected?

What if you want to determine or modify the user’s currently selected shape? The following code will determine if there is a shape object selected and then store that shape to a variable called “shp” to allow you to easily manipulate the shape going-forward.

Sub StoreSelectedShape()
'PURPOSE: Store user-selected Shape to a variable

Dim shp As Shape

  On Error Resume Next
    Set shp = ActiveSheet.Shapes(Selection.Name)
  On Error GoTo 0
  
  If shp Is Nothing Then
    MsgBox "Please select a Shape before continuing"
    Exit Sub
  End If

End Sub

Any Scenarios I Have Missed?

I tried to cover the main user-selection scenarios you may want to capture in your VBA code. If there are other scenarios you can think of, please point them out in the comments section below. I will look to add to this article when applicable.

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it! 

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris
Founder of TheSpreadsheetGuru

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

There may be occasions when you will want to export/save a particular PowerPoint slide(s) as an image file. A real world example of why you might need to do this could be to create a high-resolution thumbnail image for your YouTube videos (I do this all the time!).

In this article, we’ll take a look at the various file options we have access to via PowerPoint and how to save our slides to an image format.

Available Picture Types

You have four file types available to your when you are saving a PowerPoint slide:

  • PNG

  • JPEG

  • GIF (Graphics Interchange Format)

  • BMP (Bitmap)

  • EMF (Enhanced Windows Metafile)

I recommend using a PNG if your slide contains graphics and a JPEG if you slide contains real-life imagery from a camera.

Steps To Take

Here are the steps to saving a selected slide as an image:

  1. Go To File

  2. Select Save As in the left-hand pane

  3. Select your desired File Type from the Save As dialog box

  4. Click Save

  5. Select from the Prompt if you want to save All Slides or the Active Slide

How to Save As Images With VBA

Sub SaveSlideAsImage()
'PURPOSE: Save each selected slide as an individual image file
'SOURCE: www.TheSpreadsheetGuru.com

Dim FileExtension As String
Dim SaveLocation As String
Dim ImageName As String
Dim SelectedSlides As SlideRange
Dim sld As Slide
Dim x As Long

'Inputs
  FileExtension = "png" 'jpg, gif, bmp, emf
  SaveLocation = "C:\Users\chris\Desktop\"
  ImageName = "Custom Image"
  
'Set variable equal to only selected slides in Active Presentation
  On Error GoTo NoSlideSelection
    Set SelectedSlides = ActiveWindow.Selection.SlideRange
  On Error GoTo 0

'Loop through each selected slide
  For x = 1 To SelectedSlides.Count
    
    'Store each slide to a variable
      Set sld = SelectedSlides(x)
      
    'Save Slide as image file
      With ActivePresentation.Slides(sld.SlideIndex)
        .Export SaveLocation & ImageName & _
        sld.SlideIndex & "." & FileExtension, FileExtension
      End With

  Next x

Exit Sub

'ERROR HANDLERS
NoSlideSelection:
  MsgBox "You do not have any slides in your PowerPoint project.", 16, "No Slides Found"
  Exit Sub
End Sub

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I’ve run into a few times where I felt the user experience would be more streamlined if I gave them the option to bring in data or properties from their spreadsheet. You have a couple of options for doing this, as you can either pull this information from:

  • the user’s current selection

  • a predetermined cell range or object name

  • ask the user to type a cell address into an inputbox

I would argue the last bullet listed above is most likely going to be the most straightforward for your users as they have the most freedom to state any range address they want at that point in time. But is it worth giving the user this much freedom? Is it worth spending the countless hours coding to prevent any incorrect input a user might enter into your inputbox?

This is where the beauty of the built-in VBA InputBox object will save you time and effort. Let’s look at how we can use the InputBox to easily prompt the user to select a cell range so we can store that range location to a variable.

Using The InputBox Object

I won’t go into all the detail of what the InputBox Object can do as you can read all the attributes via Microsoft’s documentation here. However, I will note that the InputBox has some very handy input restrictions that we can use to easily account for all the error handling we need to confirm our users are properly inputting a valid Excel range. This attribute is called the Type.

InputBox “Type” Attribute
ValueMeaning
0A formula
1A number
2Text (Default)
4A logical value (True or False)
8A cell reference, as a Range object
16An error value, such as #N/A
64An array of values
InputBox Object Attributes

Below are all the attributes you may modify while using the InputBox object. The main ones we will be using are the Prompt, Title, and Type attributes.

Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type)

VBA Code Examples

Effectively what the following VBA code examples are going to carry out is prompt the user to either enter or select a cell range with their cursor. This InputBox also has the ability to reference different worksheets within the same workbook file. This flexibility really allows your user to have the best experience while providing your VBA macro a variable cell range to work with.

Grab A Cell Range

In this VBA code example, the macro’s goal will be to retrieve a Custom Number Format rule from the user and apply it to the user’s current cell selection. The InputBox will be used to gather a single cell input from the user and store that cell and all it’s properties to a variable. This way, the user does not need to type out the number format rule themselves, they can simple point to a cell that already has the rule applied. This technique is also extremely useful to getting color inputs from your users.

Sub NumberFormatFromCell()
'PURPOSE: Obtain A Number Format Rule From A Cell User's Determines

Dim rng As Range
Dim FormatRuleInput As String

'Get A Cell Address From The User to Get Number Format From
  On Error Resume Next
    Set rng = Application.InputBox( _
      Title:="Number Format Rule From Cell", _
      Prompt:="Select a cell to pull in your number format rule", _
      Type:=8)
  On Error GoTo 0

'Test to ensure User Did not cancel
  If rng Is Nothing Then Exit Sub
  
'Set Variable to first cell in user's input (ensuring only 1 cell)
  Set rng = rng.Cells(1, 1)

'Store Number Format Rule
  FormatRuleInput = rng.NumberFormat

'Apply NumberFormat To User Selection
  If TypeName(Selection) = "Range" Then
    Selection.NumberFormat = FormatRuleInput
  Else
    MsgBox "Please select a range of cells before running this macro!"
  End If

End Sub

Grab A Cell Range With A Default

The below VBA macro code shows you how to display a default cell range value when the InputBox first launches. In this example, the default range will be the users current cell selection.

Sub HighlightCells()
'PURPOSE: Apply A Yellow Fill Color To A Cell Range

Dim rng As Range
Dim DefaultRange As Range
Dim FormatRuleInput As String

'Determine a default range based on user's Selection
  If TypeName(Selection) = "Range" Then
    Set DefaultRange = Selection
  Else
    Set DefaultRange = ActiveCell
  End If

'Get A Cell Address From The User to Get Number Format From
  On Error Resume Next
    Set rng = Application.InputBox( _
      Title:="Highlight Cells Yellow", _
      Prompt:="Select a cell range to highlight yellow", _
      Default:=DefaultRange.Address, _
      Type:=8)
  On Error GoTo 0

'Test to ensure User Did not cancel
  If rng Is Nothing Then Exit Sub
  
'Highlight Cell Range
  rng.Interior.Color = vbYellow

End Sub

Grab A Cell Range Using A Userform

If you want to use the InputBox technique within a userform, I highly recommend hiding your userform before prompting the user with the InputBox. The following code is an example of how you might accomplish this.

Sub NumberFormatFromCell()
'PURPOSE: Obtain A Number Format Rule From A Cell User's Determines

Dim rng As Range
Dim FormatRuleInput As String

'Temporarily Hide Userform
  Me.Hide

'Get A Cell Address From The User to Get Number Format From
  On Error Resume Next
    Set rng = Application.InputBox( _
      Title:="Number Format Rule From Cell", _
      Prompt:="Select a cell to pull in your number format rule", _
      Type:=8)
  On Error GoTo 0

'Test to ensure User Did not cancel
  If rng Is Nothing Then
    Me.Show 'unhide userform
    Exit Sub
  End If
  
'Set Variable to first cell in user's input (ensuring only 1 cell)
  Set rng = rng.Cells(1, 1)

'Store Number Format Rule
  FormatRuleInput = rng.NumberFormat

'Apply NumberFormat To User Selection
  If TypeName(Selection) = "Range" Then
    Selection.NumberFormat = FormatRuleInput
  Else
    MsgBox "Please select a range of cells before running this macro!"
  End If

'Unhide Userform
  Me.Show

End Sub

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it! 

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris
Founder of TheSpreadsheetGuru

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I’ve run into this issue numerous times and I just figured out how to prevent the dreaded error message from popping up when Excel opens stating:

“Sorry, we couldn’t find add-in [insert add-in name]. Is it possible it was moved, renamed or deleted?”
— Microsoft Excel How To Prevent This

First you will want to note the file path that Excel thinks the add-in should be located in and the name of the add-in file. Once you know where that folder is located and the name of the add-in, proceed to do the following:

  1. Create a new Excel file

  2. Save the file with the following conditions:

    • Use the Add-in name

    • Use the Excel file extension “.xlam” (if it is an older add-in it might need to be “.xla”)

    • Save to the same folder path

  3. Once you have saved this new faux add-in file, close down Excel

  4. Reopen Excel and hopefully you won’t receive the error message

  5. Go into your Excel Add-ins dialog box and check the add-in and hit Ok

  6. Go back into you Excel Add-ins dialog box and uncheck the add-in and hit OK

  7. Close out of Excel

You should now be able to go and delete the faux Excel add-in file and not see the error message going forward. Yay!!

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
There’s No Middle Alignment Option…

Let’s just get straight to the point….unfortunately for some (lame) reason, Microsoft did not give us the option to middle align text within our Userform textbox controls. This can lead to some sloppy looking interfaces (see the userform on the left below). But fear not, with a little bit of creativity, we can get the look and feel our userforms deserve!

Example File Available

If you would like to see the finished solution inside a live Excel spreadsheet, I have added an example file with the above userform built to my Example Files vault. You can gain access through the button below.

 

Already Subscribed? Click HERE to log-in to the "Example Files" section

 Step 1: The Overlay

The essence of the trick is going to rely on overlaying one textbox on top of another one. So first, make a copy of your textbox and resize the height to the height of your textbox font size. Please note, I will be refering to the “original” textbox and the “new” textbox throughout these steps.

Step 2: Alignment

Next you will want to center and middle align your New Textbox with your original one. To ensure that your Original Textbox does not move, select it last (while holding the Ctrl key). You will know your selection is correct if your Original Textbox has white boxes around it instead of black (shown below).

With both textboxes selected, proceed to right-click your mouse and navigate to the Align options and select both Middle and Centers. This will ensure you have perfect alignment.

Step 3: Remove Sunken Effect

Next we will do some manipulation to “hide” the New Textbox. Select your New Textbox and go into it’s Properties (keyboard shortcut F4 to make appear). Scroll down to the SpecialEffect property and change it to 0 - fmSpecialEffectFlat.

After doing this you may notice that the New Textbox is covering up the right/left edges of the Original Textbox’s border. To fix this, just bring in the sides of your New Textbox a bit until it is no longer covering any border.

Step 4: A Couple Finishing Touches

If you go to test your userform now, you might notice a couple things:

  1. There is an indentation in your text

  2. It may still be possible to select the textbox in the background (aka Original Textbox)

Fix Indentation - Select your New Textbox and change the SelectionMargin property to False

Fix Selection Issue - Select your Original Textbox and change the Enabled property to False

You’ve Done It!

You have now completed the steps to stick it to Excel and show that you can really do anything you want inside this amazing program. If you have any questions, ideas, or need further clarification let me know if the comments section below.

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it! 

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris
Founder of TheSpreadsheetGuru

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
What Are ListBox Controls?

ListBoxes are a great way to manage tables within your userform. I have used them in the past to allow users to manage rows of information without necessary displaying all that information within the userform itself. There are a lot of neat things you can do with a userform so I’ve collected some of the more popular tasks you may want to know how to write within your VBA code.

For all the example VBA code snippets, the name of the listbox with be called “ListBox1”. I also have a downloadable example file that you can get for free if you want to see a lot of this code in action.

Enjoy!

 

Already Subscribed? Click HERE to log-in to the "Example Files" section

 Add An Item to The ListBoxTo The End

ListBox1.AddItem "Apple"

To A Specific Position

Remember ListBoxes are zero based, so the first item is really at position 0. So if you want to add an item to the 5th position, you need to reference number 4 in the AddItem function.

'Add to the 2nd Position (subtract 1 from desired)
  ListBox1.AddItem "Apple", 1

Add Multiple Items To The ListBoxIndividually Written

Sub ListBox_Load()

ListBox1.AddItem "Apple"
ListBox1.AddItem "Orange"
ListBox1.AddItem "Pear"
  
End Sub

From An Array List

Sub ListBox_LoadArray()

Dim myArray As Variant

myArray = Array("Apple", "Orange", "Pear")

ListBox1.List = myArray

End Sub

From A Cell Range

Sub ListBox_LoadRange()

Dim cell As Range

'Load to ListBox
  For Each cell In Worksheets("Sheet1").Range("A1:A6")
    ListBox1.AddItem cell.Value
  Next cell

End Sub

From A Table Object (ListObject)

Sub ListBox_LoadTable()

Dim tbl As ListObject
Dim cell As Range

'Store Table Object to a variable
    Set tbl = Sheet1.ListObjects("Table1")
  
'Load List Box
  For Each cell In tbl.DataBodyRange.Columns(1).Cells
    ListBox1.AddItem cell.Value
  Next cell
  
End Sub

Delete ListBox ItemsRemove An Item From The ListBox

'Remove 4th item in ListBox (subtract 1 from desired row)
  ListBox1.RemoveItem 3

Remove Selected Item(s) From The ListBox

Private Sub DeleteSelection()
'PURPOSE: Remove any selected items from the ListBox

Dim x As Long
Dim OriginalCount As Long

'Store original ListBox count
  OriginalCount = ListBox1.ListCount

'Temporarily hide ListBox (runs faster)
  ListBox1.Visible = False

'Delete selected line items
  For x = OriginalCount - 1 To 0 Step -1
    If ListBox1.Selected(x) = True Then ListBox1.RemoveItem x
  Next x

'Unhide ListBox
  ListBox1.Visible = True

End Sub

Remove All Items From The ListBox

ListBox1.Clear

Listbox Selected ItemsSelect A Specific Item In The ListBox

'Select the 5th item in the ListBox (subtract 1 from desired row)
  ListBox1.Selected(4) = True

Deselect All Items

Unfortunately, the “ListIndex = -1” method does not work when a ListBox allows for multiple selections. Hence, the below code tests for the ListBox’s selection mode.

If ListBox1.MultiSelect = fmMultiSelectSingle Then
  ListBox1.ListIndex = -1
Else
  For x = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(x) Then ListBox1.Selected(x) = False
  Next x
End If

Count How Many Items Are Selected (Function)

Function ListBoxSelectionCount(LB As ListBox) As Long
'PURPOSE: Count how many items are selected in a give Listbox

Dim x As Long
Dim Count As Long

For x = 0 To LB.ListCount - 1
  If LB.Selected(x) Then Count = Count + 1
Next x

ListBoxSelectionCount = Count

End Function

Count How Many Items Are In The ListBox

'Return how many items are in the ListBox
  MsgBox ListBox1.ListCount

Move Selected Item Up/DownMove Selection Up One Position

Sub MoveUp()
'PURPOSE: Move the selected item up one position in the list

Dim x As Long
Dim Count As Long
Dim Position As Long

'Is there an item selected?
  If ListBox1.ListIndex = -1 Then Exit Sub
  
'Which Item is selected?
  For x = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(x) = True Then
      Position = x
      Count = Count + 1
      If Count > 1 Then Exit Sub 'More than 1 item selected
    End If
  Next x

'Selected item already at the top?
  If Position = 0 Then Exit Sub

'Add an item above the current selection
  ListBox1.AddItem ListBox1.List(Position), Position - 1

'Remove Original Selection
  ListBox1.RemoveItem Position + 1

'Re-select the item that got moved
  ListBox1.Selected(Position - 1) = True

End Sub

Move Selection Down One Position

Sub MoveDown()
'PURPOSE: Move the selected item down one position in the list

Dim x As Long
Dim Count As Long
Dim Position As Long

'Is A ValidSelection Made?
  If ListBox1.ListIndex = -1 Then Exit Sub 'No Selection made
  
'Which Item is selected?
  For x = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(x) = True Then
      Position = x
      Count = Count + 1
      If Count > 1 Then Exit Sub 'More than 1 item selected
    End If
  Next x

'Move selected item down if not already at the bottom
  If Position     'Add an item below the current selection
      ListBox1.AddItem ListBox1.List(Position), Position + 2
    'Remove Original Selection
      ListBox1.RemoveItem Position
    'Re-select the item that got moved
      ListBox1.Selected(Position + 1) = True
  End If

End Sub

Additional ResourcesAnything To Add?

I know there are a TON of things you can do with ListBoxes and if there are actions your are stuck trying to figure out, leave a comment below and I will try to add them to the guide. Please only ask for generic tasks and not super specific ones. Also, if there is a more simplistic way to carry out some of these tasks, let me know!

Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview