Catatan VB untuk Excel

Catatan Pribadi VB untuk Excel

Cek Mutasi PC

Ringkasan Fitur:

1. Kolom S7:S38:

   • Jika sel kosong → biarkan apa adanya (tidak diubah)

   • Jika nilainya "FERRY AGUS SETIAWAN" atau "DESTI IKA SASMITA" → ubah ke YES

   • Selain itu → ubah ke NO

2. Kolom O7:O38:

   • Format ke Comma Style (#,##0)

3. Range A7:T38:

   • Tambahkan All Borders

4. Berlaku untuk seluruh worksheet dalam workbook

Sub FormatSeluruhWorkbook()

    Dim ws As Worksheet
    Dim i As Long
    Dim valS As String

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For Each ws In ThisWorkbook.Worksheets

        With ws
            ' 1. Proses kolom S7:S38
            For i = 7 To 38
                valS = Trim(.Cells(i, "S").Value)
                If valS <> "" Then
                    Select Case valS
                        Case "FERRY AGUS SETIAWAN", "DESTI IKA SASMITA"
                            .Cells(i, "S").Value = "YES"
                        Case Else
                            .Cells(i, "S").Value = "NO"
                    End Select
                End If
            Next i

            ' 2. Format kolom O7:O45 ke Comma Style
            .Range("O7:O45").NumberFormat = "#,##0"

            ' 3. Tambahkan All Borders untuk A7:T38
            With .Range("A7:T38").Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
                .ColorIndex = xlAutomatic
            End With
        End With

    Next ws

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    MsgBox "Selesai memformat seluruh worksheet!", vbInformation

End Sub

Membuat CheckBox

Sub BuatCheckBoxes()
    Dim ws As Worksheet
    Dim cb As CheckBox
    Dim cell As Range
    Dim cbWidth As Double, cbHeight As Double
    Dim leftPos As Double, topPos As Double
    
    Set ws = ActiveSheet
    
    ' Hapus dulu semua checkbox di worksheet ini (optional)
    For Each cb In ws.CheckBoxes
        cb.Delete
    Next cb
    
    ' Tentukan ukuran checkbox (kotak kecil, misal 14x14)
    cbWidth = 14
    cbHeight = 14
    
    ' Loop di range C5:N5 dan C6:N6
    For Each cell In ws.Range("C5:N5,C6:N6")
        ' Hitung posisi agar checkbox di tengah sel
        leftPos = cell.Left + (cell.Width - cbWidth) / 2
        topPos = cell.Top + (cell.Height - cbHeight) / 2
        
        ' Tambah checkbox form control
        Set cb = ws.CheckBoxes.Add(leftPos, topPos, cbWidth, cbHeight)
        With cb
            .Caption = ""  ' Hilangkan caption agar hanya checkbox saja
            .Name = "CheckBox_" & cell.Address(False, False)
            .LinkedCell = cell.Address ' Link ke sel, jadi hasil TRUE/FALSE di sel tersebut
            .Placement = xlMoveAndSize
        End With
    Next cell
    
    MsgBox "Berhasil membuat checkbox", vbInformation, "Sukses"
End Sub

Facebook

WhatsApp

Twitter

Pinterest

Posted by : Fer

Add Comment

Senin, 07 Juli 2025