Insert Comment Lebih Cepat Menggunakan INCOME-C

Halo Sahabat Belajar Excel, sibuknya pekerjaan menggunakan aplikasi excel sebaiknya mendorong kita selalu mengasah kemampuan kita sehingga pekerjaan akan terasa lebih mudah.

Melanjutkan postingan sebelumnya perihal konversi data range dan table menjadi comment, pada kesempatan ini saya akan sharing sebuah alat bantu untuk insert coment lebih cepat. Alat ini mengkombinasikan macro yang sudah dijelaskan sebelumnya dan dengan sedikit modifikasi.

Silahkan download  Income-C

Code VBA tidak diproteksi, sehingga anda bebas untuk mengubek-ubek nya. Silahkan dimodifikasi lagi.

Silahkan berikan feedback sahabat Belajar Excel dengan meninggalkan komentar.

Belajar Excell.. Excellent.. !
Salam.

Cara Cepat Membuat Hyperlink Dalam Ms Excel

Cara yang umum dilakukan pengguna excel untuk membuat hyperlink adalah dengan klik kanan pada sel yang akan dibuat hyperlink dan dilanjutkan klik Hyperlink. Cara lainnya adalah melalui menu atau tab Insert dan pilih hyperlink.  Sayangnya, cara tersebut hanya dapat membuat satu hyperlink saja per sekali proses. Bagaimana jika kita bekerja dengan ratusan sampai ribuan data dimana setiap data harus di hyperlink dengan data lainnya? Membuat hyperlink satu persatu, meskipun sangat mudah, tentunya bukan pilihan yang tepat kecuali Pak Bos mau membayar uang lembur  lebih untuk cara kerja yang sebenarnya tidak efisien.

Misalnya kita memiliki serangkaian data 1 s.d 100.   Untuk memudahkan review, data dirangkum dalam sebuah sheet, sebut saja sheet “Review”.  Setiap data harus dibuat hyperlink ke rincian detail pada sheet lainnya, anggaplah nama sheetnya “Rincian”. Lalu bagaimana cara membuat hyperlinknya dengan cepat?

Dalam hal ini saya akan sharing sebuah alat sederhana yang sudah saya buat. Sebut saja namanya “LinKer”.   Code VBA-nya sengaja tidak diproteksi,  jadi teman bebas untuk mengubek-ubek dan mengembangkannya.

Silahkan didownload di sini : LinKer.zip

LinKer

Cara kerja alat ini sederhana saja, yaitu dengan cara mencari text yang sama antara sumber dengan target hyperlink pada kolom dan baris yang sudah ditentukan. Alat LinKer ini hanya menggunakan range excel untuk menyimpan parameter-parameter yang diperlukan untuk membuat hyperlink. 

Parameter yang diperlukan mencakup data sumber hyperlink dan target hyperlink yaitu berupa nama file, nama sheet, kolom, dan baris. Tanda digunakan untuk menandai baris tertentu apakah harus di buat hyperlink atau tidak.Kolom cek (paling kiri) digunakan untuk menentukan apakah baris parameter akan dieksekusi atau tidak.

Untuk lebih mudah memahaminya dapat dicoba dengan mengetes file contoh yang disertakan dalam link download diatas. Cukup merubah data parameter-parameter pada alat LinKer dan kemudian tekan tombol Set Hyperlink dan perhatikan hasilnya pada file yang dieksekusi. Jangan lupa untuk hapus hyperlink terlebih dahulu pada file contoh.

Demikian semoga dapat bermanfaat...:)

Mengatasi Error "Too Many Different Cell Formats"

Pesan Error "Too Many Different Cell Format" mulai sering saya jumpai beberapa tahun lalu ketika saya harus merekap data excel dari beberapa unit kerja. Ketika data dikumpulkan menjadi 1 file excel dengan cara copy paste, tiba-tiba muncul pesan error seperti ini.

Pesan error tersebut muncul jika file dibuka/diedit menggunakan komputer perusahaan yang masih menggunakan Office 2003. Jika file tersebut dibuka di komputer pribadi yang menggunakan excel 2007, pesan error tidak muncul dan pekerjaan dapat dilanjutkan. Sayangnya ketika file tersebut dibuka kembali menggunakan excel 2003, error kembali muncul dan otomatis komputer melakukan proses recovery, dan hasilnya : file rusak, semua formatnya cell-nya hilang. :-(

Hal ini cukup memusingkan kepala dan karena ketidaktahuan saya, dulu saya mengira ini adalah akibat ulah virus. Mungkin ada orang yang iseng membuat virus cara merusak file excel dan menyampaikan pesan too many different cell formats setiap kali akan menambahkan format baru. Itu fikiran jelek saya, tapi itu dulu... :-)

