Validate numeric form fields in VBA

To help users enter the correct information every time they use a VBA form we can apply some validation checks.

In today’s example I have a field called ‘productID.text’ that will always be a number. To ensure that no other types of characters are input, you can use the following code.
Private Sub productID_Change()

If IsNumeric(productID.Text) = False Then
MsgBox "The product ID must be a number"
productID.Text = ""
End If
End Sub
The code is applied to the change event of the field and makes sure it only receives numbers. If an illegal character is entered it pops up a message asking for the correct input and then clears the field ready for the next attempt.

Perform validation on blank fields in a VBA form

When a user is inputting information it will be necessary to build in some quality checks to prevent the user from entering inaccurate or poorly formatted information.

One type of validation check that should be carried out involves checking to see if any important/mandatory fields have been left blank. The code below will check this and prompt the user if the information has not been provided.
If productID.Text = "" Then MsgBox ("Please enter the product ID")
If productID.Text = "" Then Exit Sub
This code pops up a message to the user to enter the product ID and when they click ‘OK’ the second line ensures the field is blank ready for user input.
This code should be added to your ‘Submit’ button.

Open a ‘Save As’ file dialogue box for Word using VBA

It may be necessary to complete an action in Word or Excel and then automatically pop up a ‘Save As’ dialogue .

In my case not only did I need to pop up a Save window but I also needed to give it a pre-determined name. Once the user had entered some details and submitted the information for the VBA code to act on, I didn’t want them to alter the document any further. In addition, I also wanted to standardise the word document and cut down on the work the user had to do.

Here is the code that will produce a Save window with the pre-populated name of ‘MyFileName’.

Dim fNameText As String
fNameText = “MyFileName”
With Dialogs(wdDialogFileSaveAs)
.Name = fNameText
.Show
End With

In the above example I have declared a variable, assigned it some text and then used the variable to populate the filename in the Save As dialogue.

Therefore, when the user saves the document it will be called “MyFileName.doc”.

NB. You could also just add the filename directly without a variable :

With Dialogs(wdDialogFileSaveAs)
.Name = “MyFileName”
.Show
End With

How to add a tab character in VBA

Adding information to a spreadsheet, word document etc is one thing but most of the time you will need to add some formatting.

A tab character may be necessary to improve the appearance of your entry so this will need to be accommodated in your VBA code.

You can add a Tab character in your VBA code by using one of the following :

1. Chr(9)
2. vbTab

Here is an example of the code in use :

phoneDets = “Phone number : “ & Chr(9) & Chr(9) & “07896541243”

This code is assigning the string ‘Phone number :’ with two tabs inbetween and then the actual phone number.

So the output will be :

Phone number                                  07896541243.

Write information into subject line of word document properties using VBA

The document properties within Word can be manipulated using VBA code and here are a few ways that we can write information to them.

To write hard coded text
ActiveDocument.BuiltInDocumentProperties("Subject") = “Hello World”
To write the value of a variable into the subject line
ActiveDocument.BuiltInDocumentProperties("Subject") = VariableName
To clear the subject line of any information
ActiveDocument.BuiltInDocumentProperties("Subject") = “”

Select and delete all text within a word document using VBA

The project I have been working on requires the user to fill in a VBA form, submit the details and then VBA (macro) code will write the information into the document.

This is quite straight forward but the user could open and submit the form again causing duplicate entries. Therefore, I needed a way of selecting any text that was already present in the document and deleting it BEFORE any new entry was written.

So here are the two small bits of code you will need to select all the text and delete :

Selection.WholeStory
Selection.Delete

If you put this before you write into the document, you will always be guaranteed a blank canvas !.

Matrox DualHead2Go extend desktop configuration for dual screens

A new laptop arrived in the office and the user required a dual screen setup. Matrox DualHead2Go was selected as the appropriate bit of hardware to do the job so all that was left was to set it up.

In our office we all have desktops with two monitors. Most of these are implemented by having graphics cards with two video outputs. This allows us to extend the desktop over both screens using just the Windows OS (Operating System).

Programs can be moved between screens and has standard behaviour such as maximising a program within it’s current window.

Matrox DualHead2Go

The first thing to do is connect the two monitors to the Matrox adapter and then connect the USB cable to the laptop/PC. Once the software is installed you are now ready to apply the configuration.



When I got to this stage the image on the laptop was duplicating itself on both screens and there was no obvious way to extend the desktop and get the functionality I required.
After some trial and error I managed to stumble upon the correct setup. So with the lid of the laptop closed I applied the following.

WINDOWS DISPLAY SETTINGS



MATROX POWERDESK SETTINGS




**Please note *** If you are unable to find some of these options or you change the settings and they are not maintained, I have found this is just a glitch in the hardware/software. To resolve this just pull out the USB lead which powers the Matrox adapter and plug it back in and it will sort itself out.

Philips SpeechExec Pro USB Smart Key licensing dongle

The Philips SpeechExec Pro software requires the USB Smart Key (License Dongle) in order to be used.

Without a valid license the software interface will not open and a warning message is produced.

During the installation of the software an option is provided to download the license off the USB Smart Key to the computer. If this is not selected then you can just make sure the dongle is plugged in every time you wish to use the software.

However, if you wish to download the license to the pc so it is always present it can be done in the following way :

1. From the main window of the software, click Help > License Information.



2. From the License Information Window, click “Download License” to download the license from the USB Smart Key to the computer. This will allow use of the software without inserting the USB License Key.



*  To remove the license from the computer back to the USB Smart Key, insert the USB Smart Key and click “Upload License”. This will allow the software to be transferred to a different computer.