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

Simple PHP test script to check PHP is installed correctly

When you install PHP for the first time one of the best ways of knowing it is configured correctly is to use a simple PHP script. If your browser can read the file and carry out the stated action then you know you are ready to continue with more interesting things.

So without further ado here are a couple of simple PHP scripts that will get you started.

<html>
<head>
<title> Supporting Tech PHP Test Script </title>
</head>
<body>
<?php
phpinfo( );?></body>
</html>

This script will display some of the hosts php specifications. Alternatively, if you want to 'keep it real' then you can always use the classic 'Hello World' script.

<html>
<head>
<title> Supporting Tech PHP Test Script</title>
</head>
<body>
<?php
Echo "Hello world";
?></body>
</html>

UPLOADING AND TESTING

1. Type the above and save the file as 'index.php'.
2. Add this to your web server root directory.
3. Open up your web browser and type in the address to view that page .e.g 'localhost' if you're using IIS.

Display information from a table and change the column names

In my database table I have two columns : ProdID and Prod. When I query the database to display the IDs and the product names, I would like to make the result a bit more 'User friendly' by changing the column names to 'Product ID and 'Product'. I do this in the following way :


Open query analyser.
Select the required database.

Select ProdID as 'Product ID', Prod as Products from Inventory

Display information in Upper and Lower case from SQL database

In my database table (products) I have a column called items that lists a number of products in sentence case. In order to display them in upper and lower case I do the following :

Open query analyser.
Select the required database.

Upper case

Select upper (item) from products

Lower case

Select lower (item) from products

Backup a database using a SQL command

In this example, we are backing up the database called 'Customers' to a location on the c: drive.

Backup database Customers to disk = 'c:\customer_backup.bak'

Get current date and time in SQL

There may be times where you will need to insert the current date and time into a database i.e. to record when a transaction has taken place. The command to display this timestamp is as follows :

select getdate() 'Current Date'

If you type this into query analyser you will get the current date and time in a column entitled 'Current Date'.

How do you copy a database table in SQL using code?

Open Query analyser
Select the required database (Use [databasename])

Type :

SELECT * INTO MyNewTable FROM MyTable

e.g SELECT * INTO Customer2 FROM Customer

How do you select all items from a SQL table ?

This is probably the first thing you will ever do with SQL and it's a nice easy one to get you started.

Open query analyser.
Select the required database (This can be done from the drop down menu or by typing "Use [database name"])

Type :

Select * from [table name]

This will now return all the records in the table.

Hide System Tables and Objects in MS SQL 2000 Enterprise Manager

Recently I have been working with SQL tables and stored procedures in Enterprise Manager. By default, the system and stored procedures are visible along with user-defined ones which make it fairly inconvenient when you are only interested in your own tables and stored procedures.

To make life a bit easier I thought I would find a way of hiding the system objects. The instructions are as follows :

1.  Right click on the "(Local)(WindowsNT)" in the Enterprise Manager.
2.  Select "Edit SQL Server Registration Properties," which opens up a property panel.
3.  Uncheck the option "Show system databases and system obejcts."
4.  Click OK

Get current date and time using C#

In this example, the date and time will be written to 'label1' when 'button3' is pressed.
private void button3_Click(object sender, EventArgs e)

{

label1.Text = DateTime.Now.ToString();

}

C# application that counts the number of characters being entered

This application consists of a textbox and a label. As the user types in the text box the label displays the number of characters that have been entered. The code is as follows :

private void textBox1_TextChanged (object sender, EventArgs e)

{
int charactercount;
string labeloutput;

charactercount = textBox1.TextLength;
labeloutput = charactercount.ToString();

lblcount.Text = "Character count : " + labeloutput;

{

For Loop to display user input in C#

In this example, when 'button3' is pressed the application will read what has been added to 'textBox1' and print it the console each time it loops through.
private void button3_Click(object sender, EventArgs e)

{

for (int i = 1; i <=10; ++i)

{

System.Console.WriteLine (textBox1.Text);

}

}

Trim spaces from the start and end of string using C#

In this example I have a text box on my windows form to collect the user input and a label to display the outcome once a button is pressed. The code is as follows to trim spaces from the beginning and end of a user input.

private void button3_Click(object sender, EventArgs e)

{

string userinput;
char [] trimchars = {' '};

userinput = textBox1.Text.Trim(trimchars);

lable1.Text = userinput;

}

Collect user input from form and change it to upper or lower case in C#

In this example I have a text box on my windows form to collect the user input and a label to display the outcome once a button is pressed. The code is as follows to change the input to upper and lower case.

private void button3_Click(object sender, EventArgs e)

{

label1.Text = textBox.Text.ToUpper();

}

or

{

label1.Text = textBox.Text.ToLower();

}