Dan kasihan Si Virus jadi tertuduh, padahal ia sama sekali tidak berdosa atas masalah ini :-). Ada juga teman yang mengatakan, wah ini karena terlalu kebanyakan rumus ...! Atau kebanyakan link..!

Penyebab  "To Many Different Cell Format"

Usut punya usut, setelah browsing di internet dan bertanya pada halaman bantuan excel, ternyata penyebabnya sangat jelas dan sesuai pesan error dari excel. Yaitu "Too Many Different Cell Formats", artinya "Terlalu Banyak Format Sel Yang Berbeda".  Sebelumnya kok enggak geh ya dengan arti pesan error tersebut??

Dan ternyata untuk kasus yang saya alami, masalahnya terletak pada versi Excel yang digunakan. Microsoft Excel 2003 hanya dapat menampung 4000 cell style yang berbeda/unik. Cell style atau cell format adalah kombinasi pengaturan format sell termasuk number, alignment, font, border, fill dan protection. Jika satu property saja dari format cell yang berbeda, maka dapat dikatakan bahwa cell tersebut memiliki style yang berbeda. Hal ini dapat diilustrasikan sebagai berikut:

Format Cell yang Berbeda

Gambar diatas mengilustrasikan format cell yang berbeda-beda, meskipun berisi text yang sama terdiri satu karakter huruf A, namun masing-masing cell memiliki format yang berbeda dan unik satu sama lainnya. Karena kreatifitas user, biasanya tanpa disadari semakin banyak format cell yang berbeda-beda dan pada akhirnya akan mencapai batas 4000 format cell yang berbeda.

Microsoft excel 2007, dapat menampung lebih banyak format cell yang berbeda yaitu sebanyak 64.000. Sehingga jika jika file excel 2003 yang sudah over format dibuka menggunakan excel 2007, maka pesan error tidak akan muncul. Namun celakanya jika file tersebut kembali dibuka menggunakan versi 2003, maka excelnya pun menjadi "mabuk" format.

Pantesan saja, masalah ini sering muncul, setelah munculnya excel-excel versi yang baru yang lebih canggih dan mampu menampung format yang lebih banyak. Teman-teman yang menggunakan laptop atau komputer pribadi sudah menggunakan versi excel yang lebih tinggi biar tidak ketinggalan zaman.

"Karena loyalitas yang sangat tinggi, banyak teman-teman menggunakan komputer pribadi, untuk menyelesaikan pekerjaan admin kantor. Sangat  Salut ... dan Hebat lah :-) "

Bagaimana Cara Mengatasinya.

• Jika kita bekerja dalam tim sebaiknya dibuat kesepakatan untuk menggunakan 1 versi excel saja untuk keperluan pengolahan data. Apalagi jika petugas yang mengumpulkan data final masih menggunakan excel jadul 2003, sementara data unit menggunakan excel mutakhir seperti versi 2007, 2010 maupun 2013. Sudah dapat dipastikan bahwa problem to many different cell format akan terjadi.
• Gunakan jenis font yang sama untuk semua data, dan hindari penggunaan size font yang bervariasi.
• Jangan terlalu banyak variasi dalam warna, alignment, border
• Dan lain-lain pokoknya jangan terlalu banyak variasi format dech... Memang cukup menggangu kreatifitas, tapi pilih mana? tampilan bagus dan ngejreng tapi error dan file rusak? atau tampilan sederhana tapi aman dan nyaman?
• Jika terlanjur error, buka file yang error tersebut menggunakan OpenOffice dan save sebagai excel 2000. Cara ini bisa sedikit membantu terutama jika kerusakan file belum begitu kompleks.
• Atau jika ada cukup waktu clear semua format dan ganti menjadi format yang lebih sederhana dan konsisten.
• Usulkan ke Bos Anda supaya dapat disetujui budget IT berupa instalasi program microsoft excel yang paling mutakhir di semua komputer perusahaan / instansi dimana anda bekerja... Nah kalau yang ini mungkin agak sulit...

Demikian semoga bermanfaat :-)

 

Waspadalah! : Macro Excel Dapat Merusak Data Anda

Dengan kemampuannya yang luar biasa untuk meningkatkan efisiensi dan efektifitas kerja microsoft excel, macro/vba juga membuka peluang untuk orang yang tidak bertanggung jawab untuk menyisipkan script berbahaya yang dapat merusak data excel, dan bahkan menghilangkannya sama sekali.

Hal ini harus benar-benar diwaspadai karena karena saking mudahnya, orang yang baru belajar makro pun dapat membuat script yang merusak ini. Cukup dengan record macro, maka code vba yang bersifat merusak data dapat dibuat.

