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 !.

Microsoft FaceID numbers for VBA programming

If you want to use Microsoft’s icons within your VBA (Visual Basic for Applications) code, you will probably have to refer to something called a ‘FaceId’. This FaceID requires a number which tells the office application which image to display. For example in the code you may see ‘.FaceId = 59’.

This FaceID would give you the ‘smiley face’ icon when the code was executed.

So how do you know which number to use for which icon ?.

Thankfully, the answer lies in the following graphics.

1-500	501-1000
1001-1500	1501-2000
2001-2500	2501-3000
3001-3500	3501-4000
4001-4500	5001-5500
5501-5685