Pages

5/12/2019

How to Close Excel without Prompting Dialogue

ALT + F11, insert a new module.



VBA: Close without Saving


Sub Auto_Close()

    ThisWorkbook.Saved = True

End Sub

VBA: Close with saving.


Sub Auto_Close()
    If ThisWorkbook.Saved = False Then
        ThisWorkbook.Save
    End If
End Sub

5/10/2019

Force Excel Column to Proper/Lower Case

It need to be VBA function to force it happen:


  1. Open Excel
  2. Alt + F11 to open developer mode
  3. Add following code 



VBA code: Force text string to Lowercase
1
2
3
4
Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140603
Target.Value = VBA.LCase(Target.Value)
End Sub
VBA code: Force text string to proper case
1
2
3
4
Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140603
Target.Value = Application.WorksheetFunction.Proper(Target.Value)
End Sub