Hi there! You're reading Between the Sheets. This week we'll dive into a slightly forgotten, but still powerful skill. Besides, we're covering:
If you were forwarded this message, you can get the free weekly email here.
Visual Basic for Applications (VBA) might seem these days like a relic of the past. It's true that the rise of cloud collaboration through platforms like SharePoint and OneDrive has somewhat sidelined VBA, especially since macro-enabled files (.xlsm) aren't compatible with Excel Online.
Tools like Power Query and Office Scripts have taken over some tasks that used to be the domain of VBA, owing to their ease of use and integration with modern file formats.
However, don't be too quick to dismiss VBA. Often, when searching for solutions to particular Excel issues, you'll find that VBA scripts are your most effective - sometimes only - option.
Whether you turn to online forums or use ChatGPT (trained on insights from those very forums), you'll discover pre-made VBA scripts that you can easily adapt to your needs. The key is understanding where to paste them and what parts can be modified.
Let's demystify the process with a practical example.
Scenario: You've prepared a wonderful Excel template with drop-down lists for your team. But soon you find out someone has overwritten them. Unfortunately, data validation is not foolproof. It can be pasted over.
Solution: A simple VBA script can prevent this by disabling the paste action for specific cell ranges.
Here's how to implement it:
Step 1: Open the VBA Editor
Right-click on the sheet name of the Excel worksheet you have your drop-downs on.
Select "View Code" from the context menu.
This opens the Visual Basic Editor where you can work with VBA code.
You should see a new window appear on the right side (see image below).
Step 2: Paste the code
Paste the following code in the code window:
Private Sub
Worksheet_SelectionChange(ByVal
TargetAs
Range)If Not
Application.Intersect(Target, Range("B2:B8"
))Is Nothing Then
Application.CutCopyMode =False
End If
End Sub
Step 3: Adjust the range
Adjust the range in the copied code. In the above example, we're using "B2:B8" as a placeholder as we have our drop-downs in this range. Replace this with cell references that match the data validation range in your file - basically the cells for which you want to disable the copy/paste action.
Follow the same format: inside double quotes, range start and end separated with a colon.
Step 4: Save the file and test
After pasting and adjusting the code, simply close the Visual Basic Editor. Then proceed to save your workbook - but not the usual way, because now your file contains VBA code, so you'll have to save it as a macro-enabled file - otherwise your code with be cleared.
Go to Save As and select macro-enabled file (.xlsmโ).
Test the functionality by trying to copy-paste data into the protected range. The action should be disabled, which keeps your data validations safe. You can still make selections, but no one can paste over them.
๐ Congratulations! You've now reused a ready-made VBA script to prevent overwriting data validation lists in Excel. It's not as complicated as it may seem, and with a little practice, you can solve various Excel issues using VBA.
โ
A while ago we brought your attention to OneDrive improvements. Now you're getting the same user-friendly experience in Teams.You may have noticed a new app icon on the Teams sidebar, with OneDrive replacing 'Files'.
This app streamlines access to created and shared content within Microsoft 365. It introduces new views and filters for efficient file management and retrieval.
If you don't recall in which chat or channel a specific file was shared, you will find it in the OneDrive app. You can filter by people, file type, or meeting to speed up the search.
You will also find and share your work from Microsoft Lists, Loop, Power BI or Whiteboard. All in one centralized place.
You can benefit from improved search not only for files but for messages too. The Search experience in Teams has been overhauled.
The channel information pane now includes a "find in channel" button. In a chat, you can simply use the shortcut Ctrl+F. The results will be displayed in a pop-up pane on the right side of the screen.
When you select a search result, you are taken to that specific message in the channel or chat, including the full context of the conversation, which was missing in the old experience.
The search bar on top of Teams now also includes domain-specific filters like "files", โgroup chatsโ or โteams and channelsโ to help narrow down results.
Meta has added Word, PowerPoint, and Excel to its Quest VR headsets. You can now use the Office web apps using virtual reality, controlling it with hand movements. Apparently, the experience leaves a lot to be desired, at least for now.
But who knows, maybe in the future, all we need is a VR headset and we can work with our virtual computers and keyboards to do our jobs. When I first thought of VR and Excel, I thought of landing in an Excel cell and jumping from one cell to another running away from a potential spilled range ๐
If VR is a bit too far fetched but you do use Excel on iPad, you may be happy to know that it now supports pivot tables.
This enhancement allows users to calculate, summarize, and analyze data, specifically optimized for the iPad's smaller screen and touch interface. Start by inserting a pivot table, then tailor it using the field list, adjust source data, and fine-tune settings through intuitive interfaces.
It could be particularly useful if you're presenting summary reports on the go.
โ
I'm currently reading two books. One is The Power of Now by Eckhart Tolle, which I started last week. It's been out for many years, and I've heard about it from multiple people so I decided to give it a go.
I'm only half-way through but I've had many aha moments, especially about meditation. Before, meditation for me was just being quiet and focusing on my breath. If I think of something, which I do after 5 seconds, then I bring my attention back to the breath. I never thought about what "not-thinking" really means or why it's important. This book opened my eyes. It's not something you read quickly from beginning to end. You need to take your time. If you're seeking balance, I recommend it.
The other book is Microsoft 365 Excel: The Only App that Matters by Mike Girvin. I've been reading it on and off since it was released. It's like a story-filled encyclopedia of Excel, showing not just features but how they fit into the bigger picture. It's so informative that I had to make it available on our site, along with all the downloadable content. You can access the ebook version here, thanks to a collaboration with the publisher and my Microsoft MVP friend, Mr.Excel Bill Jelen.
โ
VBA still has its place and just like any tool, it has an easy side and an advanced side. The easy part, like the example we saw above can fix annoying problems.
Our Unlock Excel VBA & Excel course shows that VBA is still important. Many new learners keep joining the course, proving that people still value VBA a lot.
Recently, Riccardo enrolled in the course. It's fantastic to see him acquiring essential skills right from the start.
And big congratulations to Paul who has already successfully completed the course ๐
I'm always happy to hear about the real-world impact of the skills taught in our courses.
Got a success story about using your XelPlus skills? Iโd love to hear it! Hit reply to share the details, and inspire other students. ๐
See you next week,
Leila
โถ๏ธ Subscribe on YouTubeโ
๐๏ธ Follow us on LinkedInโ
๐ฅ Join 400,000+ students in our coursesโ
๐ฃ Want to sponsor Between the Sheets? Get in touch here.
This newsletter contains affiliate links, which give us a small commission on any purchase made at no cost to you. This helps us run Between the Sheets and bring you updates like this. Thank you for your support!
XelPlus is a leading online education company, providing training courses for Excel, Power BI, Finance, and Google Sheets. XelPlusโ bestselling courses are popular among financial analysts, CFOโs, and business owners. Technology is changing fast. We help our members turn confusion into confidence with every skill learnt.
I hope you've had a good summer so far. I just got back from a fantastic vacation in Croatia, and I have to say, itโs a beautiful country! The clear seas and delicious food were amazing. But what I loved most was the smell of figs as you walked down the streets. Fig trees are everywhere! Every morning, Iโd grab a few for breakfast as we walked our dog. Right now, I'm back at the office with a coffee by my side. Ready to start the next video recordings for our new Pivot Table mastery course....
Oh, the dread of an overflowing Inbox after a holiday... ๐ซ We've all been there. But it doesn't have to be that way. ๐ง Combat email backlog with these Outlook features Coming back to a flood of emails after a vacation can be overwhelming. I've put together some practical tips to manage your inbox using Microsoft Outlook. Outlook "Classic" that is - I'm still not sold on the "New" one. Before you leave... Auto-responder end date I'm assuming you set up your Out of Office message (File >...
Analyzing data is all about sums and percentages. Hold on! Sometimes you need a good old count. For example: Count transactions in Region X in August. Count items in stock that fall below a threshold quantity and belong to category X. Count the number of customers in Segment A & B. You get the idea. Well, there's a simple Excel function that can do all this. ๐งฎ COUNTIFS - Are You Doing it Right? The COUNTIFS function counts the number of cells in a range that meet one or more conditions....