Cell function to entire column (Trim / Upper / Lower....)

The below code will apply the cell level function to the entire column.


Dim r As Range
    Set r = Intersect(ShtTemp.Columns(4), ShtTemp.UsedRange)
    r.Value = Evaluate("IF(ROW(" & r.Address & "),IF(" & r.Address & "<>"""",UPPER(" & r.Address & "),""""))")
    r.Value = Evaluate("IF(ROW(" & r.Address & "),IF(" & r.Address & "<>"""",TRIM(" & r.Address & "),""""))")

Hide Excel - When loading Userform

This code will hide the excel workbook when the form load. Copy the below code to Userform module. Write Userform.show in the Workbook open event.


Private m_lngLeft As Long
Private m_lngTop As Long
Private m_lngWindowState As Long


Private Sub Hide_excel()
    Application.Visible = Not Application.Visible
End Sub


Private Sub UserForm_Initialize()
    Application.WindowState = xlMaximized
    m_lngWindowState = Application.WindowState
End Sub


Private Sub UserForm_Terminate()
    Application.Visible = true
    Application.WindowState = m_lngWindowState
End Sub

List all worksheet names

The below code will list down all the worksheet names even thought the sheet has been hidden.


Sub SheetNames()
    For i = 1 To Sheets.Count
    Cells(i, 1) = Sheets(i).Name
    Next i
End Sub

Customize Excel Status Bar

We can customize the application.status bar, the below example shows how we can show the clock in the status bar.


Sub StatusBarExampleX()
    Application.ScreenUpdating = False
    ' turns off screen updating
    Application.DisplayStatusBar = True
    ' makes sure that the statusbar is visible
    
    For i = 1 To 10
    Application.StatusBar = Now()
    Application.Wait Now + TimeValue("00:00:01")
    Next i
    
    Application.StatusBar = False
    ' gives control of the statusbar back to the programme
End Sub

Transparent VBA Userform

We can set the transparency level (alpha) to the VBA Userform. Please find the code below. copy paste to the userform module.


Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _

    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _

    (ByVal hwnd As Long, ByVal nIndex As Long) As Long

Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" _

    (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Private Declare Function SetLayeredWindowAttributes Lib "user32" _

    (ByVal hwnd As Long, ByVal crey As Byte, ByVal bAlpha As Byte, ByVal dwFlags As Long) As Long

Private Const GWL_EXSTYLE = (-20)

Private Const WS_EX_LAYERED = &H80000

Private Const LWA_ALPHA = &H2&

Public hwnd As Long

Private Sub UserForm_Initialize()

    Dim bytOpacity As Byte

    bytOpacity = 192 ' variable keeping opacity setting

    hwnd = FindWindow("ThunderDFrame", Me.Caption)

    Call SetWindowLong(Me.hwnd, GWL_EXSTYLE, GetWindowLong(Me.hwnd, GWL_EXSTYLE) Or WS_EX_LAYERED)

    Call SetLayeredWindowAttributes(Me.hwnd, 0, bytOpacity, LWA_ALPHA)

End Sub

Removing an Excel Workbook VBA Password

A VBA project password can be removed with a hex editor. Close the workbook and open the workbook file in the hex editor. Find the string "DPB" and change it to "DPx". Save the file. Open the workbook and click OK until the workbook is open (one or more dialogs are displayed describing various problems with the VBA project). 

Press ALT+F11, choose the menu command Tools->VBAProject Properties, navigate to the Protection tab, and change the password but do not remove it (note the new password). Save, close, and re-open the workbook. Press ALT+F11 and enter the new password. Choose Tools->VBAProject Properties, navigate to the Protection tab, and remove the password. Save the workbook.

My Web Store

Blog has created to post my codings and best of my code collections