jSon and Dynamic Forms

 
Now we know how about Hiding data in Excel Objects, Excel JSON conversion and Dynamic Forms let’s combine all that as an enhancement to the Roadmap Generation project. The objective is to embed traceability data in the generated roadmap shapes so that we can bring up a dynamic form, allow changing of the data that made it, and write that back to the originating cells.
In previous articles I covered the roadmap project, where powerpoint ready roadmaps can be generated from simple excel input data. By making use of Data Manipulation Classes and Excel JSON conversion data can be easily embedded into the generated shapes for future reference. Using the following data
 
 
 
Our roadmapper generates this
 
 
 
 
In addition though, it also encodes the associated data in jSon format in the .alternativeText property of each generated shape. So for example,  in the ‘iphone’ shape’, this has been encoded into the alternativeText field – a jSon representation of the data, as well as some traceablity items.

Now that we have all this, there is a direct link between the collection of shapes and the data that was used to create it, which can easily be accessed through a cJobject class. For example the activate data can be addressed as cjObject.Child(“data”).Child(“activate”).Value and its location as  cjObject.Child(“location”).Child(“activate”).Value

Creating dynamic forms

I covered this topic in Dynamic Forms but it’s worth looking at this again in the context of the roadmap shape collection. The objective here is to be able to click on a shape, in this case the one labeled ‘ipod’, and bring up a form showing the original data, allow it to be changed, and write it back to the original cell.
 
 
 
This presents a few technical problems. Here is how to do it.

Assigning an action to the shape

Firstly, when the shape is created the data is serialized and hidden, then we need to assign an onAction  event handler so that it reacts to a click on the shape later.
 
 
The formatting of the onAction property of the shape is kindof funky, so here is a general procedure (makeCallString) to do it along with how to use it (procTraceability)
 
 
All this does is to eventually assign the procedure named in the constant cProc, to the onAction property of psc.Shape. This causes the following procedure to be called when the shape is clicked. Since I have included the shape name in the call string, shapeFutzing receives the name of the clicked shape. Once the corresponding shape object is found we just deserialize the jSon encoded data and either show the form, or replot the roadmap if the roadmap background is clicked as below.
 

Showing the form

I am starting with an existing, but empty userform. The textboxes and so on are all build dynamically from the contents of the deserialized .alternativeText property of the clicked shape. However there is a fundamental problem to be resolved, which is where to plot it. As you may know, positioning userforms can be complex, but in this case i want to position it more or less where the mouse was clicked. Userforms are calculated in points, whereas mouse positions are expressed in pixels, so to do that we need to find the mouse position, examine the dpi of the current display, and convert. Here is a set of API procedure calls  to do all that. The convertMouseToForm function will take care of it all and return the left and top positions you would need to specify to position a form at the current mouse position. It still needs a little modification though, since the Application.ActiveWindow might not be maximized, and a userForm will ignore top and left property settings unless its  .StartUpPosition property is set to manual (0).
Plotting the form in the right place then can be expressed as follows

Creating the dynamic forms controls

I have created a cRoadmapItemForm class to handle all aspects of dealing with the creation and handling of the form events. This is just really busy work except for a couple of points.
  • We are going to use a further 2 classes for dynamic event handling – cHandleItemFormEvents for dealing with textbox events and cHandleFormExit for dealing with command button events
  • When you create these of event handling classes, you need to ensure they dont get cleaned up by VBA garbage collection while they are still needed. To ensure that we maintain a reference to them and keep them safe from the garbage guy, i always add them to a collection in the calling procedure. You will see that in the following long and boring procedure.

Dealing with Dynamic control events

With dynamic controls, you do not have the full suite of events that you have with IDE created controls. I’ve no idea why this is, but there it is. That means that you have to do a little more work than normal when, for example, noticing if a control has lost focus, or has changed its value. Here are the 2 classes used for handling the events associated with the dynamic controls we have created on the data form. The key thing to note here is that ‘Private WithEvents’, tells VBA to allow you access to certain event processing or that type of control.
Since we have a cut down set of controls, I have passed the major processing of events back to the calling procedure as follows

Summary

You can find the full code associated with this in the downloads section – roadmapper.xlsm. There are a large number of techniques together in this example.
For help and more information join our forum, follow the blog or follow me on Twitter