Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text to upper case format
Hi All
I have prepared the code for the worksheet as follows. This code converts the text to upper case format from any cell of the worksheet. Also it checks for the specific text as well. This code works fine for all columns and rows in the worksheet. But I want to apply this code to column B only instead of whole worksheet. Means whatever I will type in Column B should be changed to Uppercase and also check for particular text, for example QUARTZ and SODA LIME. Any help would be greatly appreciated. Kind Regards Harish Private Sub Worksheet_Change(ByVal Target As Range) If Target.HasFormula = True Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If Application.EnableEvents = False Target.Value = UCase(Target.Text) If Target.Value = "QUARTZ" Or Target.Value = "SODA LIME" Then Else MsgBox "Check the Spell" Target.Select End If Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text to upper case format
if intersect(target, me.range("B:B")) is nothing then
exit sub end if (after the .cells.count check) Harish wrote: Hi All I have prepared the code for the worksheet as follows. This code converts the text to upper case format from any cell of the worksheet. Also it checks for the specific text as well. This code works fine for all columns and rows in the worksheet. But I want to apply this code to column B only instead of whole worksheet. Means whatever I will type in Column B should be changed to Uppercase and also check for particular text, for example QUARTZ and SODA LIME. Any help would be greatly appreciated. Kind Regards Harish Private Sub Worksheet_Change(ByVal Target As Range) If Target.HasFormula = True Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If Application.EnableEvents = False Target.Value = UCase(Target.Text) If Target.Value = "QUARTZ" Or Target.Value = "SODA LIME" Then Else MsgBox "Check the Spell" Target.Select End If Application.EnableEvents = True End Sub -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text to upper case format
Add this bit of code:
If Target.Column < 2 Then Exit Sub End If RBS "Harish" wrote in message ... Hi All I have prepared the code for the worksheet as follows. This code converts the text to upper case format from any cell of the worksheet. Also it checks for the specific text as well. This code works fine for all columns and rows in the worksheet. But I want to apply this code to column B only instead of whole worksheet. Means whatever I will type in Column B should be changed to Uppercase and also check for particular text, for example QUARTZ and SODA LIME. Any help would be greatly appreciated. Kind Regards Harish Private Sub Worksheet_Change(ByVal Target As Range) If Target.HasFormula = True Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If Application.EnableEvents = False Target.Value = UCase(Target.Text) If Target.Value = "QUARTZ" Or Target.Value = "SODA LIME" Then Else MsgBox "Check the Spell" Target.Select End If Application.EnableEvents = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text to upper case format
Thanks for your help.That works fine now.
Regards Harish "RB Smissaert" wrote: Add this bit of code: If Target.Column < 2 Then Exit Sub End If RBS "Harish" wrote in message ... Hi All I have prepared the code for the worksheet as follows. This code converts the text to upper case format from any cell of the worksheet. Also it checks for the specific text as well. This code works fine for all columns and rows in the worksheet. But I want to apply this code to column B only instead of whole worksheet. Means whatever I will type in Column B should be changed to Uppercase and also check for particular text, for example QUARTZ and SODA LIME. Any help would be greatly appreciated. Kind Regards Harish Private Sub Worksheet_Change(ByVal Target As Range) If Target.HasFormula = True Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If Application.EnableEvents = False Target.Value = UCase(Target.Text) If Target.Value = "QUARTZ" Or Target.Value = "SODA LIME" Then Else MsgBox "Check the Spell" Target.Select End If Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert text to all upper case without having to retype everythin. | New Users to Excel | |||
Excel: how to convert text to upper case upon entry? | Excel Discussion (Misc queries) | |||
Format cell to convert to Upper case | Excel Discussion (Misc queries) | |||
Conver Text to upper case format | Excel Programming | |||
How to convert a column of text to upper and lower case? | Excel Discussion (Misc queries) |