I'm building a template [Process-Tracker] spreadsheet.
The idea is that, for any process where we can define what steps should occur in what order (and preferably, how far apart), there will be a spreadsheet with buttons for:
- Adding a new [Item] to be tracked
- Updating the List of [Items] and their progress
- and a list of [Next Steps] ordered by [Due date].
I have written the [template] code for the Buttons and UserForm. The idea is that, when creating an actual process, I simply add specific validation for the expected variables/names and make sure it still outputs in a consistent format, and everything else will run just fine.
Here is my Form and My Code:
[Worksheet] Add Item Button
Public Sub Button_AddItem_Click()
CallFormInput
End Sub
Form Input Sub
Public Sub CallFormInput()
UserFormButtonWasPressed = False
UF_New_Process_Item.Show
If Not UserFormButtonWasPressed Then PrintErrorMessage "Please only exit the Form via one of the buttons provided", showMessageBox:=True, endExecution:=True
End Sub
UserForm Code
Option Explicit
Option Compare Text
Private values As Collection
Private Sub UF_Exit_Form_Click()
TestFormSyntax
UserFormButtonWasPressed = True
UF_New_Process_Item.Hide
Exit Sub
End Sub
Private Sub UF_Add_Item_Click()
Dim values As Collection
Dim inputsWithFailedValidation As Variant
inputsWithFailedValidation = Array()
Dim validationMessage As String, passedValidation As Boolean
Set values = New Collection
UserFormButtonWasPressed = True
ValidateAndAssignValues values, validationMessage, passedValidation, inputsWithFailedValidation
If passedValidation Then
AddItem values
Else
HandleFailedValidation validationMessage, passedValidation, inputsWithFailedValidation
End If
End Sub
Private Sub UF_Add_Item_Recurring_Click()
Dim values As Collection
Dim inputsWithFailedValidation As Variant
inputsWithFailedValidation = Array()
Dim validationMessage As String, passedValidation As Boolean
UserFormButtonWasPressed = True
ValidateAndAssignValues values, validationMessage, passedValidation, inputsWithFailedValidation
If passedValidation Then
AddItem values
Button_AddItem_Click '/ Starts again as if clicked the "Add Item" Button on the worksheet
Else
HandleFailedValidation validationMessage, passedValidation, inputsWithFailedValidation
End If
End Sub
Private Sub ValidateAndAssignValues(ByRef values As Collection, ByRef validationMessage As Variant, ByRef passedValidation As Boolean, ByRef inputsWithFailedValidation As Variant)
AssignValues values
ValidateValues values, validationMessage, passedValidation, inputsWithFailedValidation
End Sub
Private Sub AssignValues(ByRef values As Collection)
'/ Pass all values as text, formatting/validation will be handled later
Dim i As Long
Dim controlType As String
Dim uf_Control As Control
'/ item = control item text, key = control item name
Set values = New Collection
With Me.Controls
For i = 0 To .Count - 1
Set uf_Control = .Item(i)
controlType = TypeName(uf_Control)
If controlType = "textbox" Then
values.Add uf_Control.Text, uf_Control.name
End If
Next i
End With
End Sub
Private Sub ValidateValues(ByRef values As Collection, ByRef validationMessage As Variant, ByRef passedValidation As Boolean, ByRef inputsWithFailedValidation As Variant)
'/ Convert to correct varType and validate - Names, Emails, Phone Numbers, Dates etc.
'/ If failed validation, add to validation Message, add me.controls.item(key) to inputsWithFailedValidation
'/ Set passed Validation True/False
passedValidation = True
End Sub
Private Sub AddItem(ByRef values As Collection)
UF_New_Process_Item.Hide
Dim inputValues As Variant
inputValues = Array()
ReorderValuesCollection values, inputValues '/ Explicitly re-build values in a set order in a new array
CreateNewProcessItem inputValues
End Sub
Private Sub HandleFailedValidation(ByRef validationMessage As Variant, ByRef passedValidation As Boolean, ByRef inputsWithFailedValidation As Variant)
Dim uf_TextBox As TextBox
PrintErrorMessage validationMessage, showMessageBox:=True, stopExecution:=False
If ArrayIsAllocated(inputsWithFailedValidation) Then
AssignArrayBounds inputsWithFailedValidation, LB1, UB1
For ix = LB1 To UB1
Set uf_TextBox = inputsWithFailedValidation(ix)
uf_TextBox.Text = ""
Next ix
Else
PrintErrorMessage "Validation failed but no objects specified", showMessageBox:=True, stopExecution:=True
End If
End Sub
Private Sub TestFormSyntax()
End Sub
As well as general "Could I have written my code better" Feedback, I am particularly interested in how my code works as a template.
Could it be refactored/generalised even further?
Could I structure it to be even easier to modify?
Have I used a good (generic) naming convention re: distinguishing UserForm objects/subs from the rest of the program?
"Please only exit the Form via one of the buttons provided"
- you could just hide theX
on the form – user28366 Oct 5 '15 at 8:48