• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

UserForm Entries Make Them Case Insensitive

Y

Yodelayheewho

Member
I'm building a UserForm (I'm very, very new at this)
1. I want to be able to enter lowercase or uppercase and still have the VBA work. For example, to Search and Display a row, I enter a Shop Order Number like ASO-60000. In order for the data to appear, I have to enter "ASO", I would like to be able to enter "aso".
2. With that said, I would like the VBA to format the Shop Order Number in caps. Sounds crazy, but I'm developing this for others to use.
The only other possible option is a pop up message asking users to capitalize.
Please see sample attached.
Thank you in advance!!!
 

Attachments

  • Sample_Systems Order Entry Master_VBA.xlsm
    155.3 KB · Views: 8
Add this code in the your data entry form. (masterform)
Code: 
Private Sub TextShopOrderNumber_Change()
    TextShopOrderNumber.Text = UCase(TextShopOrderNumber.Text)
End Sub
 
Whoa! That is really cool. It simply 'forces' uppercase when entering. This is awesome! I'm such a geek:).

I have more than 100 columns of data, what if I wanted to force capitalization for all the columns? Is there a way to write the code without having to add a line for every column? I have this same issue with other codes. If I have to add or subtract a column, it because an editing challenge. In the following example, I've added two more, but I have a lot more that have to be capitalized.
Code: 
Private Sub TextShopOrderNumber_Change()
    TextShopOrderNumber.Text = UCase(TextShopOrderNumber.Text)
    TextProposalTerms.Text = UCase(TextProposalTerms.Text)
    TextShippingInstructions.Text = UCase(TextShippingInstructions.Text)
End Sub
Thank you so very much!
 
Yes it is possible, but you need to use a class module
In the class module
Code: 
Public WithEvents MyTBoxes As MSForms.TextBox
Private Sub MyTBoxes_Change()
    With Me.MyTBoxes
        .Text = UCase(.Text)
    End With
End Sub

Then in your Userform code
Code: 
Dim colTBoxes As New Collection
Private Sub UserForm_Initialize()
    Dim cTBoxes As clsTBoxes
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            Set cTBoxes = New clsTBoxes
            Set cTBoxes.MyTBoxes = Ctrl
            colTBoxes.Add cTBoxes
        End If
    Next Ctrl
End Sub

Dim colTBoxes As New Collection
This line of code goes on top of all your code.
 
Last edited:
Off topic
All this
Code: 
TextPrefix = ""
 TextE10Status = ""
 TextSuffix = ""
TextShopOrderNumber = ""
 TextEmailSubjectLine = ""
 TextNotes = ""
 TextStage = ""
 TextStartDate = ""
 TextStageDue = ""
 TextEndDate = ""
 TextProposalNumber = ""
 TextSalespersonInitials = ""
 TextSalesperson = ""
 TextProposalDate = ""
 TextLeadTime = ""
 TextPromisedDate = ""
 TextProposalTerms = ""
 TextExpirationDate = ""
 TextCost = ""
 TextMargin = ""
 TextPONumber = ""
 TextPODate = ""
 TextPOReceivedDate = ""
 TextPOAmount = ""
 TextPOTerms = ""
 TextAccessTermsCode = ""
TextShipVia = ""
 TextShipType = ""
