Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
simpatica_dorina
 | Femeie
19 ani
Arad
cauta Barbat
32 - 44 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
1. Non-VBA method with Data validation
It is indirect, meaning that if you don't insert text in UPPERCASE it can show an error and won't allow you to insert text other than uppercase.
In Data validation select Custom, then enter the following formula, AE6 being the first cell in your range:
Source:
2. VBA method requires you to save file as Macro-Enabled
Paste the following code into the Sheet code, and replace Range according to your needs:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("AE6:AE2000")) Is Nothing) Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If
End Sub
|
Source:
|
|
| pus acum 2 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 18 ani
Postari: 2241
|
|
3. VBA Method without saving the file as macro-enabled
a. open VBA by going to the Developer ribbon and clicking on Visual Basic
b. right click VBAProject(PERSONAL.XLSB) and choose Insert - Class Module
c. in the Properties window (if not showing: View - Properties Window) rename the Class Module to CAppEventHandler
d. paste the code below into the Class Module and change the target range to the one you need and the file name to the one you want the macro to apply to
Option Explicit
Private WithEvents App As Application
Private Sub Class_Initialize()
Set App = Application
End Sub
Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveWorkbook.Name = "Book1.xlsx" Then
If Not (Application.Intersect(Target, Range("A1:A2000")) Is Nothing) Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
On Error Resume Next
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If
End If
End Sub |
e. open the subfolder called Microsoft Excel Objects and double click on ThisWorkbook
f. paste the code below into the ThisWorkbook code
Option Explicit
Private OurEventHandler As CAppEventHandler
Private Sub Workbook_Open()
Set OurEventHandler = New CAppEventHandler
End Sub |
This works for any sheet in the given range.
_______________________________________
|
|
| pus acum 2 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 787
|
|
Proper Case in column A and Upper Case in column J:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Text As String
' Check if the changed range intersects with A3:A33
If Not Intersect(Target, Me.Range("A3:A33")) Is Nothing Then
Application.EnableEvents = False
On Error GoTo Cleanup
' Loop through each cell in the target range
For Each Cell In Intersect(Target, Me.Range("A3:A33"))
If Not IsEmpty(Cell.Value) Then
' Convert the cell value to proper case
Text = Application.WorksheetFunction.Proper(Cell.Value)
Cell.Value = Text
End If
Next Cell
Cleanup:
Application.EnableEvents = True
On Error GoTo 0
End If
Dim Cell2 As Range
Dim Text2 As String
If Not Intersect(Target, Me.Range("J3:J33")) Is Nothing Then
Application.EnableEvents = False
On Error GoTo Cleanup2
For Each Cell2 In Intersect(Target, Me.Range("J3:J33"))
If Not IsEmpty(Cell2.Value) Then
Text2 = UCase(Cell2.Value)
Cell2.Value = Text2
End If
Next Cell2
Cleanup2:
Application.EnableEvents = True
On Error GoTo 0
End If
End Sub
|
|
|
| pus acum 3 luni |
|