Sebagai contoh yang sangat sederhana adalah sbb:

• Kebanyakan pengguna excel, termasuk saya lebih suka menggunakan tombol shortcut "CTR + S" pada saat menyimpan hasil perubahan/edit pada file excel. Shortcut "CTR + S" ini berfungsi untuk memerintahkan komputer untuk menyimpan hasil akhir atas perubahan yang kita lakukan pada file excel tersebut. Kalau kita lakukan record macro atas proses ini, maka script yang kita peroleh adalah sbb: 

Sub Macro1()
    ActiveWorkbook.Save
End Sub

• Jika kita kembali melakukan record macro untuk proses sbb:  select All (tekan "Ctr + A") ---> Delete (Tekan tombol Delete)--> Save (tekan "Ctr + S"), maka script macro/vba yang kita peroleh adalah sbb: 

Sub Macro2()
    Cells.Select
    Range("A1").Activate
    Selection.ClearContents
    ActiveWorkbook.Save
End Sub

• Jika untuk Macro2 tersebut kemudian kita buat shortcut  "Ctr + S", walhasil pada saat kita tekan "Ctr +S",  maka komputer akan menjalankan Macro2, yaitu menyeleksi semua cell dalam activesheet (select all), kemudian menghapus semua datanya (clear contents) dan dilanjutkan dengan proses save file. Data yang sudah terhapus tidak dapat dikembalikan karena file sudah di-save (proses terakhir)
• Meskipun script macronya tersimpan di file lain, asalkan file tersebut dibuka dan macro enable, maka proses macro2  juga dapat merusak file excel lainnya yang akan di-save menggunakan short cut "Ctr+S".

Jadi apakah makro berbahaya?
Bisa dikatakan iya jika kita tidak berhati-hati.

Bagaimana supaya terhindar dari bahaya macro?
Pastikan dulu code macro aman sebelum dijalankan.

Demikian semoga bermanfaat, dan lebih waspada...:-)

Mengatur Target Hyperlink Pada Posisi Yang Tepat

Masih membahas perihal hyperlink pada microsoft Excel. Ketika kita membuat hyperlink dengan target hyperlinknya adalah area data dalam file yang sama dengan sel yang di-hyperlink-kan (anchor area). Bagaimana caranya supaya target hyperlink akan terbuka pada posisi yang tepat ketika hyperlink di click (follow hyperlink).

Cara berikut mudah-mudahan bermanfaat.

1. Cara 1: Buat Target Hyperlik Pada Range Row

• Klik kanan pada sel yang akan dibuat hyperlink
• Klik Hyperlink...
• Pilih : Place In This Document
• Select : Worksheet yang akan menjadi target hyperlink
• Pada text box "type cell reference" biasanya sudah terisi "A1" secara default.
• Ganti "A1" sesuai target hyperlink yang kita inginkan, misalnya jika fokus hyperlink kita adalah sel B100, maka text box diisi dengan "A100:A200" tanpa tanda kutip.   Jika target kita adalah sel G150, maka text box diisi dengan "A150:A250" tanpa tanda kutip.  
• Hide Kolom A Pada worksheet yang menjadi target hyperlink, Hal ini berguna supaya tidak nampak selected range pada saat follow hyperlink.
• Dengan metode ini maka target hyperlik akan diposisikan tepat pada bagian atas layar ketika hyperlink di-klick (follow hyperlink)
• nb: kelemahan cara ini adalah mungkin target hyperlink akan bergeser jika melakukan  insert row pada   worksheet yang menjadi target hyperlink.

2. Cara 2: Buat Target Hyperlink Pada Nama Range

• Buat Nama Range yang akan menjadi referensi target hyperlink. Misalnya jika fokus target hyperlink-nya adalah sel B100,  maka range yang harus diberinama adalah  range  A100:200. misalnya namanya  adalah "targetHyperlink" tanpa tanda kutip  (cara membuat nama range dapat dilihat di artikel Memberi Nama Cell dan Range Pada Microsoft Excel Menggunakan Name Box)
• Klik kanan pada sel yang akan dibuat hyperlink
• Klik Hyperlink...
• Pilih : Place In This Document
• dari dropdown define name pilih nama range yg sudah dibuat sebagai target hyperlink.  Ok
• Hide Kolom A Pada worksheet yang menjadi target hypgterlink, Hal ini berguna supaya tidak nampak selected range pada saat follow hyperlink.
• Dengan metode ini maka target hyperlik akan diposisikan tepat pada bagian atas layar ketika hyperlink di-klick (follow hyperlink)
• nb: dengan cara ini target hyperlink tidak akan bergeser meskipun kita melakukan  insert row pada   worksheet yang menjadi target hyperlink.

