Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Auto Cell Formatting Uppercase
Hi Excel Forum,
I am using Excel for Windows 95 v7 (isn't there always one!): I do no have access to VBA Worksheet Change Event ByVal Target. However, i possible, I would like to change text entered in a cell to uppercase o data entry; I know how to change the text to uppercase once it i already entered. Can Excel version 7 VBA convert text to uppercase on data entry Columns involved C, G, J, and R. I have already used the On_Entry Event in this particular worksheet can it be used again but distinguishing a particular range of column "ONLY" to be affected by the uppercase formatting. Please advise of solutions within Excel V7 VBA. If possible, please explain with a working example. Assistance is appreciated. Kind regards, QT -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Auto Cell Formatting Uppercase
QTE,
Post the On_Entry Event code that you are currently using. HTH, Bernie MS Excel MVP "QTE " wrote in message ... Hi Excel Forum, I am using Excel for Windows 95 v7 (isn't there always one!): I do not have access to VBA Worksheet Change Event ByVal Target. However, if possible, I would like to change text entered in a cell to uppercase on data entry; I know how to change the text to uppercase once it is already entered. Can Excel version 7 VBA convert text to uppercase on data entry. Columns involved C, G, J, and R. I have already used the On_Entry Event in this particular worksheet, can it be used again but distinguishing a particular range of columns "ONLY" to be affected by the uppercase formatting. Please advise of solutions within Excel V7 VBA. If possible, please explain with a working example. Assistance is appreciated. Kind regards, QTE --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Auto Cell Formatting Uppercase
Hi Bernie,
Here is the On_Entry code: Bernie Deitrick wrote: [b]QTE, Post the On_Entry Event code that you are currently using. HTH, Bernie MS Excel MVP --- This is the Auto_Open File: Option Explicit Sub Auto_Open() Windows("office.xls").Visible = False Workbooks.Open("G:\Excel\department.xls").Sheets ("officedept") Application.Got Workbooks("department.xls").Sheets("officedept").R ange("AX5") Entry End Sub Sub Entry() Workbooks("department.xls").Sheets("officedept").O nEntry "A_Criteria_Entry" End Sub Sub A_Criteria_Entry() [department.xls].[deptanalysis].A_Criteria_Entry End Sub 'End of Auto_Open File ------------------------------------------------------------------------------------ 'In Main Procedure Set Wks = Sheets("department.xls") Wks.OnEntry = "A_Criteria_Entry" Sub A_Criteria_Entry() 'Changes colour of cells that match criteria Set Wks = Sheets("department.xls") Wks.Activate Wks.Range("AX5").NumberFormat = "@" For Each myCell In Selection If myCell.Value = "103/1" Then With myCell.Interior .ColorIndex = 43 End With ElseIf myCell.Value = "103/2" Then With myCell.Interior .ColorIndex = 4 End With ElseIf myCell.Value = "103/3" Then With myCell.Interior .ColorIndex = 35 End With ElseIf myCell.Value = "103/4" Then With myCell.Interior .ColorIndex = 17 End With ElseIf myCell.Value = "103/5" Then With myCell.Interior .ColorIndex = 24 End With ElseIf myCell.Value = "103/6" Then With myCell.Interior .ColorIndex = 38 End With End If Next myCell Cells(3, 2).Interior.ColorIndex = Cells(5, 50).Interior.ColorIndex End Sub Kind regards, QT -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Auto Cell Formatting Uppercase
QTE,
At the top of Sub A_Criteria_Entry() use this code: If Not Intersect(Selection, Range("C:C,G:G,J:J,R:R")) Is Nothing Then For Each myCell In Selection myCell.Value = UCase(myCell.Value) Next myCell End If HTH, Bernie MS Excel MVP "QTE " wrote in message ... Hi Bernie, Here is the On_Entry code: Bernie Deitrick wrote: [b]QTE, Post the On_Entry Event code that you are currently using. HTH, Bernie MS Excel MVP --- This is the Auto_Open File: Option Explicit Sub Auto_Open() Windows("office.xls").Visible = False Workbooks.Open("G:\Excel\department.xls").Sheets ("officedept") Application.Goto Workbooks("department.xls").Sheets("officedept").R ange("AX5") Entry End Sub Sub Entry() Workbooks("department.xls").Sheets("officedept").O nEntry = "A_Criteria_Entry" End Sub Sub A_Criteria_Entry() [department.xls].[deptanalysis].A_Criteria_Entry End Sub 'End of Auto_Open File -------------------------------------------------------------------------- ---------- 'In Main Procedure Set Wks = Sheets("department.xls") Wks.OnEntry = "A_Criteria_Entry" Sub A_Criteria_Entry() 'Changes colour of cells that match criteria Set Wks = Sheets("department.xls") Wks.Activate Wks.Range("AX5").NumberFormat = "@" For Each myCell In Selection If myCell.Value = "103/1" Then With myCell.Interior .ColorIndex = 43 End With ElseIf myCell.Value = "103/2" Then With myCell.Interior .ColorIndex = 4 End With ElseIf myCell.Value = "103/3" Then With myCell.Interior .ColorIndex = 35 End With ElseIf myCell.Value = "103/4" Then With myCell.Interior .ColorIndex = 17 End With ElseIf myCell.Value = "103/5" Then With myCell.Interior .ColorIndex = 24 End With ElseIf myCell.Value = "103/6" Then With myCell.Interior .ColorIndex = 38 End With End If Next myCell Cells(3, 2).Interior.ColorIndex = Cells(5, 50).Interior.ColorIndex End Sub Kind regards, QTE --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Auto Cell Formatting Uppercase
Hi Bernie,
Code is working brilliantly. Thank you very much for your time. Man Thanks. Bernie Deitrick wrote: *QTE, At the top of Sub A_Criteria_Entry() use this code: If Not Intersect(Selection, Range("C:C,G:G,J:J,R:R")) Is Nothin Then For Each myCell In Selection myCell.Value = UCase(myCell.Value) Next myCell End If HTH, Bernie MS Excel MVP * [/color] Kind regards, QT -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Cell Contains specific UPPERCASE text | Excel Worksheet Functions | |||
Formatting a cell to utilize list and auto populate | Excel Worksheet Functions | |||
Auto Uppercase | Excel Discussion (Misc queries) | |||
Auto-formatting cell contents | Excel Discussion (Misc queries) | |||
How to start every word in a cell uppercase? | Excel Discussion (Misc queries) |