Monthly Archive for April, 2015

Microsoft Access Tips: Validation Rule

Access is designed for users to enter data into a database easily but sometimes people type in the wrong data and this can affect the tables and final reports.In this blog post we will be looking how to create a Validation Rule in Access 2007, 2010, and 2013.

 

To stop this you are able to implement Validation Rules to stop this from happening. The Validation Rule limits the values that can be entered into a field. So for example you are able to stop a number greater than 100 being entered in.

 

You can put a validation rule on any field in a table and this is found on the design view of that table, under the general tab.

 

Validation Rule 1

 

You can see there are 2 fields for Validation, Rule and Text. The rule is where you put in your “Rule” here are some examples below:

 


Rule Explanation
>30 Only numbers greater than 30
<20 Only numbers less than 20
Between 20 and 30 Numbers between 20 and 30

 

 

Validation text is where you put the text for the message box that will appear if the rule has been broken. This can be any message and you will always get a OK, Cancel Message box that will appear.

 

Validation Rule 2

Microsoft Excel VBA Tips: How to make a message box

Using Visual Basic of Applications can make your Excel workbooks a lot more user friendly and functional. In this blog post we will be looking how to create a message box in VBA for Excel 2007, 2010, and 2013.

 

The first message box we will be creating will be a small message box which shows the text “Hello World”. Before we can do this, we need to enable the developer tab on the ribbon. To do this you need to Click on File > Options > Customise Ribbon > and then check the developer tab like in the image below.

 How to make a message box 1

 

So on the developer tab; on the left hand side you can see a coding section. This is where you can easily access the Visual Basic Software, Macros and other options.

 

How to make a message box 2

 

Clicking the Visual Basic button will open up the Visual Basic Software. When making a message box, you are unable to record a macro to make this for you. You will need to type code to do it.

 

Here is what the Visual Basic Software looks like.

 

How to make a message box 3

 

To write some code we need to create a module to hold the code. To do this go to the top bar, click on Insert > Module

 

How to make a message box 4

 

Once you have done that you need then to create a Procedure to hold the Message Box.

Click Insert > Procedure > Type a name > Leave it set to Sub and Public > Click OK.

Note: The name cannot have a space in it.

How to make a message box 5


In the Coding window you can then type the following to create a message box

 

 How to make a message box 6

 

To run the Procedure/Macro, go back to your Excel window. This can be done by either closing the VBA window or just minimising it.

 

On the Developer tab > Click on Macros and the new HelloWorld Procedure/macro will be in here.

 

How to make a message box 7

 

Highlight the HelloWorld Macro and click run. You will get a little message appear with your message inside!

 

How to make a message box 8