User Form Text box number to Excel

[Nikijune]

Hi all,

Apologies for the terrible title.

I have a user form where the user enters the total number of hours they were at work for. In most cases it is 7 and everything is fine, however, for part time staff, when they enter 5.5 and click the submit button, the data that gets published to excel shows '0'

I have tried setting the max length to 5. I have formatted the textbox within the user form to '0.00' and '0.0' but it is still not publishing the number entered.

Any ideas excel fam?

 

[Norie]

How are you submitting the data to the worksheet?

 

[Nikijune]

Hi Norie,

There is a submit button, which, once the form is completed, the user clicks and all fields and published to an excel database.

Thanks

 

[mikerickson]

What is the code for the Submit button's Click event?

 

[Nikijune]

 Private Sub SUBMIT_Click()
   
   If HoursWorked.Value = "" Then
            MsgBox "'HOURS WORKED' FIELD IS EMPTY"
    Else
    
    DatabaseAdd Me
    
    Unload Me
    
    End If
End Sub

 

[mikerickson]

What is the code for the sum DatabaseAdd

 

[Rick Rothstein]

What is the code for the sum DatabaseAdd

So you are not confused by the typo Mike made, the red highlighted word above should have been sub (short for subroutine), not sum.

 

[Nikijune]

Hi Rick and Mike

Thanks both for your help so far

Below is the sub for DatabaseAdd

Sub DatabaseAdd(ByVal Form As Object)
Dim wbDatabase As Workbook
    Dim FileName As String, UsersName As String
    Dim data() As Variant
    Dim Lastrow As Long
    Dim i As Integer
    
'References cells that have filepath in and agents DKX Number
    FileName = Settings.Range("D17").Value
    UsersName = Settings.Range("D24").Value
    
    On Error GoTo ExitSub
    
'references Form Controls macro with tasks detailed
        ReDim data(1 To UBound(FormControls))
'build array
        For i = 1 To UBound(FormControls)
            With Form.Controls(FormControls(i))
'check required field
            If IsRequired(Form, .Name) Then Exit Sub
                If IsDate(.Text) Then
                    data(i) = DateValue(.Text)
                Else
                    data(i) = .Text
                End If
            End With
        Next i
            
            Application.ScreenUpdating = False
'open database
            Set wbDatabase = DatabaseOpen(FileName:=FileName, ReadOnly:=False)
            If wbDatabase Is Nothing Then GoTo ExitSub
            
            With wbDatabase
                With .Sheets(1)
'get next row
                    Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
'place array to range
                    .Cells(Lastrow, 1).Resize(1, UBound(data)).Value = data
'format record
                DatabaseFormat Target:=.Cells(Lastrow, 1).Resize(1, UBound(data))
                End With
'sort record
'                DatabaseSort sh:=.Sheets(1), SortDirection:=xlDescending
'close file & save
                .Close True
            End With
'clear from memory
        Set wbDatabase = Nothing
'clear form
        ClearForm Form
  'report success
        MsgBox UsersName & Chr(10) & Chr(10) & "New Record Added To Database" & Chr(10) & Chr(10) & "Stats submitted at " & _
        Format(Now, "dd/mm/yy hh:mm:ss"), 48, "New Record Added"
ExitSub:
'close database if open
If Not wbDatabase Is Nothing Then wbDatabase.Close False
Application.ScreenUpdating = True
'tell user what went wrong
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

 

[mikerickson]

There are references to an array FormControls and to a function IsRequired. What are those?

 

[Nikijune]

Hi Mike,

FormControls is the Array of field names that I are within the userform that are being populated into the database spreadsheet.

Option Base 1
Function FormControls() As Variant
    
    FormControls = Array("DateTextBox", "ComboBox1", "WorkAbsenceType", "HoursWorked", "Name1", "Name2", "Name3", "Name4", "Name5", "Name6", _
    "Name7", "Name8", "Name9", "Name10", "Name11", "Name12", "Name13", "Name14", "Name15", "Name16", "Name17", "Name18", "Name19", "Name20", _
    "Name21", "Name22", "Name23", "Name24", "Name25", "Name26", "Name27", "Name28", "Name29", "Name30", _
    "Qty1", "Qty2", "Qty3", "Qty4", "Qty5", "Qty6", "Qty7", "Qty8", "Qty9", "Qty10", "Qty11", "Qty12", "Qty13", "Qty14", "Qty15", "Qty16", _
    "Qty17", "Qty18", "Qty19", "Qty20", "Qty21", "Qty22", "Qty23", "Qty24", "Qty25", "Qty26", "Qty27", "Qty28", "Qty29", "Qty30", _
    "Date1", "Date2", "Date3", "Date4", "Date5", "Date6", "Date7", "Date8", "Date9", "Date10", "Date11", "Date12", "Date13", "Date14", _
    "Date15", "Date16", "Date17", "Date18", "Date19", "Date20", "Date21", "Date22", _
    "TeamName1", "TeamName2", "TeamName3", "TeamName4", "TeamName5")

End Function

Isrequired checks fields and tells the user if it is a required field to be populated.

'function checks any field tagged as a REQUIRED entry
Function IsRequired(ByVal Form As Object, ByVal ControlName As String) As Boolean
    Dim RequiredControl As String
    With Form.Controls(ControlName)
'required field
        If UCase(.Tag) = "REQUIRED" Then
'no entry made by user
            IsRequired = Len(.Text) = 0
            If IsRequired Then
'get field name caption
                RequiredControl = GetCaption(Form.Controls(ControlName))
'tell user
                MsgBox RequiredControl & Chr(10) & Chr(10) & "Entry Required.", 16, "Entry Required": .SetFocus
            End If
        End If
    End With
End Function

Function GetCaption(ByVal TextBox As Object) As String
    Dim cap As Object
    For Each cap In TextBox.Parent.Controls
        If TypeName(cap) = "Label" Then
            If cap.Top = TextBox.Top Then
            If TextBox.Left - cap.Left < 130 Then GetCaption = cap.Caption: Exit Function
            End If
        End If
    Next cap
End Function

Many Thanks