textbox cannot enter letters and numbers

[Patriot2879]

Hi hope youcan help me please, I have attached the file below which I am stuck on, I havea user form ‘enter expenses’ but when I try to enter number/letter combinationin the boxes for Airfare, accommodation, ground transport and food and drink,the box goes red when I click on update and it doesn’t transfer the informationto the enter expenses sheet, I hope you can help me please?
https://www.dropbox.com/s/r6k2c5lln3m1ij8/Copy%20of%20Excel-Forms-Insert-Update-Delete.xlsm?dl=0

 

[Patriot2879]

Hi thanks for getting back to me. What do you mean commenting it out instead? Thank you for the help

 

[Rick Rothstein]

Hi thanks for getting back to me. What do you mean commenting it out instead? Thank you for the help

How familiar are you with VBA coding? To comment out a VBA programming statement, you put a apostrophe (') at the beginning of the statement. You would have to do that to the Else statement that I told you about and every INDENTED statement below it (stop at the End If that is not indented... and do not comment out that ending End If statement).

 

[Patriot2879]

Hi I am still learning vba code. I'm a beginner. So I dont delete but just put an '?

 

[Rick Rothstein]

Hi I am still learning vba code. I'm a beginner. So I dont delete but just put an '?

You can delete it if you are confident that what I am guessing you need to do is correct and if you are sure you won't ever need to see that code again, but my recommendation is to comment them out and leave them there "just in case". And yes, you just put a ' at the beginning of each line of code I told you about. By the way, once you comment out a line of code, it will turn green and VBA will completely ignore it (which is why you do not have to delete it). If you ever need to re-establish a line of code you comment out, deleting the ' will reactivate the line of code.

 

[Patriot2879]

Ok thank you I shall try this. Thanks again

 

[Patriot2879]

hi i tried this but still cant get it to work please can yhou still help, i think i must be doing it wrong. i put the ' on the code below

 'Select Case TypeName(Ctrl)        
            'Case "TextBox"
                'If the text box is empty
                'If Ctrl.Value = "" Then
                
                    'If the text box is not optional i.e. it must contain something
                    'If Ctrl.Tag <> "Optional" Then
    
                        'FlagError Ctrl
                       ' ErrorsFound = ErrorsFound + 1
                
                    'Else
                
                        'ClearError Ctrl
                        'At this point we have an empty expenses field.
                        'Record this for now and we'll check again later
                        'CompletedExpenses = CompletedExpenses - 1
                    
                    'End If

 

[Rick Rothstein]

hi i tried this but still cant get it to work please can yhou still help, i think i must be doing it wrong. i put the ' on the code below

 'Select Case TypeName(Ctrl)        
            'Case "TextBox"
                'If the text box is empty
                'If Ctrl.Value = "" Then
                
                    'If the text box is not optional i.e. it must contain something
                    'If Ctrl.Tag <> "Optional" Then
    
                        'FlagError Ctrl
                       ' ErrorsFound = ErrorsFound + 1
                
                    'Else
                
                        'ClearError Ctrl
                        'At this point we have an empty expenses field.
                        'Record this for now and we'll check again later
                        'CompletedExpenses = CompletedExpenses - 1
                    
                    'End If

You commented out more lines of code than I said to and to the wrong part of the code. Here is the entire CheckForErrors function with the modification I wanted you to do. Make sure you find this line of code...

Function CheckForErrors() As Integer

and then find the first...

End Function

after it... delete those lines and everything between them and then paste the following code in its place...

Function CheckForErrors() As Integer

    Dim ErrorsFound As Integer
    Dim CompletedExpenses As Integer
    Const ExpensesFields As Integer = 5
    Dim aDecimal As Double
    Dim Ctrl As MSForms.Control

    ErrorsFound = 0
    CompletedExpenses = ExpensesFields

    For Each Ctrl In ExpensesForm.Controls
        
        Select Case TypeName(Ctrl)
        
            Case "TextBox"
                'If the text box is empty
                If Ctrl.Value = "" Then
                
                    'If the text box is not optional i.e. it must contain something
                    If Ctrl.Tag <> "Optional" Then
    
                        FlagError Ctrl
                        ErrorsFound = ErrorsFound + 1
                
                    Else
                
                        ClearError Ctrl
                        'At this point we have an empty expenses field.
                        'Record this for now and we'll check again later
                        CompletedExpenses = CompletedExpenses - 1
                    
                    End If
                
[B][COLOR="#FF0000"]'                Else
'
'                    If Ctrl.Name = "Description" Or IsAcceptedNumber(Ctrl) Then
'
'                        ClearError Ctrl
'
'                    Else
'
'                        FlagError Ctrl
'                        ErrorsFound = ErrorsFound + 1
'
'                    End If[/COLOR][/B]
                    
                End If
                
                
            Case "OptionButton"
                If ReceiptsYes.Value = False And ReceiptsNo.Value = False Then
                    
                    FlagError ReceiptsFrame
                    ErrorsFound = ErrorsFound + 1
                    
                Else
                
                    ClearError ReceiptsFrame
                    'Must make frame border visible again
                    ReceiptsFrame.BorderStyle = fmBorderStyleSingle
                
                End If
                
                
            Case "ComboBox"
                If Ctrl.ListIndex = -1 And (Ctrl.Name = "ClientName" Or Ctrl.Name = "StaffName") Then
                
                    FlagError Ctrl
                    ErrorsFound = ErrorsFound + 1
                
                Else
                
                    ClearError Ctrl
                
                End If
                
                
        End Select
        
    Next Ctrl
    
    
    'Chosen date can not be after today
    If Calendar1.Value > Date Then
    
        FlagError CalendarFrame
        ErrorsFound = ErrorsFound + 1
        
    Else
    
        ClearError CalendarFrame
    
    End If
    
    'If all expenses fields are empty
    If CompletedExpenses = 0 Then
    
        'Check each expenses field and flag the ones in error
        For Each Ctrl In ExpensesForm.Controls
        
            Select Case TypeName(Ctrl)
        
                Case "TextBox"
                    If Ctrl.Value = "" And Ctrl.Tag = "Optional" Then
    
                        FlagError Ctrl
                
                    End If
                                    
            End Select
        
        Next Ctrl
        
        CheckForErrors = 1
        
    Else
    
        CheckForErrors = ErrorsFound
        
    End If
    
End Function

or, alternately, look at the lines of code I commented out (the apostrophes are at the very beginning of the code lines) and duplicate them in your existing code. If you want to do it this way, I highlighted the lines of code in red that need to be commented out.

 

[Patriot2879]

thank you that works great now where did i go wrong? sorry i am still learning, thanks for your patience.