Monthly Archives: August 2014

Microsoft Word Content Controls and VBA

I’ve been developing some “smart” forms a government department in the last few months. These forms had to be developed in good old MS Word. Now, Word is not really suited to creating tightly controlled forms, but, in my case it’s all I had to work with and I’ve had to work within the limitations presented to me. The only real solution available was to use Word Content Controls and then to protect portions of the document that users shouldn’t be able to edit. I’ve managed to get things working fairly well and thought it worth sharing a bit of code I’ve developed along the way that should help anyone working with Microsoft Word Content Controls and VBA.

Data Validation

If you’re doing forms then data validation is critical. It’s pretty straightforward if you’re working with content controls if you make use of the ContentControlOnExit event. For example, here’s a bit of basic validation that ensures the value entered into a field is currency:

Private Sub Document_ContentControlOnExit(ByVal CC As contentControl, Cancel As Boolean)
  Dim sngTotalCost As Single
  Dim oCC As contentControl
  Select Case CC.Tag
    Case "sTotalCost"
      If Not validateCurrency(CC.Range.Text) Then
        Cancel = True
        Beep
        CC.Range.Select
        Exit Sub
      Else
        CC.Range.Text = Format(parseCurrency(CC.Range.Text), "$#,###0.00")
      End If
  End Select
End Sub

Public Function validateCurrency(sValue As String) As Boolean
  Dim iLoop As Integer
  Dim bReturn As Boolean
  Dim iAsc As Integer
  
  On Error GoTo errorHandler
  
  bReturn = False
  
  validateCurrency = bReturn
  
  sValue = Trim(sValue)
  sValue = Replace(sValue, "$", "")
  sValue = Replace(sValue, ",", "")
  
  If Len(sValue) = 0 Then
    validateCurrency = True
    Exit Function
  End If
  
  For iLoop = 1 To Len(sValue)
    iAsc = Asc(Mid(sValue, iLoop))
    
    If iAsc = Asc(".") Or (iAsc >= Asc("0") And iAsc <= Asc("9")) Then
    
    Else
      Exit Function
    End If
  Next iLoop
  validateCurrency = True
  Exit Function
errorHandler:
  MsgBox "An error has occurred" & vbCrLf & "Module: ThisDocument" & vbCrLf & "Procedure: validateCurrency" & vbCrLf & "Error Number: " & Err.Number & vbCrLf & "Description: " & Err.Description, vbOKOnly
  Err.Clear
End Function

Public Function parseCurrency(sValue As String) As Single
  Dim iLoop As Integer
  Dim iAsc As Integer
  On Error GoTo errorHandler
  parseCurrency = 0
  
  sValue = Trim(sValue)
  sValue = Replace(sValue, "$", "")
  sValue = Replace(sValue, ",", "")
  
  If Len(sValue) = 0 Then
    parseCurrency = 0
    Exit Function
  End If
  
  For iLoop = 1 To Len(sValue)
    iAsc = Asc(Mid(sValue, iLoop))
    
    If iAsc = Asc(".") Or (iAsc >= Asc("0") And iAsc <= Asc("9")) Then
    
    Else
      Exit Function
    End If
  Next iLoop
  parseCurrency = Round(CSng(sValue), 2)
  Exit Function
errorHandler:
  MsgBox "An error has occurred" & vbCrLf & "Module: ThisDocument" & vbCrLf & "Procedure: parseCurrency" & vbCrLf & "Error Number: " & Err.Number & vbCrLf & "Description: " & Err.Description, vbOKOnly
  Err.Clear
End Function

In this code we simply check the tag of each content control as users move to the next one. Content control tags are set on the Developer tab from within Word. If the control has a tag we’re interested in then the value (Range.Text) is run through the ValidateCurrency function. If it is valid then the parseCurrency function is used to format the value correctly and write it back to the content control contents. If the value entered isn’t valid then a user alert is raised and the focus is returned to the content control

Excel Style “Automatic” Calculations

If you’ve got data validation sorted out it’s a simple step to have read only fields in your forms whose values are derived from user entered fields. This bit of code takes the value of a currency field, multiplies it by 1.1 and writes the value into a second field.

Private Sub Document_ContentControlOnExit(ByVal CC As contentControl, Cancel As Boolean)
  Dim sngTotalCost As Single
  Dim oCC As contentControl
  Select Case CC.Tag
    Case "sTotalCost"
      If Not validateCurrency(CC.Range.Text) Then
        Cancel = True
        Beep
        CC.Range.Select
        Exit Sub
      Else
        CC.Range.Text = Format(parseCurrency(CC.Range.Text), "$#,###0.00")
      End If
      Set oCC = ActiveDocument.SelectContentControlsByTag("sTotalCost").Item(1)
      sngTotalCost = parseCurrency(oCC.Range.Text)
      
      Set oCC = ActiveDocument.SelectContentControlsByTag("sTotalCostGST").Item(1)
      With oCC
        .LockContents = False
        .Range.Text = Format(sngTotalCost * 1.1, "$#,###0.00")
        .LockContents = True
      End With
  End Select
  Set oCC = Nothing

End Sub 

It’s pretty simple to see what’s happening here. The value in the content control with the tag sTotalCost is validated for currency, and if a correct value the number has a calculation applied to it and the resultant value is written to a second content control with the tag sTotalCostGST. Note that I use the LockContents method to be able to write to the second content control and then use it again to make it read only.

Changing Document Format Based on Content Control Value

The last thing I’ll share is using the value in a content control to change some format in a Word document. In this case I simply set the font color of the content control based on the value selected in the control