Semoga bermanfaat...:-)

Salam

Macro untuk Menghapus Hyperlink Tanpa Merubah Format

Pada postingan sebelumnya kita sudah belajar bagaimana caranya untuk Menghapus Hyperlink Tanpa Merubah Format pada Microsoft Excel. Proses tersebut dibuat secara manual dengan cara menghapus seluruh data yang ada hyperlinks-nya (clear contents), dilanjutkan dengan mengembalikan data tersebut dari data backup dengan cara copy paste formula.

Proses tersebut  sebenarnya dapat dibuat lebih praktis dengan menggunakan macro/vba. Berikut adalah contoh script-nya.

'----------------------------------------------------

Sub clearHyperlinkWithoutFormat()

'je270215-remove hyperlinks tanpa merubah format

Dim sel As Range, tempFormula As String

Application.ScreenUpdating = False

For Each sel In Selection

    If sel.Hyperlinks.Count > 0 Then

        tempFormula = sel.Formula

        sel.ClearContents

        sel.Formula = tempFormula

    End If

Next

End Sub

'----------------------------------------------------

Cara menggunakan macro tersebut sebagai berikut:

• copy script di atas ke module standar dan buatkan shortcutnya
• Sorot/Seleksi range excel yang hyperlink-nya akan di hapus
• Jalankan macro tadi (clearHyperlinkWithoutFormat)

Semoga bermanfaat... :-)

Menghapus Hyperlinks Tanpa Merubah Format

Pada saat kita membuat hyperlinks pada microsoft excel, secara default format sel akan berubah menjadi font warna biru bergaris bawah. Jika kita menginginkan format yang lain,  maka kita dapat merubahnya kemudian. Proses sebaliknya, ketika kita menghapus hyperlink dengan cara biasa (klik kanan - remove hyperlink), maka secara default, format akan di-clear-kan menjadi font warna hitam tanpa garis bawah.

Berikut contoh ilustrasinya:

Remove Hyperlink Mengembalikan Format Ke Kondisi Default

Jika kita ingin menghapus hyperlinks tanpa merubah format maka cara berikut mungkin akan membantu.

• Copy file atau worksheet dimana hyperlinks akan dihapus.  Hal ini berguna sebagai back up.
• Hapus data yang ada hyperliknya dengan menggunakan tombol DELETE atau  dengan cara Clear Contents. Pada proses ini, hyperlink ikut terhapus bersama data.
• Kembalikan data yang sudah dihapus tadi dengan cara copy-paste special formula dari file/worksheet back up.

Berikut Contoh Ilustrasinya:

Remove Hyperlink Tanpa Merubah Format

Excel Comment - Macro Untuk Konversi Data Tabel Menjadi Text dalam satu Kolom

Konversi Data Tabel Menjadi Text dalam satu Kolom

Pada tulisan sebelumnya sudah dijelaskan bagaimana caranya mengkonversi text dalam range kolom menjadi text dalam comment box. Proses ini menggunakan prosedur makro yang saya namai convertColumnToCmt .

Dalam beberpa kasus, adakalanya kita harus menyertakan table ke dalam comment. Prosedur makro convertColumnToCmt masih mempunyai kelemahan yaitu ia hanya dapat mengkonversi text dari satu kolom saja. Sedangkan table, seperti kita ketahui terdiri atas beberapa kolom.  Sehingga, untuk dapat menyertakan table kedalam comment,  maka diperlukan prosedur untuk mengkonversi tabel terlebih dahulu menjadi text dalam satu kolom

Part1: Font Type Mono Space

Pada bagian pertama ini saya akan jelaskan cara membuat prosedur macro untuk menggabungkan data table kedalam satu kolom jika font yang digunakan adalah type mono space. Penggunaan font tipe lainnya akan dijelaskan dalam bagian kedua karena memerlukan pendekatan dan prosedur khusus.

Saya sendiri lebih suka menggunakan font type monospace karena kerapian text akan  lebih stabil meskipun ada perubahan pada size font maupun zoom windows. Contoh font mono space yang  biasa digunakan adalah: courier new dan arial monospaced for sap.

Berikut adalah contoh script untuk konversi data tabel menjadi text dalam satu kolom (dengan kata lain : menggabungkan data tabel yang terdiri beberapa kolom menjadi satu kolom saja).

Copykan script berikut pada module standar, dan buat shortcut untuk menjalankan macro-nya.


