User Form Text box number to Excel

Nikijune

Board Regular
Joined
Aug 16, 2016
Messages
51
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?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
How are you submitting the data to the worksheet?
 
Upvote 0
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 :)
 
Upvote 0
Code:
 Private Sub SUBMIT_Click()
   
   If HoursWorked.Value = "" Then
            MsgBox "'HOURS WORKED' FIELD IS EMPTY"
    Else
    
    DatabaseAdd Me
    
    Unload Me
    
    End If
End Sub
 
Upvote 0
Hi Rick and Mike

Thanks both for your help so far :)

Below is the sub for DatabaseAdd


Code:
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
 
Upvote 0
Hi Mike,

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

Code:
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.

Code:
'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 :)
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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
Back
Top