About these examples...
On this page we offer some free examples of how to use VBA with Microsoft Excel to produce tools that will make your use of Excel more efficient and simpler. There are also some useful tools in the form of Microsoft Excel addins that we actually use on a daily basis. These are offered free of charge Please note that the examples are all zipped so you will possibly need an unzip program such as Winzip
Note: Click on the images to enlarge
J & R Database Form...
This has been one of our most popular downloads
for some time. It demonstrates how to create a UserForm in Visual Basic for
Applications to simplify the entry of data into a table of a Spreadsheet. It can
also be used to search for an item and will create a list of all items found.
the Labels are "dynamic". This way if the headings on the Worksheet are changed
the labels will reflect that change. The code also demonstrates how to loop
through a set of controls in a UserForm.
What it does, is look at each control in Frame1 and if it is a label it will
change the caption to the relevant Heading. It reads the Heading by locating the
First entry in the Database and using the text in the cell above by using
Offset, N.B. the negative row reference refers to the cell above.. Because there
are four Columns in this Database the use of i as an Integer and
for i = 0 to 3 will check each of the four
Column Headings.
If the Frame contains Labels not relevant to the Database headings then use the
alternative line of code, you will see that this refers to the part of the label
Name that reads "Head" by using the Mid statement. It will ignore any other
Labels. Change the Name in the Properties Window.
You could use this method to loop through & load or clear the Textboxes. Adding
a Textbox will be automatically recognised.
Click here to download
Navigating a large workbook...
Often a Spreadsheet can contain many sheets and can become difficult to navigate, many of the Navigation Tabs being out of sight. You can of course right click on the navigation arrows at the bottom left of the Workbook and choose "More sheets..." Here we offer some suggestions to overcome this using Microsoft Visual Basic for Applications and at the same time make your Workbook a little more professional, in fact you could even hide the sheet tabs if you wish. use the Tools menu and select Options and in the View tab de-select Sheet Tabs.
The first method creates a Table of Contents in a separate sheet. This page will contain hyperlinks to all the sheets in the Workbook so that a simple click will open the required sheet. You simply need to copy & paste the code into your own Workbook, alternatively if you have the sample Workbook & your Workbook open you can simply "drag & drop" the code into the destination. Download the working example here, you could even use this as a Template for future work. If you don't want to do this we also have a free addin to do the work for you, get it here.
For a really professional tool you could install our Navigation Toolbar, this works in any Workbook and updates as you switch from Workbook to Workbook, or add and delete sheets. Click here to for more information
Contacts Form...
This example demonstrates how a simple form can be created on a spreadsheet to save data to another sheet that acts as a database. The data entry cells in the example are on a separate sheet, but there is no reason why they cannot be placed above the table of data actually on the database Sheet.
This example demonstrates the use of Offset to enter the data, .End(xlUp) to locate the next empty Row for Data input, a MessageBox to inform the user of successful completion and also how to hide unnecessary Columns and Rows using the Format Menu.
Click here to download the example
Excel Web Toolbar
This is an Excel addin that enables you to quickly log on to your Favourite Excel Web Sites & Forums. There is a even a Search function. Simply type your query into the TextBox and select then Forum of your choice or Google.
Click to enlarge
The addin is downloaded as an .exe installation file. When the installation is complete a GUI is displayed which will help you correctly install the addin.
After installation, you will find a new item in your Excel Tools Menu - Excel WebToolBar. From this item you can launch the ToolBar or even disable or completely uninstall it.
Click here to download the addin
Hyperlink in a UserForm
This example demonstrates how to use a label on UserForm to simulate a hyperlink The form example provides a quick and easy way to let users email you directly from the UserForm. This code allows users to click on a label that contains an email address, and open the default email application and fills in the email address All the user has to do is fill in the subject, and the comments they wish to send. The other labels act as hyperlinks to websites.
This workbook demonstrates the use of the Shell Command, formatting Labels on a UserForm, sending an email from Microsoft Excel. It also shows how to use the WorkSheet_Change event to call the UserForm, this can be adapted to trigger any Macro.
This example can be useful if you want to include a "Contact us" form in your applications.
Click here to download the example
Message Box Example
This is a simple example to get a user's confirmation with a MessageBox. This feature is very useful and can be used in numerous ways which we will demonstrate later in one of our planned Tutorials.
Click here to download the example
Populate UserForm
This example demonstrates how to call a UserForm by using the Before_DoubleClick event, and to use the UserForm_Initialise event to populate Textboxes. It would be simple o adapt this example by adding Commandbuttons to add, delete or even amend data.
Click here to download the example (47Kb).
Worksheet Protection with UserInterFace Only...
Individual worksheets can be protected using VBA, the only problem is that you will need to write an unprotect routine into your code to allow user input via your code. The best way around this is to setup a blanket protection scheme for every sheet in the workbook but still allow all your macros to function without interference from the protection. This is done when the workbook is opened. You must protect your VBA project as well. To do this, whilst in the VB Editor open the Tools menu and select VBAProject properties, then check Lock project for viewing and finally type in and confirm your password. To be effective you must then close & save your workbook. See this demo.
Jf you need to exclude specific sheets then adapt this code
Const PW As String = "password"
Dim wSht As Worksheet
Application.ScreenUpdating = False
For Each wSht In Worksheets
Select Case wSht.Name
'exclude these sheets, amend names and add others if required
Case "Sheet1", "Sheet2"
Case Else
With wSht
wSht.Protect _
Password:=PW, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True
End With
End Select
Next wSht
Application.ScreenUpdating = True
Click
here to download the example (47Kb).
Always remember that. although the Visual Basic password is stronger than Excel's worksheet protection, it can still be cracked. If your projection needs stronger protection check this.
Note: all our examples are offered as is with no warranty. J & R Excel Solutions accept no responsibility for any loss arising from your use of them