Excel provides a wide range of user interface features that allow users to interact with the application. However, these features can be time-consuming to use, especially when performing repetitive tasks. Macros can be used to automate these tasks and streamline the workflow.
SpreadsheetWeb supports various built-in user interface features similar to those handled by macros in Excel. In this article, we will show how commonly used user interface macros can be replaced by those built-in features in SpreadsheetWeb.
Hiding Workbook Elements
Excel includes various tools for managing worksheets, including the ability to hide and unhide workbook elements like worksheets, rows, and columns. However, hiding multiple workbook elements manually can be time-consuming and tedious. In other cases, you may want to hide specific elements when certain conditions are met. Macros can be used to automate this process and make it quicker and more efficient.
The above scenario uses a VBA code that hides or shows the rows 20 and 21 according to checkbox's value.
Private Sub CheckBox1_Click()
If CheckBox1.Value Then
Range("20:21").EntireRow.Hidden = False
Else
Range("20:21").EntireRow.Hidden = True
End If
End Sub
In SpreadsheetWeb, there is a built-in feature to manipulate the visibility of each user interface control. You can set the visibility of each control by simple TRUE/FALSE values in a named cell. Each control in a SpreadsheetWeb application has a property called Visible property. When you select "By Value Of" option, you can see the Named Range property with a list of cells that contain Boolean values.
Either assign a Boolean value by binding the named range to a checkbox control in the SpreadsheetWeb app or a formula to make things dynamic. In the example below, the visibility of certain rows is bound to a checkbox.
Tip: You can show or hide any control in the user interface including the pages of your application by Visible property.
Dynamic tables
Another common usage of VBA is inserting and removing rows to mimic dynamic grids. A dynamic grid improves the user experience by allowing them to add rows as needed without having to allocate a large area.
Although rows can be inserted or deleted via single-line codes, handling it with a macro allows to maintain functions and formatting.
…
' insert a row before Subtotal (end of the table)
Rows(Range("Subtotal").Row).Insert
…
…
' delete the row if the a cell is selected in the grid
If Not Intersect(Selection, Range("Grid")) Is Nothing Then
Selection.Cells(1, 1).EntireRow.Delete
End If
…
There is a build-in control in SpreadsheetWeb called “Form-based” grid that supports adding or removing rows manually. All you need to do is to select either one of “Form-based” grid options for Type of Input Method.
That’s it. When you load the app, you will only see the rows that has a data.
Resetting Values
Resetting typically refers to the process of returning a spreadsheet to its original or default state, removing any data or changes that have been made. Resetting a spreadsheet is useful for users who have made a mistake or want to start over without having to reload the file or navigate back to a previous page or state. Creating a “reset” button with a VBA code is the most common approach to reset cell values back to defaults in Excel.
SpreadsheetWeb has a built-in Reset feature that simply reverts the inputs’ values to their default state. All you need is a button with a Reset event.
The Reset event has two modes. You can use it to reset either all inputs in your application or only the inputs in the active page. To switch modes, click on the event to see the options.
Here is how the Reset event works:
Navigation
Users may require entering and viewing data across multiple worksheets in complex Excel applications. Moreover, each worksheet may contain a specific set of inputs or actions that the user must complete before proceeding to the next worksheet. This is a common design-pattern called wizard-based navigation that presents a series of sequential steps or pages to guide users through a complex process or task.
This type of complex user interface navigation can only be handled by inserting buttons with VBA code in Excel as shown in the example below.
SpreadsheetWeb includes several navigation features that can be configured without any coding. Tab-based navigation is the most popular design pattern in SpreadsheetWeb because it provides a clear and organized way to present complex content and functionality. It can also make it easier for users to find the information they need and quickly switch between different sections of the application.
Tabs can be arranged horizontally or vertically, depending on the application's design and the amount of content that needs to be displayed. In addition, some web applications may use nested tabs, where clicking on a tab reveals a sub-menu of additional tabs.
You can always customize your application’s navigation process by using buttons. Wizard-based navigation is a popular design pattern for applications that require users to complete a multi-step process, such as filling out a complex form, making a purchase, or creating an account. By breaking the process down into smaller, more manageable steps, wizards can help users to stay focused and avoid feeling overwhelmed.
Wizard-based navigation can also be combined with features to hide pages in an application as described earlier in this article to allow users to skip or go back to previous steps, while others may require users to complete each step in order.
Another cool feature of SpreadsheetWeb applications is the Wizard Tracking feature. You can convert the regular navigation bar to a wizard-style visual which guides the user about the process as shown below.
Please visit our dedicated help pages to learn more about navigation in SpreadsheetWeb applications.
