Arrow key scroll in Mac Excel

Ever been using Microsoft Excel on the Mac and found that when you use the arrow keys on the keyboard to move from one cell to the next, instead the whole worksheet scrolls?

This is actually a feature of Microsoft Excel carried over from the Windows version on which you press the Scroll lock key to enable or disable.  Older Macintosh computers had the F14 key mapped to the Scroll lock key of PC keyboard, so it was quite easy to enable or disable.  However newer Macintoshes no longer have a F14 key.

This makes disabling this feature tricky to say the least.

In the past I have attached an older Mac keyboard to my 2010 Macbook Pro and pressed the F14 key.  However for those of you who do not have ready access to such legacy keyboards, you can use a simple applescript application that I have written called Excel Scroll Lock.

What it does is “send” a Shift-F14 key press to Microsoft Excel as if you pressed the Scroll lock key, thus toggling on or off this function within Excel.

To use, simply download the application, unzip, and drag the application into your Applications folder.  Then run it.  You can alternatively download a disk image version of the application, and run without installing into your Applications folder.

For those interested in the applescript, it is shown below, and also included in the downloaded application bundle under the Contents/Resources/Scripts folder.  The script is also available on github – contributions welcome.

--Applescript to fix scroll-lock problem with Microsoft Excel on Mac OS
--Written by Damien Clark (https://damosworld.wordpress.com)
--Licenced under GPLv2 (http://www.gnu.org/licenses/gpl-2.0.html)
--18th of June, 2012

--From time to time you may find that using the cursor keys in MS Excel for Mac will 
--scroll the worksheet, but not shift the selected cell.  This is a feature of Excel 
--that is enabled by pressing the Scroll Lock key.  On Macintosh computers once upon 
--a time, F14 was the equivalent key to Scroll lock on the PC.  Modern Macintosh 
--computers are no longer furnished with an F14 key, and while I am sure there is a 
--keyboard combination to emulate F14, I have yet to find it.  As a result, you can 
--find yourself stuck with this special scroll lock mode of Excel and it is very 
--frustrating.  This script effectively "sends" an F14 keypress to Microsoft Excel, 
--thus turning on or off this scroll lock feature.  So if you find yourself in this 
--situation, you can execute this program to toggle the feature on and off as 
--necessary.  Simply run the application and click OK.  

set returnedItems to (display dialog "Press OK to send scroll lock keypress to Microsoft Excel or press Quit" with title "Excel Scroll-lock Fix" buttons {"Quit", "OK"} default button 2)
set buttonPressed to the button returned of returnedItems

if buttonPressed is "OK" then
    tell application "Microsoft Excel"
        activate --Make it the active application to receive System keyboard events
    end tell
    tell application "System Events"
        key code 107 using {shift down} --Send the F14 key which is scroll-lock on Mac keyboards
    end tell
    activate
    display dialog "Scroll Lock key sent to Microsoft Excel" with title "Mac Excel Scroll-lock Fix" buttons {"OK"}
end if

Macports: How to configure php5 for apache2

I guess I am spoiled with Redhat’s integration of all these packages where you simply install and away you go. Macports isn’t quite a polite in this regard. The default config files are incomplete and not enabled when it is installed.

So I have had to do some massaging to get php working.

The file /opt/local/apache2/conf/extra/mod_php.conf is missing some configuration parameters. I’ve changed mine to look like this:

<IfModule mod_php5.c>

AddType  application/x-httpd-php         .php
AddType  application/x-httpd-php-source  .phps

AddHandler php5-script .php
AddType text/html .php

DirectoryIndex index.php

</IfModule>

I specifically had to add the handler and also load the module. Then in the /opt/local/apache2/conf/httpd.conf file, I had to add the line: Include conf/extra/mod_php.conf as there wasn’t even a commented out line that would include this module.  Also in the /opt/local/apache2/conf/httpd.conf file, also need to add the line LoadModule php5_module modules/libphp5.so so that the php library is loaded into Apache.

A graceful on the server:

sudo /opt/local/apache2/bin/apachtctl graceful

and away we went.

Damien.

Addendum

Turns out that a message is printed at completion of the install, which was not apparent to me because I used a GUI to do the installation. The message states:

If this is your first install, you need to activate PHP in your web server.

To enable PHP in Apache, run
cd /opt/local/apache2/modules
/opt/local/apache2/bin/apxs -a -e -n “php5” libphp5.so

A useful tip I highlighted in a previous post helps to find this sort of information after installation.  This command simply adds a LoadModule option to the main httpd.conf file, but does not add the other configuration options above.  So you still need to make the above changes.

Macports: How to install mod_perl2

When attempting to install mod_perl2, I received an error code 2. Very unhelpful error message, but after some googling, I found this bug ticket which explains what you need to do.

Essentially, you need to forcibly uninstall perl5.12 (or perl5.10) and then re-install with the threads variant:

sudo port -f uninstall perl5.12
sudo port install perl5.12+threads

Then you can install mod_perl2 with:

sudo port install mod_perl2

Not a particularly elegant solution, but nonetheless gets the job done. Hopefully this issue will be fixed in the future.

Damien.

Macports: Some useful tricks

I have decided to jump ship from Fink over to Macports.  Out of the frying pan and into the fire perhaps, but Fink’s packages just don’t seem to be kept up to date as well as Macports.  I’m keeping a close eye on brew as well, although I too am skeptical of its dependence on Apple packaged software, especially when it comes to OS upgrades.

Anyway, here are some very handy tips that I have discovered in my travels.

Post-installation instructions

Some packages provide instructions on installation completion.  At times you may have ingored them or used tools such as Porticus to do the installation in which case the instructions are lost in the scroll from all the dependencies that went along with it.  So, there are a couple of commands that you can use to get this vital information back:

This command looks at the notes for a package (if it has any):

sudo port notes $packagename

If there are no notes, you can look at the portfile directly using:

sudo port cat $packagename

You will probably see some instructions to be printed at the end of the script

How do I start or stop daemon processes

So this always frustrates me, and each packaging system does it differently. Macports wraps some scripts around the launchd system of MacOS 10.4+. So you can use the launchctl load -w /Library/LaunchDaemons/${packagename}.plist to do it. But a more convenient way is to use the load/unload option to the port command.

sudo port load $packagename
sudo port unload $packagename

Sadly, this not only starts and stops the daemon process, but also enables or disables auto-startup at the same time. You can do things separately, or at least not without knowing the individual package’s startup scripts.

As I come up with new tips and tricks, I’ll add them to this blog post.

Damien.

Installation of Postgresql on MacOS X using Macports

Installing and configuring postgresql using Macports is a nightmare.  Documentation is very difficult to find.  Trawling the blogosphere, I have assembled the following procedure that I have had to follow to make it all happen, as it should.

To install postgresql84 on Snow Leopard (10.6) using Macports, execute the following commands:

sudo port install postgresql84-server
sudo mkdir -p /opt/local/var/db/postgresql84/defaultdb
sudo chown postgres:postgres /opt/local/var/db/postgresql84/defaultdb
sudo -u postgres /opt/local/lib/postgresql84/bin/initdb -D /opt/local/var/db/postgresql84/defaultdb

Now to make sure that the postgres user account can be executed using the su command, we need to give postgres a shell:

sudo dscl . -create /Users/postgres UserShell /bin/sh

Then merge the Sys V Shared Memory Kernel parameters from the /etc/sysctl.conf.pg file with your existing /etc/sysctl.conf file (if you already have one):

sudo sh -c "cat /etc/sysctl.conf.pg >>/etc/sysctl.conf"

You can reboot at this point so that the new Sys V shared memory configuration in /etc/sysctl.conf is loaded by the kernel.  Alternately, being a UNIX you can execute the following command to get you going right away:

sudo sysctl -w `cat /etc/sysctl.conf.pg`

Next, we need to create the logfile directory and make it writable by the postgres user:

sudo mkdir -p /opt/local/var/log/postgresql84
sudo chown postgres:postgres /opt/local/var/log/postgresql84

Next, add to the /opt/local/lib/postgresql84/bin directory to your path variable in either /etc/profile or ~/.profile and you should be set

export PATH=/opt/local/bin:/opt/local/sbin:/opt/local/lib/postgresql84/bin:$PATH

Now create yourself a postgres database user account

$ createuser `whoami`
Shall the new role be a superuser? (y/n) y

To make the database server accessible via a TCP socket, edit the /opt/local/var/db/postgresql84/defaultdb/pg_hba.conf file and add the following two lines:

host    all         all         127.0.0.1/32          md5
host    all         all         0.0.0.0/0                md5

Edit the /opt/local/var/db/postgresql84/defultdb/postgresql.conf file and add the following line:

listen_addresses = '*'

Now, we should be right to start the server.  We use MacOS’ launchctl command:

sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql84-server.plist

Now you can connect to the database server with:

psql template1
psql (8.4.7)
Type "help" for help.

template1=#

Hopefully this has been useful to someone else, as I have spent over 2 hours *&^%*&% around with this to get it working.

 

Damien.