Code VBA add-in to make a Userform. What does this code mean and how do I use it? Trying to learn

chips

New Member
Joined
Oct 21, 2008
Messages
45
VBA Code:
Option Explicit
Public IsCancelled As Boolean

Private Sub UserForm_Initialize()
    IsCancelled = True
End Sub

Private Sub btnANAClose_Click()
    Me.Hide
End Sub

Private Sub btnANAOk_Click()
    If IsInputOk Then
    'ANAFormToSheet
        IsCancelled = False
        'Me.Hide
    End If
End Sub

Public Sub SetValues(udtANADataEntry As ANADataEntry)
    With udtANADataEntry
        SetValue Me.lbxANADate, .Date
        SetValue Me.lbxANAShift, .Shift
        SetValue Me.lbxANANames, .Name
    End With
End Sub

Public Sub GetValues(ByRef udtANADataEntry As ANADataEntry)
    With udtANADataEntry
        .Date = GetValue(Me.lbxANADate, TypeName(.Date))
        .Shift = GetValue(Me.lbxANAShift, TypeName(.Shift))
        .Name = GetValue(Me.lbxANANames, TypeName(.Name))
    End With
End Sub

Private Function IsInputOk() As Boolean
Dim ctl As MSForms.Control
Dim strMessage As String
    IsInputOk = False
    For Each ctl In Me.Controls
        If IsInputControl(ctl) Then
            If IsRequired(ctl) Then
                If Not HasValue(ctl) Then
                    strMessage = ControlName(ctl) & " must have value"
                End If
            End If
            If Not IsCorrectType(ctl) Then
                strMessage = ControlName(ctl) & " is not correct"
            End If
        End If
        If Len(strMessage) > 0 Then
            ctl.SetFocus
            GoTo HandleMessage
        End If
    Next
    IsInputOk = True
HandleExit:
    Exit Function
HandleMessage:
    MsgBox strMessage
    GoTo HandleExit
End Function

Public Sub FillList(ControlName As String, Values As Variant)
    With Me.Controls(ControlName)
        Dim iArrayForNext As Long
        .Clear
        For iArrayForNext = LBound(Values) To UBound(Values)
            .AddItem Values(iArrayForNext)
        Next
    End With
End Sub

Private Function IsCorrectType(ctl As MSForms.Control) As Boolean
Dim strControlDataType As String, strMessage As String
Dim dummy As Variant
    strControlDataType = ControlDataType(ctl)
On Error GoTo HandleError
    Select Case strControlDataType
    Case "Boolean"
        dummy = CBool(GetValue(ctl, strControlDataType))
    Case "Byte"
        dummy = CByte(GetValue(ctl, strControlDataType))
    Case "Currency"
        dummy = CCur(GetValue(ctl, strControlDataType))
    Case "Date"
        dummy = CDate(GetValue(ctl, strControlDataType))
    Case "Double"
        dummy = CDbl(GetValue(ctl, strControlDataType))
    Case "Decimal"
        dummy = CDec(GetValue(ctl, strControlDataType))
    Case "Integer"
        dummy = CInt(GetValue(ctl, strControlDataType))
    Case "Long"
        dummy = CLng(GetValue(ctl, strControlDataType))
    Case "Single"
        dummy = CSng(GetValue(ctl, strControlDataType))
    Case "String"
        dummy = CStr(GetValue(ctl, strControlDataType))
    Case "Variant"
        dummy = CVar(GetValue(ctl, strControlDataType))
    End Select
    IsCorrectType = True
HandleExit:
    Exit Function
HandleError:
    IsCorrectType = False
    Resume HandleExit
End Function

Private Function ControlDataType(ctl As MSForms.Control) As String
    Select Case ctl.Name
    Case "lbxANADate": ControlDataType = "Date"
    Case "lbxANAShift": ControlDataType = "Variant"
    Case "lbxANANames": ControlDataType = "Variant"
    End Select
End Function

Private Function ControlName(ctl As MSForms.Control) As String
On Error GoTo HandleError
    If Not ctl Is Nothing Then
        ControlName = ctl.Name
        Select Case TypeName(ctl)
        Case "TextBox", "ListBox", "ComboBox"
            If ctl.TabIndex > 0 Then
                Dim c As MSForms.Control
                For Each c In Me.Controls
                    If c.TabIndex = ctl.TabIndex - 1 Then
                        If TypeOf c Is MSForms.Label Then
                            ControlName = c.Caption
                        End If
                    End If
                Next
            End If
        Case Else
            ControlName = ctl.Caption
        End Select
    End If
HandleExit:
    Exit Function
HandleError:
    Resume HandleExit
End Function

Private Function IsRequired(ctl As MSForms.Control) As Boolean
    Select Case ctl.Name
    Case "lbxANADate", "lbxANAShift", "lbxANANames"
        IsRequired = True
    Case Else
        IsRequired = False
    End Select
End Function

Private Function IsInputControl(ctl As MSForms.Control) As Boolean
    Select Case TypeName(ctl)
    Case "TextBox", "ComboBox", "ListBox", "CheckBox", "OptionButton", "ToggleButton"
        IsInputControl = True
    Case Else
        IsInputControl = False
    End Select
End Function

Private Function HasValue(ctl As MSForms.Control) As Boolean
    Dim var As Variant
    var = GetValue(ctl, "Variant")
    If IsNull(var) Then
        HasValue = False
    ElseIf Len(var) = 0 Then
        HasValue = False
    Else
        HasValue = True
    End If
End Function


So used code vba add in to generate a userform with 3 listboxs. but I am lost as to why so much code is needed. what does it all do and where do I add the things I need??

Private Function GetValue(ctl As MSForms.Control, strTypeName As String) As Variant
On Error GoTo HandleError
    Dim value As Variant
    value = ctl.value
    If IsNull(value) And strTypeName <> "Variant" Then
        Select Case strTypeName
        Case "String"
            value = ""
        Case Else
            value = 0
        End Select
    End If
HandleExit:
    GetValue = value
    Exit Function
HandleError:
    Resume HandleExit
End Function

Private Sub SetValue(ctl As MSForms.Control, value As Variant)
On Error GoTo HandleError
    ctl.value = value
HandleExit:
    Exit Sub
HandleError:
    Resume HandleExit
End Sub
 
Last edited by a moderator:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
709
Office Version
  1. 365
Platform
  1. Windows
If people don't go out of their way to go through the code, they won't discover the part where you have inadvertently inserted your more detailed explanation / question:

So used code vba add in to generate a userform with 3 listboxs. but I am lost as to why so much code is needed. what does it all do and where do I add the things I need??

This is a limited picture of what's going on, so everything I say below should be understood with the caveat that I'm only guessing. This is tricky to do without much context, but generally, you can work out what is happening in the code by looking at the Sub / Function names. For example,

VBA Code:
Sub FillList(ControlName As String, Values As Variant)

would appear to fill a listbox with some values.
IsInputControl appears to check to make sure the userform control (e.g. textbox) is one that accepts input.
IsCorrectType appears to check to make sure that something is using the correct data type.

and so on.

It might seem a little verbose, but you will find that code will be longer than you expect when it is designed to deal with any and all possibilities and to handle and prevent as many errors as possible.
 
Solution

Forum statistics

Threads
1,147,481
Messages
5,741,400
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top