Contextures Search Form Builder Help 02 Advanced

Excel Dashboard Template

Additional tips on how to use the Contextures Search Form Builder (SFB) add-in from Contextures. This add-in included in the UserForms for Data Entry Kit

Add 2nd Search Form

The Search Form Builder (SFB) add-in is designed to add a single Search Form to your Excel workbook. If you run the SFB again, the macros will remove the old Search Form and code modules, and add new ones.

However, if you need to have a 2nd Search Form in the same workbook, follow the instructions below. The new Search Form can be based on the same data, or on different data (additional preparation steps required).

Preparation Steps

Additional Preparation Steps (if based on different data)

Add the New Search Form

Rename the Sheets (optional)

Preparation Steps

When the SFB macro runs, it looks for existing UserForms and code modules with specific names, and deletes them. If you change the names of those objects, the macro won’t delete them.

Before you add a second Search Form, follow these steps to change the existing form and modules.

1. Make a Backup File

As always, you should make a backup copy of your workbook, before making major changes.

• On the Developer tab, click the Search Form Builder button
• Click the Make Quick Backup command

2. Go to the Open Form Macro

• Go to the sheet that has the “Open the Search Form” button
• Right-click the button, and click Assign Macro
• In the list of macros, select SFB_Form_Open, and click the Edit button

3. Change Open Form Macro

The Visual Basic Editor (VBE) opens, showing you the macro named SFB_Form_Open, which opens the Search Form. You will add the letter “A” at the end of object names, so that Excel won’t automatically delete them.

NOTE: If you repeat these steps later, to create a 3rd form, use “B”, or another letter, instead of “A”

In the macro named SFB_Form_Open,

• Add an “A” at the end of the macro name
• Add an “A” at the end of the form name in that macro.

Here’s what the revised macro looks like:

4. Global Constants

When the SFB runs, it will create a new SFB_Form_Open module, with global constants for the data table name and its worksheet name. Duplicates will cause errors, so you’ll delete or change the global constants above the macro.

Same Data – Delete Constants

If your new Search Form will be based on the same data as the first Search Form,, follow these steps:

• Select the green comment line, and the 2 lines with global constants
• Press the Delete key, to delete those 2 lines

Different Data – Rename Constants

If your new Search Form will be based on different data from the first Search Form, leave the global constants in the module, and add an A at the end of each constant’s name.

5. Change Form Name

Next, you’ll change the Search Form name

• In the Project Explorer list, select the form named frmSearch_SFB
• In the Properties window, add an “A” at the end of the form name

Here’s what the revised form name looks like:

6. Change Module Name

Next, you’ll change the module name, where the Form Open macro is stored

• In the Project Explorer list, select the module named modSFB_Form
• In the Properties window, add an “A” at the end of the module name

Here’s what the revised module name looks like:

7. Update the Open Form Button

The final step is to update the Open Form button, so that it runs the renamed macro

• Go to the sheet that has the “Open the Search Form” button
• Right-click the button, and click Assign Macro
• In the list of macros, select SFB_Form_OpenA
• Click the OK button

NOTE: Don’t test the button now, because it won’t work yet (the global constants were deleted).

Additional Preparation Steps

Same Data: If your new Search Form will be based on the same data as the first Search Form, go to the Add New Search Form step. No additional preparation steps are needed.

Different Data: If your new Search Form will be based on different data from the first Search Form, a few additional preparation steps are required. Follow the instructions in this section, before creating the new Search Form

A. Change Module Name

For a new form based on different data, you’ll also change another module name, where the Search Form settings are stored

• In the Project Explorer list, select the module named modSettings_SFB
• In the Properties window, add an “A” at the end of the module name

Here’s what the revised module name looks like:

B. Change Names in Settings

When the SFB runs, it will create a new modSettings_SFB module with global constants for the Admin sheet names and search results range.

Duplicates will cause errors, so change the existing names — add an “A” at the end of each constant’s name, and the sheet/range names. Here’s the revised code, with 6 changes :

C. Change Names in Form Code

The final preparation step is to replace all of the old variables with new names, in the Search Form code.

In the Project Explorer list, right-click the form named frmSearch_SFBA, and click View Code

• Press Ctrl+H, to open the Replace dialog box
• In the Find What box, type shSR
• In the Replace With box, type shSRA
• In the Search xection, select Current Module
• Click the Replace All button

• When the confirmation appears, to say there were 2 replacements, click OK

Repeat the Find and Replace steps, until all the following names have been replaced:

Find What	Replace With	Count
shSR	shSRA	2
shAdm	shAdmA	2
strRes	strResA	2
strDataWs	strDataWsA	5
strDataTbl	strDataTblA	6

D. Rename SFB Worksheets

The final step is to change the names on the SFB worksheets.

Add a “A” to these sheet names:

That is the end of the additional preparation steps required for the Search Form based on different data.

Add the New Search Form

Now that the existing objects have been renamed, you can create a new Search Form, based on the same data table as the existing Search Form .

Follow these steps to make a backup, add the new form, and then test the buttons to open the forms

Add the New Form

• Select the sheet where the data table is stored
• On the Developer tab, click the Search Form Builder button
• Click the Make Quick Backup command
• Go through the rest of the steps, to create the field list, customize it, and build a Search Form

Test the Open Form Buttons

After the new Search Form has been added, both of the Open Form buttons should work correcly.

• Click the Open Form button on the new sheet
• The new Search Form should open
• Find the sheet with the old Open Form button, and click it
• The old Search Form should open

If the forms don’t work correctly,

• Save the file with a different name, or close without saving the file.
• Then, make another copy of your backup file, and try the steps again.

Rename List Sheets and Buttons

This step is optional, but to prevent confusion, you should rename the sheets where the field lists and buttons are stored.

For example, are the renamed sheets in the practice workbook

You can change the text on the worksheet buttons too, if you want to:

• Press the Ctrl key, and click on a worksheet button, to select it
• The pointer changes to an I-beam shape when you point to the text
• Click where you want to change the text, and edit it
• If necessary, drag the border handles to adjust the size of the button, so all the text shows
• If you want to change the button colour, click the Format tab on the Ribbon, and choose from the Shape Styles palette
• When finished, click on any worksheet cell, to deselect the button

Search Form Questions

For additional help, see SFB Help – General Tips and SFB Help – Code

NOTE: Print Preview instructions have been moved to SFB Help – Code.

If you have other questions or comments about the Contextures Search Form Builder add-in, please send them to Debra Dalgleish at Contextures by
email:

ddalgleish @ contextures.com

Related Links

Install an Excel Add-in

UserForms for Data Entry Kit

Search Form Builder Help 01