Sub convertTableToColumn()
Dim r As Range, tabel As Range, xTabel As Range
Dim x As Integer, y As Long, xMax As Long, yMax As Long
Dim textTabel As String, spMax As Integer, sp As Double
Dim spAwal As Double, textSp As String
Set tabel = Selection
Set r = Application.InputBox( _
prompt:="Pilih Satu Sel Untuk Menempatkan Text Tabel", Type:=8)
spAwal = InputBox("Masukan Jarak Spasi", "", 2)
xMax = tabel.Rows.Count
yMax = tabel.Columns.Count
Application.ScreenUpdating = False
For y = 1 To yMax
    spMax = 0
    For x = 1 To xMax
        Set xTabel = tabel.Range(Cells(x, y), Cells(x, y))
        textTabel = Trim(xTabel.Text)
        If Len(textTabel) > spMax Then spMax = Len(textTabel)
    Next x
    For x = 1 To xMax
        Set xTabel = tabel.Range(Cells(x, y), Cells(x, y))
        textTabel = Trim(xTabel.Text)
        sp = spAwal + spMax - Len(textTabel)
        textSp = WorksheetFunction.Rept(" ", sp)
        If y = 1 Then
            textTabel = textTabel & textSp
            r.Offset(x - 1, 0).ClearContents
        Else
            textTabel = textSp & textTabel
        End If
        r.Offset(x - 1, 0) = r.Offset(x - 1, 0) & textTabel
    Next x
Next y
End Sub

 Cara penggunaan prosedur makro ini

• Sorot table yang akan di konversi
• Jalankan macro ini
• Pilih cell tempat dimana table hasil konversi akan ditempatkan
• Ok
• Hasilnya: text table digabung menjadi satu kolom

• Text tabel tersebut (kolom B) selanjutnya dapat dikonversi menjadi text comment dengan prosedur seperti yang sudah saya jelaskan dalam article :  Excel Comment - Konversi Text Dari Kolom Menjadi Text Comment

Semoga bermanfaat... :-)

Memberi Nama Cell dan Range Pada Microsoft Excel Menggunakan Name Box

Nama sel atau range pada microsoft excel sangat berguna untuk berbagai keperluan diantaranya:

• Nama sel yang diidentifikasikan sendiri oleh user lebih mudah diingat dibandingkan dengan refferensi range/sel default excel. Misalnya kita akan lebih mengingat "dataku" dibanding "data!A1",  atau lebih mudah mengingat "kumpulanDataku" dibanding ""data!A1:A13"
• Lebih mudah digunakan sebagai referensi dalam pembuatan formula, terutama jika file excel terdiri atas banyak worksheet. dan formula merujuk ke referensi sumber yang tetap.
• Lebih digunakan sebagai referensi target hyperlink.
• Lebih mudah digunakan sebagai referensi dalam penulisan code VBA
• Lebih mudah digunakan sebagai list data untuk data validasi.

Untuk membuat nama sel/range dapat dilakukan dengan cara berikut:

Menggunakan Name Box

Cara termudah untuk cembuat/identifikasi nama range adalah dengan menggunakan Name Box

Name Box biasanya terlepas di kiri atas layar excel.

Ilustrasi Name Box dan Range

Untuk membuat nama range dapat dilakukan sbb:

• Sorot range yang akan dibuat namanya (contoh dalam gambar diatas adalah "A1:A13")
• Ketikan nama yang diinginkan pada Name Box
• Tekan Enter

Format Penamaan Range/Cell Yang /Benar

• Karakter yang diperbolehkan adalah alfabet, numerik dan "_"
• Tidak dapat diawali dengan karakter numerik, jadi harus diawali dengan alfabet, misal nama "1data", adalah salah dan tidak dapat diterima program excel.
• Tidak boleh ada spasi, misal  "kumpulan data" adalah nama yang salah, yang benar adalah "kumpulanData" atau "kumpulandata"
• Spasi dapat diganti dengan "_",   contoh "kumpulan_data"

Semoga bermanfaat.. :-)

Macro Untuk Memindahkan Text Dari Cell Ke Kotak Insert Comment

Menambahkan / insert comment pada microsoft excel kadang cukup membosankan terutama jika jumlahnya banyak dan textnya panjang.  Cara yang sering digunakan biasanya dengan klik kanan pada sel yang akan ditambahkan komentar dan kemudian klik insert comment untuk memunculkan kotak komentar. Selanjutnya komentar diketik secara langsung pada kotak insert comment. 

Bagaimana jika ada data berbentuk tabel yang harus disertakan dalam comment?

Metode  yang sering dilakukan pengguna ms excel umumnya dengan cara mengcopy dulu tabel dari excel ke notepad, kemudian mengeditnya supaya susunan text rapi dan berbentuk tabel.  Selanjutnya text notepad tersebut dicopy ke kotak insert comment. 

