Force uppercase into a cell from a textbox
Posted by Sid on July 22, 2001 4:56 AM
I am using this to force uppercase in the linked cell
Private Sub TextBox2_Change()
Dim rCells As Range
For Each rCells In Selection
rCells = UCase(rCells.Text)
Next
End Sub
It works, but after it is used it cancels the following located on another sheet in the same workbook
Private Sub Worksheet_Change(ByVal Target As Range)
'Prevent spaces in fitting list
Dim c As Object
Dim Offender As String
For Each c In Target.Cells
If Left(c, 1) = " " Then
Offender = c.Address
MsgBox "Mr. NSI - If you start the Fitting No. with a space character you will not be able to search for it later!", vbExclamation
Application.Undo
End If
Next
'Insert Date next to name
Dim keyRange As Range
Set keyRange = Range("h3:h65536", "J3:J65336")
Application.EnableEvents = False
If Not Intersect(keyRange, Target) Is Nothing Then
If Target <> "" Then
Target.Offset(0, 1) = Date
Else
Target.Offset(0, 1) = ""
End If
End If
Application.EnableEvents = True
'Make uppercase
Dim rCells As Range
Application.EnableEvents = False
If Not Intersect(Target, Columns("f:f")) Is Nothing Then
For Each rCells In Intersect(Target, Columns("f:f"))
rCells = UCase(rCells.Text)
Next
End If
Application.EnableEvents = True
End Sub
The above works fine until the textbox is used.
Any ideas much appreciated
Cheers Sid
Posted by Barrett (South London) on July 22, 2001 10:05 AM
Look a diff way the data i input i tend to format as i go. that means user UPPER and the formula ot Text you input, copy all over F:F and the select cell hit F2 and add to the formula that way you control the UPPER or do you have thousands to convert.. if so convert the lot via VA and the add or re run the convert as reqd... 65536 rows can be done in a second or less to any format
let me know but please give data and give as much as poss details..
I use button always so on row A i have many buttons to do all my tricks and convert say as i need... this way i have control over the data
Posted by Sid on July 22, 2001 3:19 PM
I need to control cell input by as data is added by numerous users. This cell is used as a search reference to a list, also input by various users - the list is controlled by the Private Sub Worksheet_Change(ByVal Target As Range) which gets knocked out by the text box control.
Part of the search code uses this
BoatValue = Range("C" & X).Value
BoatRequest = Range("INDEX!B3").Value
If BoatValue = BoatRequest Then
This comparison is case sesitive. Is there a way to make it non case sensitive?
Posted by Ivan F Moala on July 22, 2001 8:29 PM
If you are comparing text then no need to
put it into upper case.....2 ways
1) Use Option Compare Text @ the start of your module
ie. In the Declarations part.
2) compare them via code as ALL ucase eg.
BoatValue = UCase(Range("C" & X).Value)
BoatRequest = UCase(Range("INDEX!B3").Value)
If BoatValue = BoatRequest Then
HTH
Ivan
Posted by Sid on July 23, 2001 12:15 AM
Cheers
Sid
