textbox cannot enter letters and numbers

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
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

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Hi thanks for getting back to me. What do you mean commenting it out instead? Thank you for the help
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,412
Office Version
  1. 2010
Platform
  1. Windows
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).
 
Last edited:

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
Hi I am still learning vba code. I'm a beginner. So I dont delete but just put an '?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,412
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Last edited:

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081

ADVERTISEMENT

Ok thank you I shall try this. Thanks again
 

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
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
Code:
 '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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,412
Office Version
  1. 2010
Platform
  1. Windows
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
Code:
 '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...
Code:
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.
 
Last edited:

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,081
thank you that works great now :) where did i go wrong? sorry i am still learning, thanks for your patience.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,825
Messages
5,544,545
Members
410,619
Latest member
gregor222
Top