Enabling Outlining Commands on a Protected Worksheet

I have a financial model that I set up using a grouping in some key places so that I could collapse sections of the model when I didn't want to look at them. As I was handing off the model to someone else to work with, I wanted to protect the worksheets, but unfortunately there is no setting in the user interface to allow for expanding/collapsing the outlining tools when the sheet is protected. In fact, trying to do so gives you the following message:

I found this a little frustrating, but gave up on it. I expanded the model completely, protected the sheets and let the users have at 'er.

Tonight at VBAExpress.com though, I was posting on a thread where the user had included the following in their code:

Sh.EnableOutlining = True

Wow! So obviously there IS a way to enable the outlining tools when the worksheet is protected, right? I ran the macro I had modified for the user and sure enough it worked. Cool!

So then I opened up a copy of my model and:

  • Ran the following code: Activesheet.EnableOutlining = True
  • Protected the worksheet

I didn't work. What the hell?

After a little sleuthing I found out what the issue was. In order for the EnableOutlining to take effect, you must run the code that protects your worksheet with the userinterfaceonly:=true argument.

The unfortunate part of this is that userinterfaceonly:=true doesn't stick between sessions. So that nice macro free workbook is now going to have to be saved into an xlsm format with the following code in it:

Private Sub Workbook_Open()

Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets

With ws

.Protect userinterfaceonly:=True

.EnableOutlining = True

End With

Next ws

Application.ScreenUpdating = True

End Sub


That shouldn't be necessary in my opinion, but whatever. A macro laden file is a small price to pay for the functionality. Man I love VBA!

Built in Ribbon Customizations in Office 2010

I know it's been forever since I've blogged here, and I'm sorry to have to revive this with a rant, but…

I was writing an article up for how to customize the Office Ribbon in Excel 2010, and thought I'd build a custom Auditing Tab. Using the built in groups is REALLY easy, but there were commands there I didn't need. So I figured that I'd try to make some custom groups, insert the commands I wanted, and see how it worked. Even better, I know that you can now export the customizations, so I could even share them!

To build a custom Ribbon tab:

  • Right click the Ribbon and choose "Customize Ribbon", or go to FileàExcel OptionsàCustomize Ribbon.
  • Click New Tab
  • Select the tab and choose Rename
  • Go to the "Choose commands from" dropdown and choose Main Tabs
  • Find the groups you like, select them and click the Add>> button

To add a new group to a tab, first select the tab then:

  • Click New Group
  • Select the group and choose Rename
  • Drill down to the commands you like (left window), select them and click the Add>> button

Overall it's not too complicated really.

So here's the setup I did for my Auditing Tab… (I shrunk it so that you only see stuff pertaining to that tab):

