TO AUTO CORRECT THE CASE – VBA CODE
UPPER CASE
Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140603
[Link] = [Link]([Link])
End Sub
LOWER CASE
Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140603
[Link] = [Link]([Link])
End Sub
PROPER CASE
Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140603
[Link] = [Link]([Link])
End Sub
UPPER CASE MACRO – VBA CODE
(1.) Sub Uppercase()
' Loop to cycle through each cell in the specified range.
For Each x In Range("A1:M1000")
' Change the text in the range to uppercase letters.
[Link] = UCase([Link])
Next
End Sub
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
[Link] = False
'Change A1:A10 to the range you desire
'Change UCase to LCase to provide for lowercase instead of uppercase
If Not [Link](Target, Range("A1:A10")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
[Link] = True
End Sub
{{
‘To call Sub Procedure to Center and Apply
Sub main()
Call Format_Centered_And_Sized( arg1, arg2, ... )
End Sub
‘Sub Procedure to Center and Apply a Supplied Font Size to the Selected Range
Sub Format_Centered_And_Sized(Optional iFontSize As Integer = 10)
[Link] = xlCenter
[Link] = xlCenter
[Link] = iFontSize
End Sub
}}
VBA: Paste values to visible cells only.
Sub CopyFilteredCells()
'Updateby20150203
Dim rng1 As Range
Dim rng2 As Range
Dim InputRng As Range
Dim OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = [Link]
Set InputRng = [Link]("Copy Range :", xTitleId,
[Link], Type: = 8)
Set OutRng = [Link]("Paste Range:", xTitleId, Type: = 8)
For Each rng1 In InputRng
[Link]
For Each rng2 In OutRng
If [Link] > 0 Then
[Link]
Set OutRng = [Link](1).Resize([Link])
Exit For
End If
Next
Next
[Link] = False
End Sub
VBA CODE TO SORT TABLE ACCORDINGLY SELECTED COLUMN RANGE
Sub sbSortDataInExcelInDescendingOrder()
Dim strDataRange, strkeyRange As String
strDataRange = "A2:I17"
strkeyRange = "I2:I17"
With Sheets("Sheet1").Sort
.[Link]
.[Link] _
Key:=Range(strkeyRange), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal
.SetRange Range(strDataRange)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
UPPER CASE VBA CODE (Fully Working with copy & paste also)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
On Error Resume Next
[Link] = False
For Each cell In Target
cell = UCase(cell)
Next
[Link] = True
End Sub
UPPER CASE VBA CODE (Fully Working with copy & paste also)With
range option
Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''''
'Forces text to UPPER case for the range A1:B20
''''''''''''''''''''''''''''''''''''''''''''
If [Link] > 1 Or [Link] Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("A1:B20")) Is Nothing Then
[Link] = False
Target = UCase(Target)
[Link] = True
End If
On Error GoTo 0
End Sub
FILL IN THE BLANK CELLS IN SELECTED RANGE
Sub FillEmptyBlankCellWithValue()
Dim cell As Range
Dim InputValue As String
On Error Resume Next
InputValue = InputBox("Enter value that will fill empty cells in
selection", _
"Fill Empty Cells")
For Each cell In Selection
If IsEmpty(cell) Then
[Link] = InputValue
End If
Next
End Sub
REMOVE FIXED OBJECT MOVE ERROR
Option Explicit
Sub fixedobjecterror()
Dim wksht As [Link]
Dim cmt As Object
Dim wb As [Link]
Set wb = ActiveWorkbook
For Each wksht In [Link]
For Each cmt In [Link]
[Link] = xlMoveAndSize
Next cmt
Next wksht
End Sub
Option Explicit
Sub EnableInsertDeleteRowsCols()
Dim ctrl As CommandBarControl
''
'Disable "Row" Delete.
For Each ctrl In [Link](ID:=293)
[Link] = True
Next ctrl
'Disable "Column" Delete.
For Each ctrl In [Link](ID:=294)
[Link] = True
Next ctrl
''
'Disable "Row" and "Column" Insert.
For Each ctrl In [Link](ID:=3183)
[Link] = True
Next ctrl
''
'Disable "Cell" Delete.
For Each ctrl In [Link](ID:=292)
[Link] = True
Next ctrl
'Disable "Cell" Insert.
For Each ctrl In [Link](ID:=3181)
[Link] = True
Next ctrl
End Sub
To disable control put .Enable=False
