Excel VBA Dynamic Forms

Excel VBA Dynamic Forms change based on user selections to facilitate additional choices . It is a sign of sophistication in your Excel application.  More importantly it makes the experience better for the user. If we look at my example below from My Build a Better Personal Budget Tool,  you note when the user selects a frequency of ‘Month’ there is no need to provide additional information because we are building a monthly budget.  However, if the user selects ‘Biweekly’ (paid or charged every 2 weeks), we know that some months will have 2 pay or charge periods and some will have 3.  This is determined by the day of the week the user gets paid or charged.  Our form reacts to that selection using VBA’s event functionality and offers Monday – Friday as choices.  The user immediately knows what he or she is being asked and responds accordingly. Excel VBA Dynamic Forms are not difficult to do but may take some time testing until you get the right appearance of the form after additional choices are revealed. My Build a Better Personal Budget Tool page can be found her
Excel VBA Dynamic Form make for a better user experience
Excel VBA Dynamic Form make for a better user experience 
Private Sub cbBiWeekly_Click()

    ' the user has clicked the biweekly checkbox and if its value is true
    ' we then send it to the FreqSetup subroutine to dynamically modify the
    ' form
    
    If cbBiWeekly.Value = True Then
    Call FreqSetup("cbBiWeekly")
    End If

End Sub


Sub FreqSetup(sCntrl As String)

Dim myControl As Control
Dim x As Integer

' this first rather tedious section manages the checkboxes so only one
' can be checked at one time ....
If sCntrl = "cbAnnually" Then
Me.cbBiMonthly.Value = False
Me.cbBiWeekly.Value = False
Me.cbMonthly.Value = False
Me.cbWeekly.Value = False
Me.cbQuarterly.Value = False

ElseIf sCntrl = "cbBiMonthly" Then
Me.cbAnnually.Value = False
Me.cbBiWeekly.Value = False
Me.cbMonthly.Value = False
Me.cbWeekly.Value = False
Me.cbQuarterly.Value = False

ElseIf sCntrl = "cbBiWeekly" Then
Me.cbBiMonthly.Value = False
Me.cbAnnually.Value = False
Me.cbMonthly.Value = False
Me.cbWeekly.Value = False
Me.cbQuarterly.Value = False

ElseIf sCntrl = "cbMonthly" Then
Me.cbBiMonthly.Value = False
Me.cbBiWeekly.Value = False
Me.cbAnnually.Value = False
Me.cbWeekly.Value = False
Me.cbQuarterly.Value = False

ElseIf sCntrl = "cbWeekly" Then
Me.cbBiMonthly.Value = False
Me.cbBiWeekly.Value = False
Me.cbMonthly.Value = False
Me.cbAnnually.Value = False
Me.cbQuarterly.Value = False

ElseIf sCntrl = "cbQuarterly" Then
Me.cbBiMonthly.Value = False
Me.cbBiWeekly.Value = False
Me.cbMonthly.Value = False
Me.cbAnnually.Value = False
Me.cbWeekly.Value = False
Else
End If

' this section instructs if the pay/charge period is monthly or biMonthly
' hide checkboxes cb1 to cb12 and resize the form to hide them ...
If sCntrl = "cbMonthly" Or sCntrl = "cbBiMonthly" Then

    ' hide the months
    For x = 1 To 12
        sMisc = "cb" & x
        Controls(sMisc).Visible = False
    Next x
    
    ' adjust form sizing ...
    Me.Frame1.Height = 48
    Me.btnClose.Top = 195
    Me.btnAddRow.Top = 195
    Me.btnDeleteRow.Top = 195
    Me.btnSave.Top = 195
    Me.Height = 260
    
' this section instructs if the pay/charge period is weekly or biweekly
' unhides checkboxes cb1 to cb7, name them for the days of the week
' and resize the form to show them ...

ElseIf sCntrl = "cbBiWeekly" Or sCntrl = "cbWeekly" Then
    For x = 1 To 7
        sMisc = "cb" & x
        Controls(sMisc).Visible = True
        Controls(sMisc).Caption = Left(WeekdayName(x), 3)
    Next x
        
    For x = 8 To 12
        sMisc = "cb" & x
        Controls(sMisc).Visible = falase
    Next x

    ' adjust form sizing ...
    Me.lblMorW.Caption = "Please select a day of the week"
    Me.Frame1.Height = 102
    Me.btnClose.Top = 252
    Me.btnDeleteRow.Top = 252
    Me.btnAddRow.Top = 252
    Me.btnSave.Top = 252
    Me.Height = 315

' this section instructs if the pay/charge period is Quarterly
' unhides checkboxes cb1 to cb12, name them for the Months of the year
' and resize the form to show them ...

ElseIf sCntrl = "cbQuarterly" Then

    ' hide the months
    For x = 1 To 12
        sMisc = "cb" & x
        Controls(sMisc).Visible = True
        Controls(sMisc).Caption = Left(MonthName(x), 3)
    Next x
    
   ' adjust form sizing ...

    Me.lblMorW.Caption = "Please select Month (Hint: for Quarterly first Month)"
    Me.Frame1.Height = 102
    Me.btnClose.Top = 252
    Me.btnAddRow.Top = 252
    Me.btnDeleteRow.Top = 252
    Me.btnSave.Top = 252
    Me.Height = 315


' this section instructs if the pay/charge period is Annual (the default)
' unhides checkboxes cb1 to cb12, name them for the Months of the year
' and resize the form to show them ...

Else
     For x = 1 To 12
        sMisc = "cb" & x
        Controls(sMisc).Visible = True
        Controls(sMisc).Caption = Left(MonthName(x), 3)
    Next x
    

   ' adjust form sizing ...
    Me.lblMorW.Caption = "Please select Annual Month"
    Me.Frame1.Height = 102
    Me.btnClose.Top = 252
    Me.btnAddRow.Top = 252
    Me.btnDeleteRow.Top = 252
    Me.btnSave.Top = 252
    Me.Height = 315
End If

End Sub
If you enjoyed this post and would like to comment or share improvements please send your comments Here.  
If you would like to say hello and buy me a cup of coffee Click here.
Raymond Mills MBA, MS

Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle, Sequel Server and Sybase to give up their secrets.

Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” If you have a challenge with Excel, Access or Word and would like to speak with Ray, you can get his contact details by clicking here: Contact Me 

Comments are closed.