As you can see, I created custom Formula Auditing, Other Tools and Sort & Filter groups. (I don't know why I use the default Sort & Filter as well a custom one, but there you have it. At any rate, it doesn't change this… the tab looks like crap!

The icons for Trace Precedents, Trace Dependents and Remove Arrows are all large and very blocky looking. Same with the Sort buttons on the custom group. Why is it that they are shown nice and small on the built in group, but not the custom one? And isn't it interesting how Microsoft broke the 1 or 3 to a row rule with the Sort group? Makes it very obvious here!

If you want the customization file to try this yourself, click here.

Personally, I'm happy enough with the way the built-in groups work, but I think there should have been a bit more control on the sizing of the icons here. It seems that the need for my book (RibbonX – Customizing the Office 2007 Ribbon) isn't dead yet, but honestly, it should be!

The danger of being out of date…

Tonight I discovered that my site had been hacked... as it turns out I was running an old version of WordPress and there was some injection code fed into a security vulnerability. I've upgraded to the latest version, which has cured that issue, but in the process some things seem to have occurred:

  • I've got some funny looking code samples
  • I've got a weird A character showing up all over
  • I've lost my navigation to the RibbonX TOC

I'll try and fix this up over the next few weeks (kind of busy), but my main priority is going to have to be my main site, which still has some issues and needs a pretty major upgrade...

Flowchart Programming

This has to be one of the weirdest little programs I've ever worked with...

Back in 2003 I built a database to track our liquor inventories at work. It's fairly simple in that it has a table of product details like name, price, size, cost, etc... and a table that records my inventory counts. We used a little Palm Pilot with a built in Symbol scanner to collect the scan date, UPC code and quantity on hand. As we estimate our partial bottles that sit on the bar, we record decimal quantities on hand. (i.e. 2.1 bottles.)

This has worked really well for the last few years, but as with all technology, it wears out. While the database is fine, the Palm Pilot is another matter. The screen calibration went so far off and despite several resets I couldn't fix it, rendering it useless. The bad news, of course, is that the unit we used has been discontinued for about 2 years, and my last replacement unit... well... it's now in the trash.

Fortunately I already had a new device on hand to use. We'd installed a new system in another department last year that came with a handheld ruggedized scanner. Very nice little unit made by Unitech (HT-630). We were so impressed that we bought a second one. One reason was that we now have a backup unit, but the other is that the vendor (whom I know fairly well) convinced me that I could use this as a replacement for my aging Palm units. "Oh yeah," he told me, "it's fully programmable. You'll have no problem knocking up a little program for it. There's a whole programming environment, you build the job, upload it and you're good to go. It won't be a problem for you."

I love that people have that much faith in me sometimes... then there's other times where I want to phone them up and...

At any rate, I've been meaning to get around to programming this new unit, but just haven't been able to make it a priority. But since our little Palm Pilot blew up, that kind of forced the issue. And naturally it had to happen two days before quarter end. Great. So last Thursday/Friday I spent time trying to get this thing working in time for them to count inventory on Saturday. Nothing like a little pressure!

The odd thing is that this unit is programmed by building a flowchart. I've never seen the like before. To me flowcharts are used for documenting how the program works, not telling the program how to work. Very odd. The shot below is from the program I released on Friday to count inventory. (Yeah... I DID do it, but I wouldn't say it wasn't a problem!)

This was a real trip to work with. Ever seen a flowchart that is allowed to go multiple directions at once? I haven't. To me flowcharts have a decision point between every directional choice, but this little program actually flows data along one arrow and the input screens along another one.

And well creating the flowchart is all good, you then have to configure every option of every node in the chart. You can't copy an existing node that is setup correctly either... each option of each box has to be configured. Each link, each input, everything. This is just one of at least 14 screens to configure. Each line you see (incoming and outgoing) increases that count by 1, (image below has 20 screens to do) so it's very tedious:

It took me about 1½ days to built this program. I spent a ton of time spinning my wheels trying to figure out how to make a lookup file work, then trying to figure out how to write the data to the text file, then trying to figure out how to upload the program to the unit, and then I thought I had it. Then I reviewed the collection file and figure out that it was submitting a full record every time a single piece was captured. So I'd get four records for every UPC code, one with the correct quantity, three with the prior... arrggh!

I also capture the count date and month end date. I was really happy to see that you could use a formula too, so I figured that I could use a formula to convert the count date to the month end date. I quickly jumped into the formula screen... and after staring at that for about 4 seconds I clicked the Help button:

Seriously? "Write pieces of C code"? Try web searching for some help there... even when I did find something, I couldn't figure out how to make any sense of it!

In Excel this would be easy.... =If(Day(CountDate)<15,Eomonth(CountDate,-1),EOMonth(CountDate,0))

If anyone has any idea how to write that in C code, I'd be really grateful. There is no manual with this software and the "Help" files are about as useful as... well... they're not. I ended up ignoring the formula and elected to capture the count date twice. I just have to edit the text file and replace one of them with the month end date right now, and I'm good to go.

At any rate, I DID get the program working in time, and deployed it out there. The staff are ecstatic as it is much simpler to use than the last one. Yay, it's time to celebrate!


Today I was trying to import the text file into the existing table in Access. Access is causing me a hell of a hassle as it wants to populate the AutoNumber index field with my text file data. I don't get that. It was never an issue before. I even put headers in my data fields, but no luck. I may have to resort to VBA to use a SQL insert statement in a loop to get this licked.

Worse though is that I messed something up. All of the decimal counts have been truncated to whole numbers, which makes my count file useless. I HATE having to deploy new technology in a hurry without proper testing. Our only recourse now is to estimate our inventory this month end and deal with fixing the technology issue for next month end.

At any rate, this has been an interesting learning experience. Programming C using flowcharts... it's just plain weird.

Thanks a lot, macro recorder!

I think it's pretty well known that the Excel macro doesn't always record code when you'd like it to.  I know that there's been several times I've recorded something to get a syntax, and it's been an empty stub after I'm done.  This one was a new one to me though...

I tried to record the creation of a conditional format in 2007 and here's what I got:

[vb]Sub Macro4()
' Macro4 Macro

Selection.FormatConditions.Add Type := xlExpression, Formula1 :=
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub[/vb]

The first line was even highlighted red. Seriously, it could get the rest, but not the formula?

Windows 7 install

Last night I decided to install Windows7 RC on a spare laptop hard drive. So I pulled my Vista drive out of the laptop, slipped in the other drive and away I went.

For reference, I found the install very slow compared to XP or Vista. It seemed to take forever and a day to install it. Oh, and just as a note, you need to license code before it will boot into Windows (duh!). I had to run to my wife's computer to grab a code from MSDN as I kind of forgot that little part.

I'd already installed this on a desktop PC, and found some interesting new features that look nice:

  • Screen Magnifier tool (no more need for the magnifier that comes with my mouse software?)
  • Sticky Notes (stick right on the desktop)
  • Live Preview of application pages/windows from the task bar
  • Clipping tools (competes with SnagIt/GrabIt)

Interestingly enough the screen magnifier and live preview did NOT work well on my laptop, where they did work on the desktop.

I'll also say that the version of IE8 included with Win7 sucks. On both the desktop and laptop it crashed repeatedly. I ended up restarting IE8, clearing 2 error messages and going straight to Firefox.com to download a useable web browser.

Windows Live Mesh will not install on Windows7 at this time. That's a bit of a problem for me as I keep some pretty critical documents there to make sure I have a backup of them.

Upon shutting the laptop down last night I also ended up triggering the blue screen of death… I haven't actually seen one of those in a long time.

Of course, I had to work today, so I pulled the Windows 7 drive out and put my old Vista one back in… and just about had a heart attack! The BitLocker drive encryption screen came up. I've never actually seen that before, as it doesn't actually appear to do anything when you turn it on. The momentary panic passed though when I verified that the key I had tucked away was the right one, and I'm up and running again.

Brutal Date Format

We recently updated our main property management system and are now going through the first month since. Over and over again now I'm running up against an issue in the way the vendor decided to start treating dates. In the past they just used "MMM DD', which was fine. These converted into valid dates in Excel that assumed they were in the current year. All good!

In the recent update, the vendor decided to add the year, but not in a good way. Now my data looks like this:





Unfortunately this does NOT translate well into a date format automatically. You'd think that it would convert when you pulled it through the text import wizard, but it doesn't. Had the dates had 2 digit days consistently, or a delimiter of some kind between the month and day I believe it would have worked. As it is, the MDY format messes up the dates with 2 digit days and ignores the single digit days completely, giving me an even bigger mess. But pulling them in as text means that they can't be used to drive date dependant formulas, and sort like this:







This is very irritating, and many users would tell you that this isn't trivial to fix. I worked up the following formula so that I could convert the dates into real dates:

=DATEVALUE(LEFT(A9,3)&" "&MID(A9,4,FIND("/",A9,1)-4)&", "&2000+RIGHT(A9,2))

(It assumes that the date is in A9)

This works by feeding the DateValue function the date in a "MMM DD, YYYY" format, which it can interpret. Here's the breakdown:

  • Month: LEFT(A9,3) &" " returns the left 3 characters of the text string followed by a single space
  • Day: MID(A9,4,FIND("/",A9,1)-4)&", " returns the string in the middle starting with the 4th character, returning the number of characters between the slash and the 3rd character, plus a comma and a space
  • Year: 2000+RIGHT(A9,2) returns 2000 plus the right two characters

Hopefully we're not using this spreadsheet in 2100... I can safely say that it won't be my issue if we are. J

We have lodged a complaint with the vendor as this is stupid. Any financial program that exports financial data should export dates in a format that is compliant with the biggest spreadsheet program out there. I should not have had to waste time writing a formula like that above.

Very tired of snow…

It's snowing here… again. We've had more snow this year than I can ever remember in Nanaimo, and it's getting really old. I don't anticipate that this snow will stick, but it's still irritating. So far it has snowed at the beginning of Jan, Feb, Mar and now April, with our first big snowfall of the winter being in mid December. Honestly, there is still a little bit of snow by the stop sign on my street from the FIRST snowfall. Ridiculous.

The bigger part of irritation for me is that it is coming during every month end reporting period, causing us grief on getting to work. Yesterday, as we're busy preparing for our quarter end (which has an EXTREMELY tight deadline), high winds knocked the power out… and now it's snowing.


Oh well, we'll get through it. We always do, but I think it's time for spring to come along now!

Breaking Links in Excel

So during my infatuation with converting my workbook to use IFERROR statements throughout, I suddenly realized that the reports I had to send to head office needed to work in Excel 2003. After a bit of… um… motivational speaking… I elected to do the following:

  • Create a new workbook
  • Copy each of the required worksheets to the new workbook
  • Break the links

This method would allow me to take full advantage of Excel 2007's additional functionality, speed up my calc time by using more efficient formulas, and also still provide head office with the reports they need. And easy enough to do as well, since it's just a bit of VBA code to automate it all… right?

Yeah, right. Nothing is ever as easy as it should be.

Copying the worksheets to a new workbook was simple, but the code to break the links is bizarre. I used the code that I'd posted on this blog a while back, but I had to do it in a weird way. Here's the method I ended up using:

  • A loop to copy the worksheets to the new workbook
  • A call to break links within the loop
  • Save and close the workbook
  • Re-open the workbook and break the links
  • Save and close the workbook

Seems a little redundant, doesn't it?

The reason I ran the link breaking within the loop that I was using to copy the worksheets to a new workbook is because is dramatically improved the speed of the routine. I can only surmise that it did break some links, as it certainly didn't do them all.

After that I tried to break the links with the workbook still open (saved or not), and it didn't have any effect. Basically the only way I found to reliably kill those links was to save, close and re-open the workbook. Very irritating and slow, but effective.

Of course, as trying to work this out wasn't hard enough, I was also battling this issue, which was leaving me with phantom VBA projects. I'm virtually certain that this was affecting some of the code, although I still had to resort to the above method once I had uninstalled that add-in.

My conclusion… breaking links on an unsaved workbook is hokey. This should work.

And just in case anyone wants it, here's the routine I used to break the links:

[vb]Private Sub Util_BreakWorkbookLinks(wb As Workbook)
'Date Created : 3/18/2009 08:36
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Break all links in the workbook

Dim vLinks As Variant
Dim lLink As Long

On Error Resume Next
vLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
If Not vLinks = vbNullString Then
' Break all links in the active workbook.
For lLink = LBound(vLinks) To UBound(vLinks)
wb.BreakLink _
Name:=vLinks(lLink), _
Next lLink
End If
vLinks = Nothing
On Error GoTo 0
End Sub

What I want from Outlook…

In a word, linked sub-tasks. I use Outlook every day for my task list, my calendar and my email. I spend hours in the this program and, while I've fully aware I could always use more training in it, I don't think I'm a total dummy in it's use either. (Glad to be proven wrong though!)

Ultimately, what I want from Outlook is an EASY way to manage the projects that I work on. I don't want Microsoft Project and I don't want to have to go to the hassle of setting up Sharepoint for a one-off project. (Steve French showed me a cool trick for doing pushing repeated tasks into Outlook from Sharepoint, but that's overkill here.) The big issue that I run into with Outlook is that I can't link the minor milestones to the overall project goal.

What I do now is create a task, and within it create a table of what needs to be done and the status, like this one that I'm working on now:

This has a few inherent problems. The very first is that the Due Date is what shows in my calendar on the task view. As you can see below, where the work week starts on Sunday, the task is showing up on Friday (the 20th). I have to complete a lot of these tasks as I go along through the week. So if I don't want an overdue task in my calendar, I have to update that field every day.

What I want is to be able to create all of the milestones into "Sub Tasks", then link them to the main task. When I click on the main task, I should be able to get a status update that shows me exactly what I'm seeing above in the first image… Okay, maybe not subgroup like I've done the "Build Statement" tasks, but I could preface each of those as something if I wanted. With proper sub-tasking, I could then manage my calendar.

Honestly, my needs aren't that big… I just want a central place to manage related tasks as compared to those that aren't. Surely this is something that should be native in Outlook as a "productivity" app, shouldn't it?