As I mentioned, in my last post, I’ve been doing Excel development recently, so I thought I’d post a few procedures, to spread the wealth as they say. Most of these I created quite a few years ago for other projects that I revamped for this newest project.

This post is about Protecting and Unprotecting a worksheet or all the Worksheets in a workbook.

Protect/UnProtect All the Worksheets

Below are 2 procedures that loop through all the worksheets within a workbook and Protect/UnProtect them all using the same password.
Continue reading →

I’ve been doing a lot of Excel development to create several very advanced WorkSheet forms for data entry.

I’ve experienced some serious issues trying to use Data Validation in which Excel crashes randomly when the feature is used (and yes I tried an Office repair, and yes I tried it in Excel 2013 and 2016, …). Since I cannot deliver such an unstable product to my clients, I ended up switching over to using ActiveX Combo boxes.

Now, all that said, if you have a form, any form, you are going to need some sort of Reset button. Now you can reset each combo box individually by doing something along the lines of:

ActiveWorkbook.Worksheets("TimeSheet").YourComboBoxName.value = ""

but then if your design evolves, then you have to recode and that can becomes very tedious, time consuming and prone to mistakes and oversights!

Anyways, long story short, I wanted a simple function that would loop through all the combo boxes on a sheet and reset them all. I didn’t want to have know their names, how many of them existed, … I just wanted a function to handle it all. Below is the procedure I came up with.

Continue reading →