TextShipCharges = ""
TextShippingInstructions = ""
 TextSalesOrderNumber = ""
 TextQuoteNumber = ""
 TextProjectManagerInitials = ""
 TextProjectManager = ""
 TextElectronicEngineer = ""
 TextSystemDescription = ""
 TextSCode = ""
 TextBMTH = ""
 TextTransferOrderNumber = ""
 TextMultipleLines = ""
 TextStandardPartsIncluded = ""
 TextInstallationDays = ""
 TextStartUpDays = ""
 TextTrainingDaysOnsite = ""
 TextTrainingDaysToledo = ""
 TextVendorFieldServiceDays = ""
 TextServiceTechnician = ""
 TextStandardHours1and2 = ""
 TextStandardHours3 = ""
 TextSaturdaySundayorHolidays = ""
 TextAdditionalOvertime = ""
 TextTravelLessThan8Hours = ""
 TextTravelMoreThan8Hours = ""
 TextAirfare = ""
 TextHotel = ""
 TextCarRental = ""
 TextMeals = ""
 TextMileage = ""
 TextParking = ""
 TextServiceParts1 = ""
 TextServiceParts2 = ""
 TextBookingFees = ""
 TextOptionalDescription = ""
 TextTotal = ""
 TextServiceGroup = ""
 TextEnteredinE10 = ""
 TextConfirmationofPO = ""
 TextRequestApproval = ""
 TextRequestPM = ""
 TextPMAssigned = ""
 TextFoldersCopied = ""
 TextSOtoTeamPM = ""
 TextApproved = ""
 TextSOAtoCustomer = ""
 TextSOADateinE10 = ""
 TextEnteredinAccess = ""
 TextRequestInvoice = ""
 TextReceivedInvoice = ""
 TextInvoiceNumber = ""
 TextCustomerName = ""
 TextDiamondDistributor = ""
 TextAKAorNickname = ""
 TextCUSTID = ""
 TextAccessCUSTID = ""
 TextBillToName = ""
 TextBillToAddress1 = ""
 TextBillToAddress2 = ""
 TextBillToCity = ""
 TextBillToState = ""
 TextBillToZipCode = ""
 TextBillToCountry = ""
 TextAltBillToName = ""
 TextAltBillToID = ""
 TextTaxExempt = ""
 TextContact1Name = ""
 TextContact1Email = ""
 TextContact2Name = ""
 TextContact2Email = ""
 TextShipToID = ""
 TextAccessShipToID = ""
 TextShipToName = ""
 TextShipToAddress1 = ""
 TextShipToAddress2 = ""
 TextShipToCity = ""
 TextShipToState = ""
 TextShipToZipCode = ""
 TextShipToCountry = ""
 TextEndUserName = ""
 TextEndUserID = ""
 TextAccessEndUserID = ""
 TextRansburgReport = ""
 TextBGKReport = ""
 TextShippedYesorNo = ""
 TextShipDate = ""
 TextPromiseDateAKAShipDate = ""
 TextExpectedShipDateAKARecognizeRevenue = ""
 TextStatusUpdated = ""
Can you replace by
Code: 
For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
Next Ctrl
 
Belleke said:
Yes it is possible, but you need to use a class module
In the class module
Code: 
Public WithEvents MyTBoxes As MSForms.TextBox
Private Sub MyTBoxes_Change()
    With Me.MyTBoxes
        .Text = UCase(.Text)
    End With
End Sub

Then in your Userform code
Code: 
Dim colTBoxes As New Collection
Private Sub UserForm_Initialize()
    Dim cTBoxes As clsTBoxes
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            Set cTBoxes = New clsTBoxes
            Set cTBoxes.MyTBoxes = Ctrl
            colTBoxes.Add cTBoxes
        End If
    Next Ctrl
End Sub

Dim colTBoxes As New Collection
This line of code goes on top of all your code.
Click to expand...
So, this changes ALL textboxes? Right?
 
Belleke said:
Off topic
All this
Code: 
TextPrefix = ""
TextE10Status = ""
TextSuffix = ""
TextShopOrderNumber = ""
TextEmailSubjectLine = ""
TextNotes = ""
TextStage = ""
TextStartDate = ""
TextStageDue = ""
TextEndDate = ""
TextProposalNumber = ""
TextSalespersonInitials = ""
TextSalesperson = ""
TextProposalDate = ""
TextLeadTime = ""
TextPromisedDate = ""
TextProposalTerms = ""
TextExpirationDate = ""
TextCost = ""
TextMargin = ""
TextPONumber = ""
TextPODate = ""
TextPOReceivedDate = ""
TextPOAmount = ""
TextPOTerms = ""
TextAccessTermsCode = ""
TextShipVia = ""
TextShipType = ""
TextShipCharges = ""
TextShippingInstructions = ""
TextSalesOrderNumber = ""
TextQuoteNumber = ""
TextProjectManagerInitials = ""
TextProjectManager = ""
TextElectronicEngineer = ""
TextSystemDescription = ""
TextSCode = ""
TextBMTH = ""
TextTransferOrderNumber = ""
TextMultipleLines = ""
TextStandardPartsIncluded = ""
TextInstallationDays = ""
TextStartUpDays = ""
TextTrainingDaysOnsite = ""
TextTrainingDaysToledo = ""
TextVendorFieldServiceDays = ""
TextServiceTechnician = ""
TextStandardHours1and2 = ""
TextStandardHours3 = ""
TextSaturdaySundayorHolidays = ""
TextAdditionalOvertime = ""
TextTravelLessThan8Hours = ""
TextTravelMoreThan8Hours = ""
TextAirfare = ""
TextHotel = ""
TextCarRental = ""
TextMeals = ""
TextMileage = ""
TextParking = ""
TextServiceParts1 = ""
TextServiceParts2 = ""
TextBookingFees = ""
TextOptionalDescription = ""
TextTotal = ""
TextServiceGroup = ""
TextEnteredinE10 = ""
TextConfirmationofPO = ""
TextRequestApproval = ""
TextRequestPM = ""
TextPMAssigned = ""
TextFoldersCopied = ""
TextSOtoTeamPM = ""
TextApproved = ""
TextSOAtoCustomer = ""
TextSOADateinE10 = ""
TextEnteredinAccess = ""
TextRequestInvoice = ""
TextReceivedInvoice = ""
TextInvoiceNumber = ""
TextCustomerName = ""
TextDiamondDistributor = ""
TextAKAorNickname = ""
TextCUSTID = ""
TextAccessCUSTID = ""
TextBillToName = ""
TextBillToAddress1 = ""
TextBillToAddress2 = ""
TextBillToCity = ""
TextBillToState = ""
TextBillToZipCode = ""
TextBillToCountry = ""
TextAltBillToName = ""
TextAltBillToID = ""
TextTaxExempt = ""
TextContact1Name = ""
TextContact1Email = ""
TextContact2Name = ""
TextContact2Email = ""
TextShipToID = ""
TextAccessShipToID = ""
TextShipToName = ""
TextShipToAddress1 = ""
TextShipToAddress2 = ""
TextShipToCity = ""
TextShipToState = ""
TextShipToZipCode = ""
TextShipToCountry = ""
TextEndUserName = ""
TextEndUserID = ""
TextAccessEndUserID = ""
TextRansburgReport = ""
TextBGKReport = ""
TextShippedYesorNo = ""
TextShipDate = ""
TextPromiseDateAKAShipDate = ""
TextExpectedShipDateAKARecognizeRevenue = ""
TextStatusUpdated = ""
Can you replace by
Code: 
For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
Next Ctrl
Click to expand...
So, would the code look like this?
Code: 
'Clear Form Button'
Private Sub ClearForm_Click()
For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
Next Ctrl
 
