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:

• how to stop others from overwriting your drop-down lists
• improvements to Teams
• Excel in VR?!

If you were forwarded this message, you can get the free weekly email here.

💡 Keep Your Drop-Downs Safe: 4 Steps to Avoid Overwriting

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 Target As 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.

​

🤓 Geeky News

👥 OneDrive app in Teams

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.

🔎 Improved search in Teams

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.

🥽 Excel in virtual reality?

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 😁

📈 Pivot Tables on iPad

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.

​

💚 My Favorite Things

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.

​

👏 Power Stories

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

Want more?

▶️ 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!