Contextures Search Form Builder Help 03 Code

Business KPIs App

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

Show Print Preview

When you click the Print List button, a macro prints the Search Results sheet from the active workbook. If you prefer, you can make a minor change to the code, so it shows a Print Preview, instead of printing the sheet.

Follow these steps, to see the Search Form in design view:

• Open your workbook that contains the Search Form
• Press Alt+F11, to open the Visual Basic Editor (VBE)
• In the Project Explorer list at the left, find your workbook
• Click the plus sign beside the Forms folder, to see the forms
• Right-click on frmSearch_SFB, and click View Object

Next, follow these steps to change the Print List button code and caption:

• On the Search Form, right-click the Print List button, and click View Code
• In the following line of code, change False to True
  • OLD LINE:
    • wsSR.PrintOut Preview:=False
  • NEW LINE
    • wsSR.PrintOut Preview:=True
• Above the PrintOut line, add a new line, with this code:
• Below the PrintOut line, add a new line, with this code:

Here is a screen shot of the revised code

Next, follow these steps to change the button caption:

• In the Project Explorer list, right-click on frmSearch_SFB, and click View Object
• Click on the Print List button, to select it
• In the Properties window, click in the Caption box
• Type a new caption for the button — Preview List
• 
• Drag the handle on the left side of the button, to make it wider
• Finally, click the Save button, to save your changes, and close the VBE.

NOTE: You can make the same changes in the Seach Form master form (ctxsfbuilderform.xlam), if you want all new Search Forms to do a print preview, instead of printing the list.

Print Hidden Sheet

If the SFB_Results sheet is hidden, the Print List button won’t work. Instead, you’ll see a message, “Could not print Search Results.

To allow printing when the SFT_Results sheet is hidden, follow the instructions below.

First, follow these steps, to see the Search Form in design view:

• Open your workbook that contains the Search Form
• Press Alt+F11, to open the Visual Basic Editor (VBE)
• In the Project Explorer list at the left, find your workbook
• Click the plus sign beside the Forms folder, to see the forms
• Right-click on frmSearch_SFB, and click View Object

Next, follow these steps to add 2 lines of code to the Print List button:

• On the Search Form, right-click the Print List button, and click View Code
• In the If…Else…End If section, click at the end of the Else line, and press Enter, to create a blank line
• In that blank line, type this code, to temporarily unhide the sheet:
• Click at the start of the End If line, and press Enter, to create a blank line above the End If
• In that blank line, type this code, to hide the sheet again:

Here is a screen shot of the revised code, with the new lines highlighted.

Then, click the Save button at the top of the VBE window, and close the VBE window.

Fix Table Totals Problem

There was a problem with the Add button code in early versions of the Search Builder Master workbook – ctxsfbuilderform.xlam.

To fix the problem, makethe following minor change to the Add button code. Do this in your copy of the ctxsfbuilderform.xlam file, and in any Search Forms that you’ve added to your workbook.

Open the Search Form Design

Follow these steps, to see the Search Form in design view:

• Open your workbook that contains the Search Form
• Press Alt+F11, to open the Visual Basic Editor (VBE)
• In the Project Explorer list at the left, find your workbook
• Click the plus sign beside the Forms folder, to see the forms
• Right-click on frmSearch_SFB, and click View Object

Modify the Add Button Code

Next, follow these steps to change the Add button code:

• On the Search Form, right-click the Add button, and click View Code
• Scroll down, and find these two lines, a few lines above the End Sub line

      myTable.ShowTotals = bTot
      SFB_ClearSearchControls

• Select the 2 lines of code
• Drag those 2 lines down to the blank line above “bEventsOff=False”

Here is the revised code

NOTE:

That code only clears the Search boxes. If you would prefer to clear the data entry boxes too, and update the search results list, use this code instead.

      myTable.ShowTotals = bTot
      SFB_ResultsListUpdate

Save the Workbooks

After moving the 2 lines of code, click the Save button at the top of the Visual Basic Editor.

Do this in the Search Builder Master workbook – ctxsfbuilderform.xlam, and in every other workbook where you change the code.

Search Form Questions

For additional help, see Search Form Builder Help 01

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

 

Excel KPI Application