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:
dany_frumusik la Simpatie.ro
 | Femeie
24 ani
Constanta
cauta Barbat
24 - 56 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
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 17 ani
Postari: 2186
|
|
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 1 an |
|