Yodelayheewho said:
So, would the code look like this?
Code: 
'Clear Form Button'
Private Sub ClearForm_Click()
For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
Next Ctrl
Click to expand...
So, is the code below for the Delete Order Button correct when using your amazing condensed code?
Code: 
'Delete Order Button'
Private Sub DeleteOrder_Click() 'The first section of code deletes the record in Master'
    Dim Shop_Order_Number As String
    Shop_Order_Number = Trim(TextShopOrderNumber)
On Error Resume Next
    With Worksheets("Master")
i = WorksheetFunction.Match(Shop_Order_Number,.Range("D:D"),0)
If Err.Number=0 Then
.Rows(i).Delete
Else
Err.Clear
MsgBox "Unknown Shop Order Number", vbInformation, Shop_Order_Number
Exit Sub
End If
End With
'The following code clears the textboxes'
For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
Next Ctrl
 
Belleke said:
Yes, indeed.
Don't forget to add End Sub at the end.
Click to expand...
Like this?
Code: 
'Delete Order Button'
Private Sub DeleteOrder_Click() 'The first section of code deletes the record in Master'
    Dim Shop_Order_Number As String
    Shop_Order_Number = Trim(TextShopOrderNumber)
On Error Resume Next
    With Worksheets("Master")
i = WorksheetFunction.Match(Shop_Order_Number,.Range("D:D"),0)
If Err.Number=0 Then
.Rows(i).Delete
Else
Err.Clear
MsgBox "Unknown Shop Order Number", vbInformation, Shop_Order_Number
Exit Sub
End If
End With
'The following code clears the textboxes'
For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
Next Ctrl
End Sub
 
Fabulous!
Now, let's say I have more than one textbox, but less than all textboxes that I need to format as lowercase. In this example, I have two textboxes I want lowercase. Would it need to look like this, or is there a better way?
Code: 
'Forces Lowercase When Entering'
Private Sub TextContact1Email_Change()
    TextContact1Email.Text = LCase(TextContact1Email.Text)
End Sub

'Forces Lowercase When Entering'
Private Sub TextContact2Email_Change()
    TextContact2Email.Text = LCase(TextContact2Email.Text)
End Sub
 
Do you mean like this?
Code: 
'Forces lowercase on 2 or more textboxes when entering'
Private Sub TextLowerCase_AfterUpdate()
    TextContact1Email.Text = LCase(TextContact1Email.Text)
    TextContact2Email.Text = LCase(TextContact2Email.Text)
End Sub
 
Belleke said:
I looked at your questions, if you give me a few days, I gonna rework your userform.
Click to expand...
You are AWESOME! I'm sorry it took me a couple days to respond. I've been revising the Sample file with updates, so just in case, I attached the latest version.
Thank you again!!!!
 

Attachments

  • Sample_Systems Order Entry Master_VBA.xlsm
    217.3 KB · Views: 6
You must log in or register to reply here.
Back
Top