Jika anda Jenuh dengan cara biasa yang sangat menguras energi, sebaiknya anda mencoba cara yang satu ini untuk melakukan insert comment dengan cara mengambil text langsung dari sell dan memindahkannya ke dalam kotak komentar

Ya, Dengan macro dan VBA, kita dapat melakukan insert comment dengan cepat dan lebih rapi. Berikut contoh script vba macro untuk konversi text kolom menjadi text komentar (insert comment)

‘----------------------------------------------------------
Sub convertColumnToCmt()
Dim r As Range, kolom As Range, rKolom As Range, tf As TextFrame
Dim cmt As String, x As Integer, y As Long, z As Long
On Error GoTo skipError 'error jika pengguna membatalkan proses
Set kolom = Selection
Set r = Application.InputBox( _
prompt:="Pilih Satu Sel Untuk Menempatkan Komentar", Type:=8)
If r.Cells.Count > 1 Then
    MsgBox "TIDAK BERHASIL! - Silahkan Pilih Satu Sel Saja!"
    Exit Sub
End If
r.ClearComments
r.AddComment.Text Text:=" "
Set tf = r.Comment.Shape.TextFrame
For x = 1 To kolom.Rows.Count
    Set rKolom = kolom.Range(Cells(x, 1), Cells(x, 1))
    cmt = rKolom.Text & Chr(10)
    z = Len(cmt)
    With tf.Characters(y + 1, z).Font
        .Parent.Insert (cmt)
        .Bold = rKolom.Font.Bold
        .Italic = rKolom.Font.Italic
        .Underline = rKolom.Font.Underline
        .Name = rKolom.Font.Name
        .ColorIndex = rKolom.Font.ColorIndex
    End With
    y = y + z
Next x
y = 0
For x = 1 To kolom.Rows.Count
'je:2 kali looping untuk menghindari error pada Excel 2007
    Set rKolom = kolom.Range(Cells(x, 1), Cells(x, 1))
    z = Len(rKolom.Text) + 1
    tf.Characters(y + 1, z).Font.Size = rKolom.Font.Size
    y = y + z
Next x
tf.AutoSize = True
Application.Goto r
Exit Sub
skipError: 'jika pengguna membatalkan proses
End Sub
‘-------------------------------------------------------------

Cara menggunakan prosedur makro ini.

• Pastikan kode vba tersebut sudah di copy pada module standar
• Seleksi/sorot range yang mengandung text yang akan dipindahkan ke insert comment (sorot satu kolom saja, tapi bukan entirecolumn).
•  Jalankan makro ini (Anda bisa membuat shortcut terlebih dahulu untuk menjalankan makro ini supaya lebih mudah).
• Muncul input box : pilih satu sel untuk menempatkan insert comment
• klik Ok
• Hasilnya : text dalam kolom yang disorot akan berpindah ke dalam comment box. Format text menyesuaikan dengan format font dalam kolom yang disorot.

Dan ini contoh hasilnya:

Cara di atas jika digabung dengan macro lainnya dapat juga digunakan untuk memindahkan tabel kedalam kotak insert comment.  

Jika kita perhatikan kembali cara diatas dimana kumpulan text yang dapat dipindahkan ke dalam kotak insert comment harus berada dalam satu kolom.  

Oleh karena itu,  untuk memindahkan tabel ke dalam kotak insert comment , kita harus mengkonversi tabel terlebih dahulu menjadi text dalam satu kolom. 

Silahkan dibaca caranya dalam artikel: Makro untuk menggabungkan text tabel menjadi satu kolom. 

Selamat mencoba...:)

Excel Comment - Find & Replace Text dalam Comment

FIND DAN REPLACE text atau string dalam excel worksheet atau range adalah hal biasa dan mudah dilakukan dengan perintah Find (Ctrl + F)  atau Replace (Ctr + H). Namun bagaimana caranya menemukan dan menukar text dalam exel comment.  Saya  belum menemukan cara lain selain menggunakan vba dan macro.  Contoh script berikut dapat digunakan untuk mencari dan sekaligus mengganti text tertentu dalam comment box.

1.       Contoh Script untuk mencari dan menghitung text tertentu yang dijumpai dalam text comment dan memberikan informasi hasil dari proses tersebut.

 '----------------------------------------------------------------------------------------

Sub findTextInComment()

Dim findStr As String, cmtStr As String, r As Range

Dim i As Long, findCount As Long, cmtCount As Long

findStr = InputBox("Text Yang Dicari :")

If findStr = "" Then Exit Sub

