Microsoft Excel does not wrap text in a merged cell. Solution.

 

The Background:

If you type more text than will fit in a cell, the contents will automatically spill over into the next cell:

However, if the next cell over has anything in it, then it won’t spill over:

Wrap Text to the rescue! Enable Wrap Text and your problem is solved, right?

 

The Problem:

What if you have merged cells that you want to wrap text with? You’d think it wouldn’t be a problem but even Excel 2010 can’t handle it:

It’s too big! And if you did manually shrink it, it won’t expand as you type in more text:

It is incredibly frustrating to have Microsoft Excel not wrap text properly just because there is a merged cell.

 

The Solution:

I found a few different plausible solutions including making a “sister/mock” cell in the same row that is not merged and whose width is slightly less than the merged cell. It didn’t work well for me and seemed like a sloppy workaround.

The best solution I found was VB code (don’t panic) that just makes everything work as it should out of the box.

Right-Click on your sheet tab and select View Code

Paste this code into the window and then close the window:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range
With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

Credit for this code goes to Greg Wilson (as it was given on the forum I found it)

That’s it!

Now as you make changes to a merged cell, it’s height will automatically increase or decrease as it should have in the first place.

Configure ISA to allow outbound internet access based on IP address, not username, password, or other credentials.

Recently I had to configure ISA 2006 to allow outbound internet access to a computer, regardless of who was logged in. 

The ISA configuration was set to only allow access to users that were specifically members of a Windows Active Directory group “Allow-Internet”.  This was done via web proxy clients and firewall clients.  The problem, then, is computers that are not logged in and running services (like email SMTP, FTP, etc) can not authenticate with the ISA to gain outbound internet access.

Likewise, a device that was not running windows would not be able to access the internet (maybe a hardware appliance).

This is a simple rule I added to permit access to those devices.

1. Open ISA Server Management

2. Navigate to Firewall Policy

If you can’t find it, make sure the left pane is open:

3. Create a new rule using these settings:

4. Click the From tab then click the Add button

5. The computer (or device) that you want to add is probably not listed so you’ll need to hit the New button and create a New Computer

6. Enter the IP address for the computer (or device) and a friendly name for your reference

7. Activate your changes by clicking the Apply button

Forward Email Headers in Microsoft Outlook

Have you ever received a complaint about an email issue that required you to view the email headers?

Maybe you’ve asked a user to forward you the email so you can take a look.  However all the email headers from the original email are now missing (heaven forbid that extra 1k of data be in the message)!

Sometimes I try to get users to use the Insert Item button to add a message but believe it or not this is sometimes too difficult for the end user. Instead, I have them change the option in Outlook (2003 in my case) to forward the entire email. The forwarded email will then show up as an attachment:

1. From Outlook, click Tools > Options

2. Click the Email Options button

3. Under When forwarding a message click the drop-down and select Attach original message

That’s it.

Upgrade notepad with this great notepad replacement

If you ever use Start > Run > notepad.exe then you owe it to yourself to try this notepad replacement.

It’s called Notepad++ and it does everything notepad does but better. 

http://sourceforge.net/projects/notepad-plus/

After you download, install and try this wonder, if you’re like me you’ll want to replace notepad.exe but it’s not too easy at face value.  First I tried just replacing the notepad.exe with the EXE from notepad++ but windows security is rather ugly and the EXE doesn’t have it’s supporting files nearby.

Instead, I used a small executable that does this job nicely:

http://www.ohloh.net/p/npplauncher
http://notepad-plus.sourceforge.net/commun/misc/NppLauncher.bin.zip

In my case (Windows 7), I had to replaced the notepad.exe under the \windows\system32\ folder.  But that first required taking ownership of the notepad.exe file:

To take ownership of a file:

Go to c:\windows\system32 and then the properties of notepad.exe:

The Security tab:

Then Advanced:

Then the Owner tab:

Then click the Edit button and click your username then OK to change the ownership of the file to yourself.

Now you should be able to rename the existing notepad.exe to something like notepadbackup.exe.

This now makes it possible to copy the notepad.exe that you already downloaded from here and place it in c:\windows\system32.

Now every reference to notepad.exe (most importantly, the one that I use, start > run > notepad.exe) should launch Notepad++!

 

I’ve read that some people also need to do this to the notepad.exe file that exists in these locations but I didn’t have to:

\Windows\ServicePackFiles\i386
\Windows\System32\dllcache
\Windows

“Remember Password” Setting Not Working for Sharepoint

Here’s one that’s been a thorn for months that I finally found a solution to.  Since I’ll probably forget it before I need it again…

If you try to access a Sharepoint site (3.0 in my case) by mapping a drive letter or access it directly, maybe you’ll be confronted with this one:

“The operation being requested was not performed because the user has not been authenticated.”

 

 

 

Or this little gem:

It kind of gives you the impression that if you check “Remember my credentials” that you’ll never have to type your password in again, doesn’t it?

I had to employ two solutions in order to completely wrap this issue up:

1. Configure Internet Explorer to stop asking for passwords

This setting allowed me to finally remember the password when trying to open the sharepoint site (or links to files on the sharepoint site) from within Internet Exploer.  Navigate to Internet Options > Security then click Custom Level on the Internet zone:

Scroll all the way down to User Authentication and notice that by default it’s set for Automatic logon only in Intranet zone.  You could either add your sharepoint site to your intranet zone or change the setting here to  Automatic logon with current user name and password. 

I suppose if your windows credentials are different than the sharepoint server you are using you may want to try the windows password vault (or credential manager or whatever they call it).  In my case my username and password were the same, but my domain name was different.

This was enough for me to open the sharepoint website just fine (without being prompted for the password) but I was still getting the password prompt when trying to open links to files stored on sharepoint and also when using mapped drives to DavWWWRoot and others.  In this case, step 2 came to the rescue.

2. Tell Windows to allow the WebClient service to remember passwords

Maybe you haven’t heard of the WebClient service but it’s playing a role in this password issue.  It requires the addition of a registry key but it works great!  Navigate to this registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WebClient\Parameters

Click Edit > New > Multi-String Value and enter the name:

AuthForwardServerList

For the Value enter the URL of the sharepoint site.  For Example, I used:

http://companyweb.mydomain.com

However these examples may be more applicable to you:

https://*.yourdomain.com
*.moredomains.net
http://192.168.1.100

After you do this, save yourself a reboot and restart the WebClient service (e.g. net stop WebClient && net start WebClient).  Or maybe you like rebooting so feel free to do that as well.

 

That’s all it took for me to finally get this bug out of my hair.  I suppose the amount of time I spent trying to get this fixed exceeds the time it would have taken me to type in the passwords for the next 10 years but it’s a matter of principle!