Private Sub Document_ContentControlOnExit(ByVal CC As contentControl, Cancel As Boolean)
  Dim sngTotalCost As Single
  Dim oCC As contentControl
  Select Case CC.Tag
    Case "sTag1", "sTag2", "sTag3"
      If CC.Range.Text = "Yes" Then
        CC.Range.Font.ColorIndex = wdGreen
      End If
      If CC.Range.Text = "No" Then
        CC.Range.Font.ColorIndex = wdRed
      End If
  End Select
  Set oCC = Nothing
End Sub

These content controls were of the dropdown list type. When “Yes” is selected the font color is set to green with the ColorIndex property. When set to “No” the Font.ColorIndex is set to Red. Pretty simple.

How to Always Know the IP Address of your Home or Office Modem

A lot of home broadband plans in Australia do not offer a fixed IP address (I am not sure about plans in other countries). This can be troublesome if you want to get remote access to your home PCs with something like TightVNC or if you want to serve a little web page off of your home computers. It can also be a pain in the ass if you want to lock down access to remote servers based on IP address. I’ve worked up a little system of my own than allows me to work out what the currently assigned IP address is for my modem at home regardless of where I am in the world. Doing the same yourself is surprisingly simple. All you need is:

  • A home based server running PHP that can run timed jobs with CRON or some equivalent windows based program.
  • A remote web server running PHP.

The system uses three php files and one text file.

File 1 : check-and-update-ip.php

The first, which I’ll call check-and-update-ip.php resides on your local home server and is executed periodically either with CRON or some equivalent windows program. I’ve got my file to run every 30 minutes, so worse case if I need to access my home network and the assigned IP address has just changed I’ll have to wait a maximum of 30 minutes for the update. Here’s the contents of check-and-update-ip.php

<?php
// check-and-update-ip.php
//
// This file calls a file on the remote server which returns the $_SERVER['REMOTE_ADDR'] value.
// This value is compared with a stored value and if different written back to a stored value
  $remote_host="http://remote-host-address.com/file-location";
  $current_remote_ip=file_get_contents($remote_host."/remote-ip.php");  
	
	$last_remote_ip=file_get_contents($remote_host."/last-ip.txt");
	
	if ($current_remote_ip<>$last_remote_ip)
  {
			echo $remote_host."/write-ip.php?ip=".$current_remote_ip;
	  	$write_ip=file_get_contents($remote_host."/write-ip.php?ip=".$current_remote_ip);
			
			echo $write_ip;
	}
	else
	{
		echo "external ip address has not changed since last check";	
	}
?>

This file gets the returned contents of remote-ip.php from the remote server. This file simply returns the super global $_SERVER[‘REMOTE_ADDR’], compares it with the remotely stored contents of last-ip.txt and if different calls write-ip.php to record the change.

File 2: remote-ip.php

This file resides on the remote web server and contains just one line of PHP.

<?php
// remote-ip.php
//
// This file simply echoes back the remote client IP
echo $_SERVER['REMOTE_ADDR'];
?>

Of course this is just echoing back the client address, which in this case happens to be the IP address assigned to my home router by my ISP.

File 3: write-ip.php

This file is responsible for writing IP address changes to last-ip.txt.

<?php
//write-ip.php
//
//This file takes one parameter in correct IP format and writes it to a text file.  Incorrect parameter formats are rejected.

  if (isset($_GET['ip']))
		$new_ip=trim($_GET['ip']);
	else
	{
		echo "no IP parameter";
	  die();
	}
		
	if (strlen(trim($new_ip))==0 || strlen($new_ip)>15 )	
	{
		echo "invalid IP parameter";
		die();
	}	
	$quartet_array=explode(".",$new_ip);
	
	if (count($quartet_array)!=4)
	{
		echo "invalid IP parameter";
		die();		
	}
	
	if (!is_numeric($quartet_array[0]) || !is_numeric($quartet_array[1]) || !is_numeric($quartet_array[2]) || !is_numeric($quartet_array[3]))
	{
		echo "invalid IP parameter";
		die();	 		
	}
	
	
	$file_name="/server/path/to/last-ip.txt";
  $file_handle=fopen($file_name,'w') or die('cannot open file');
	
	$contents=$new_ip;
	
	fwrite($file_handle,$contents);
	fclose($file_handle);
	
	echo $new_ip. " written to last-ip.txt";
?>

This script expects one parameter in normal 4 quartet IP address format (XXX.XXX.XXX.XXX). Any other parameter format is rejected. If the parameter format is correct then the parameter is written to last-ip.txt.

File 4: last-ip.txt

This simple text file is an empty text file that can be uploaded to your remote server. You’ll need to chmod 777 it to make it writeable by write-ip.php. And finally we get to useful bit, it’s this file that you can access from any web browser by typing the file location into your browser, something like:

http://www.remote-host-address.com/path/to/last-ip.txt

And hey presto you’ll see the IP address of your modem at home. You can then use that IP in your remote desktop tool, or just type it into your browser if you’re hosting a website from home. I also use the text file as an include in other remote PHP files to provide IP based exceptions.

How I Reload my iptables Rules on Ubuntu Reboot

There’s a few ways that custom iptables rules can be re-loaded when your Ubuntu server reboots. I’ve chosen to reload mine using the /etc/network/interfaces file. Here’s what I’ve included in that file:

auto lo
iface lo inet loopback
pre-up iptables-restore < /etc/iptables.firewall.rules
auto eth0
iface eth0 inet dhcp

The key line here is the line starting with pre-up. This directs the iptables-restore to reload my rules from the /etc/iptables.firewall.rules file.

Another way of accomplishing the same thing is to create a script file in the /etc/network/if-pre-up.d/ directory and put the following in it:

#!/bin/sh
/sbin/iptables-restore < /etc/iptables.firewall.rules

Then set the permissions on the script file with:

sudo chmod +x /etc/network/if-pre-up.d/your-filename