For Each r In Selection

    If Not (r.Comment Is Nothing) Then

        cmtCount = cmtCount + 1

        cmtStr = r.Comment.Text

        i = InStr(1, cmtStr, findStr, vbTextCompare)

        Do While i <> 0

            findCount = findCount + 1

            i = InStr(i + 1, cmtStr, findStr, vbTextCompare)

            r.Interior.Color = vbGreen

        Loop

    End If

Next

MsgBox "Hasil Pencarian :  " & findCount

End Sub

 '----------------------------------------------------------------------------------------

2.       Contoh Scrip untuk mengganti text tertentu yang dijumpai dalam text comment, dan memberikan informasi hasil dari proses tersebut:

Nb: pada script lainnya yang saya jumpai dari berbagai sumber di internet, biasanya proses replace text comment merubah/merusak format sebelumnya. Code vba excel macro berikut sudah di-test dan bekerja tanpa merubah format comment text sebelumnya.

 '----------------------------------------------------------------------------------------

Sub replaceTextInComment()

Dim c As Comment, r As Range, i As Long, j As Long, k As Long

Dim sCmt As String, sFind As String, sReplace As String

Dim replaceCount As Long, cmtFindCount As Long, cmtCount As Long

Application.ScreenUpdating = False

sFind = InputBox("text yang akan ditukar :")

If sFind = "" Then Exit Sub

sReplace = InputBox("text pengganti :")

j = Len(sFind)

k = Len(sReplace)

For Each r In Selection

If Not (r.Comment Is Nothing) Then

    cmtCount = cmtCount + 1

    Set c = r.Comment

    i = InStr(1, c.Text, sFind, vbTextCompare)

    If i > 0 Then

        cmtFindCount = cmtFindCount + 1

        r.Interior.Color = vbBlue

    End If

    Do While i > 0

        If sReplace = "" Or i = 1 Then

            c.Shape.TextFrame.Characters(i, j).Insert (sReplace)

        Else

            sCmt = c.Text(sReplace, i + 1, j - 1)

            c.Shape.TextFrame.Characters(i, 1).Insert ("")

        End If

        replaceCount = replaceCount + 1

        i = InStr(i + k, c.Text, sFind, vbTextCompare)

    Loop

End If

Next

If replaceCount > 0 Then

Application.ScreenUpdating = True

MsgBox ("BERHASIL DENGAN KETERANGAN SBB:    " & Chr(10) _

      & "Text Awal: " & Chr(34) & sFind & Chr(34) & Chr(10) _

      & "Text Pengganti: " & Chr(34) & sReplace & Chr(34) & Chr(10) _

      & "Text Diganti: " & replaceCount & Chr(10) _

      & "Comment dicek: " & cmtCount & Chr(10) _

      & "Comment diganti: " & cmtFindCount)

End If

End Sub

 '----------------------------------------------------------------------------------------

Cara menggunakan macro: sorot range yang ada insert comment-nya dan kemudian jalankan makro di atas.

Selamat Mencoba..

Excel Comment - Cek Value/String Dalam Comment

MACRO UNTUK MENGECEK TEXT /STRING DALAM INSERT COMMENT

Adakalanya insert comment dibuat untuk menjelaskan langkah perhitungan data/angka yang ada dalam sel. Kita dapat mengecek apakah angka akhir perhitungan dalam insert comment sama atau tidak dengan angka dalam sel. 

Sebenarnya kita bisa mengeceknya satu persatu dengan cara membuka comment (show comment) satu persatu. Namun resiko human error-nya tinggi, apalagi jika dilakukan pada jam ngantuk...:). 

Saya tidak tahu nilai manfaat cara ini bagi anda. Tapi inilah yang harus saya kerjakan dimana saya harus merekap dan mengecek data-data dari divisi/unit, dimana ada penjelasan data yang dibuat dengan menambahkan insert comment pada data tersebut... dan saya termasuk orang yang "malas" untuk melakukan pengecekan satu persatu.

Code berikut akan menghitung jumlah insert comment yang tidak matching dengan nilai dalam cell. Dengan code ini, kita bisa langsung tahu dan lebih fokus untuk mengecek penjelasan/comment yang "diduga" tidak tepat.  Memang sih, ini masih banyak kekurangannya karena hanya mengecek ada tidaknya text/string/angka yang seharusnya ada atau tidak dalam comment box.

'dicoba dulu aja dulu dech...

Sub cekValInComment()

Dim cekStr As String, cmtStr As String, r As Range

Dim notMatchCounter As Long, cmtCounter As Long

For Each r In Selection

    If r.Value <> "" And Not (r.Comment Is Nothing) Then

        cmtCounter = cmtCounter + 1

        cekStr = Trim(r.Text)

        cmtStr = r.Comment.Text

        If InStr(1, cmtStr, cekStr) = 0 Then

            notMatchCounter = notMatchCounter + 1

            r.Interior.Color = vbRed

        End If

    End If

Next

If notMatchCounter > 0 Then

    MsgBox "Dijumpai " & notMatchCounter & " komentar tidak matching dari " & cmtCounter & " yang di-cek"

End If

End Sub   

Ingat, metode ini benar-benar mengecek string value dalam comment dengan forrmat yang persis sama dengan value cell. Jadi harus dipastikan bahwa sistem penulisan angka dalam insert comment harus sama dengan format cell (number).

Contoh : jika angka dalam cell adalah 20000, sedangkan penulisan dalam insert comment adalah 20.000, maka hal ini akan dianggap tidak matching (tidak ditemukan).

Macro Untuk Setting Comment Pada Microsoft Excel

Untuk melakukan perubahan / setting comment, Excel sendiri sudah menyediakan fasilitas dengan perintah “format Comment” yang dapat diakses dengan cara klik kanan pada comment box. 

Melakukan setting comment dengan cara biasa/manual

Sayangnya dengan cara tersebut, prosesnya dilakukan satu persatu dan tentu saja akan memerlukan banyak waktu. Jika hanya ada 1 atau 2 comment yang harus diedit, mungkin tidak menjadi masalah. Bagaimana jika ada puluhan, ratusan, bahkan ribuan comment yang harus di-edit?

Cara manual tentunya bukan pilihan yang tepat, kecuali jika kita bersedia untuk lembur semalaman untuk mengerjakannya satu per satu.Bagi saya ini adalah pekerjaan yang sangat membosankan, apalagi tidak ada tuh yang namanya upah lembur...

Dengan macro dan VBA, pekerjaan tersebut jauh lebih mudah dan efisien. Contoh script berikut dapat digunakan untuk melakukan setting semua comment sekaligus dalam range yang diseleksi, atau dalam aktivesheet bahkan sekaligus dalam satu workbook. Anda dapat mengembangkan  property yang diatur sesuai kebutuhan

1.       Reset semua  posisi comment box

Posisi comment box biasanya berubah karena proses hide atau unhide row dan column, atau sengaja digeser untuk keperluan tertentu. Contoh code berikut dapat digunakan untuk reset posisi semua comment box pada kondisi standar.

a.       Reset posisi semua comment box dalam activesheet

'Code berikut ini akan mengembalikan posisi semua comment box pada sheet yang aktive menjadi keadaan standar/default

Sub resetCommentPosition_ws()

    Dim c As Comment, r As Range

    For Each c In ActiveSheet.Comments

        Set r = c.Parent

        c.Shape.Top = r.Top - 7

        c.Shape.Left = r.Offset(0, 1).Left + 11

    Next

End Sub

b.      Reset posisi semua comment box dalam activeworkbook

Code berikut ini akan mengembalikan posisi semua comment box pada semua sheet di dalam file excel yang sedang aktive menjadi keadaan standar/default

Sub resetCommentPosition_wb()

    Dim c As Comment, r As Range, ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets

    For Each c In ws.Comments

        Set r = c.Parent

        c.Shape.Top = r.Top - 7

        c.Shape.Left = r.Offset(0, 1).Left + 11

    Next

    Next

End Sub

2.       Setting Comment Font

a.       Set  format font comment  dalam lingkup activesheet

Sub setAllCommentFont()

Dim c As Comment

For Each c In ActiveSheet.Comments

    With c.Shape.TextFrame.Characters.Font

        .Name = "arial"

        .Size = 12

    End With

Next

End Sub

b.      Set font  comment dalam lingkup range yang diseleksi

Sub setSelectedCommentFont()

Dim c As Comment, r As Range

For Each r In Selection

    If Not (r.Comment Is Nothing) Then

        With r.Comment.Shape.TextFrame.Characters.Font

            .Name = "arial"

            .Size = 12

        End With

    End If

Next

End Sub

                Contoh code diatas dapat dikembangkan dan dimodifikasi dengan menambahkan atau mengganti dengan property comment object yang lainnya seperti: autosize, font.bold, font.color, margins dsb. Untuk mengetahui  code-code yang diperlukan dapat menggunakan record macro dan lakukan langkah edit format seperti biasa. Untuk menambah interaktivitas dapat juga ditambahkan fungsi inputbox. Misalnya  untuk menentukan nama font dan ukurannya.

Semoga bermanfaat...:-)
Belajar Excel..! Excellent..!