sembunyikan baris dan kolom dari banyak kriteria

  1. ‹ Older
  2. 6 tahun lalu

    Caton

    4 Sep 2018 Terverifikasi Indonesia + 20.101 Poin
    Di sunting 6 tahun lalu oleh Caton

    Pertanyaan saya, scriptnya jalan khan? Hanya saja masih salah proses, begitu khan? Kalau memang jalan, mungkin bisa dicoba dengan menambahkan perintah:

    DoEvents

    pada beberapa bagian script, contohnya:

    ...
    '+-- Hide beberapa kolom sekaligus!
    If InStr(1, "GANJIL|GENAP", UCase$([E21])) > 0 Then
       With Sheet5
          '+-- Kolom semester!
          .Columns("A:BR").Hidden = False
          .Columns("B:AI").Hidden = (UCase$([E21]) = "GENAP")
          .Columns("AJ:BQ").Hidden = (UCase$([E21]) = "GANJIL")
          DoEvents
          '+-- Kolom per kelas per semester!
          If (UCase$([E21]) = "GANJIL") Then
             '+-- Kelas < 3, Semeter Ganjil
             .Range("H:H, L:M, X:X, AB:AC").EntireColumn.Hidden = (lIdx < 3)
             .Range("P:P, AF:AF").EntireColumn.Hidden = (lIdx < 7)
             DoEvents
          Else
             '+-- Kelas < 3, Semeter Genap.
             .Range("AP:AP, AT:AU, BF:BF, BJ:BK").EntireColumn.Hidden = (lIdx < 3)
             .Range("AX:AX, BN:BN").EntireColumn.Hidden = (lIdx < 7)
             DoEvents
          End If
          .Activate
          .Range(IIf(UCase$([E21]) = "GANJIL", "C10", "AK10")).Activate
          DoEvents
       End With
    End If
    ...

    Selain itu, ada beberapa opsi yang bisa dilakukan:

    [1]. Cara untuk menampilkan informasinya dialihkan dari macro menggunakan formula. Memang kurang menyenangkan jadinya karena beberapa kolom akan terlihat kosong.

    [2]. Dibuatkan 3 sheet berbeda untuk masing-masing tingkat. Misalkan untuk kelas 1 dan 2, sheet NilaiA. Untuk kelas 3 sampai 6 dibuat sheet NilaiB dan untuk kelas 7 sampai 9 dibuat sheet NilaiC. Jika ada perubahan, kemungkinan prosesnya dapat berupa:
    — Sheet terpilih ditampilkan, yang lain disembunyikan. Atau,
    — Dibuat 1 sheet master (misalkan sheet Nilai). Saat ada proses pergantian kelas, salin dan timpa data dari sheet terkait ke sheet master.

    [3]. Susun ulang ... ;)

    Itu saran saya saat ini. Kalau mau bisa saya bantu melalui remote (misalkan pake Team Viewer) ...

  3. @Caton ....melalui remote (misalkan pake Team Viewer) ...

    mas saya lagi main PES nih, mau tanding kah ?
    ID : 615 123 482

    pass nya kalo udah diconfirm ;)

  4. Caton

    4 Sep 2018 Terverifikasi Indonesia + 20.101 Poin

    @Farud

    Atau mas @Farud coba file terlampir. Ada beberapa baris script saya coba tambahkan, mudah-mudahan ini dapat menyelesaikan masalah. Jika tidak berhasil, nanti coba dicarikan kembali solusinya.

    Demikian.

  5. baik mas saya download dulu...
    saya sekarang lagi instal team viewer biar mas caton bisa lihat langsung masalahnya

  6. team viewer ID : 1 269 221 087 password : 5219 filenya saya taruh di folder coba mas

  7. nomer hpnya kayakny lebih mas

  8. selesai gak case nya?

  9. Caton

    5 Sep 2018 Terverifikasi Indonesia + 20.101 Poin

    @manweljs_ ... selesai gak case nya ...

    Sumber masalah sudah diketahui, solusi sementara sudah ada, solusi akhir masih dicari ...

    @Farud

    Coba mas @Farud tes file terlampir, apakah bisa diterapkan. Kesulitan saya dalam menemukan solusi yang tepat untuk mengatasi masalah script yang mas @Farud alami adalah masalah tersebut tidak saya alami. Jadi, solusi yang saya berikan ini masih bersifat uji coba saja.

    Saya tidak paham mengapa masalah script tersebut bisa terjadi demikian. Aktualnya, script pada modul objek Sheet1 itu tereksekusi. Namun entah mengapa proses aktualnya yang diharapkan (menyembunyikan kolom) tidak terjadi. Apakah karena versi Excel-nya, ataukah proses multitasking-nya yang gagal ... (^_^)/ Dugaan saya sih yang terakhir ini, dengan alasan jika opsi Workbook Calculation = Manual, proses menyembunyikan kolom berhasil dikarenakan sheet Raport tidak terkalkulasi otomatis.

    Dari hasil penelusuran, sumber awal masalah adalah formula yang digunakan pada sheet RAPORT yang mengacu ke sheet SEKOLAH. Contohnya pada sel A4 sheet RAPORT yang mengacu ke sel E20 sheet SEKOLAH. Pada saat terjadi perubahan pada nilai sel E20 sheet SEKOLAH tersebut, Excel langsung mengubah nilai pada sel A4 sheet RAPORT sebelum mengeksekusi prosedur Worksheet_Change.

    Alhasil, terjadi update terhadap sel-sel yang menggunakan prosedur AcakIndeksBaris. Masalahnya, prosedur AcakIndeksBaris merupakan fungsi Volatile yang sifatnya akan mengupdate sendiri jika terjadi proses kalkulasi pada sheet terkait (dalam hal ini, proses kalkulasi dipicu oleh perubahan pada sel A4 sheet RAPORT tersebut).

    Pada saat yang sama, Excel melakukan eksekusi terhadap script dalam prosedur Worksheet_Change dari modul objek Sheet1. Ini saya ketahui saat melihat StatusBar menginformasikan masih terjadi proses kalkulasi saat Excel mengeksekusi script-script VBA tersebut. Saya menduga, dikarenakan terjadi 2 proses seklaigus tersebut akhirnya timbul masalah.

    Berdasarkan estimasi masalah tersebut, pada file terlampir saya coba:

    [1]. Mengalihkan referensi langsung dari sheet RAPORT ke sheet SEKOLAH dengan memanfaatkan nama range. Nama-nama range tersebut berisi nilai aktual, bukan alamat referensi. Nilai-nilai pada nama range tersebut akan hanya akan diupdate jika terjadi perubahan pada sheet SEKOLAH saja. Nama-nama range tersebut bersifat lokal untuk sheet RAPORT saja (nama range saya berikan prefiks RAPORT) sehingga tidak dapat diacu dari sheet lain.

    [2]. Menunda proses update kode KD pada sheet RAPORT. Pada dasarnya, yang saya coba usahakan adalah proses update pada sheet RAPORT dilakukan setelah proses eksekusi script selesai. Karena script pada sheet SEKOLAH hanya bertujuan untuk menyembunyikan atau menampilkan baris atau kolom data saja, menurut hemat saya proses eksekusinya seharusnya tidak mengganggu formula yang ada. Namun entah mengapa hal itu tidak terjadi pada PC mas @Farud (mungkin Excel-nya harus diupgrade ke versi 2010+).

    [3]. Menyesuaikan proses update kode KD pada sheet RAPORT dengan mengganti Spin Button yang ada pada sheet tersebut dari Spin Button Form Object menjadi Spin Button ActiveX Control. Tujuannya agar sheet tersebut dapat diupdate kembali sesuai perubahan nilai indeks dari Spin Button.

    Mudah-mudahan file terlampir bisa menjadi solusi. Kalaupun solusi kali ini gagal, masih ada solusi lainnya. Hanya saja, untuk solusi terakhir dibutuhkan perubahan terhadap alur prosesnya. Idenya, ada tabel data yang menyimpan hasil penentuan kode KD yang akan diacu oleh sheet RAPORT menggunakan formula biasa. Jika data sudah ada, maka ambil nilai pada tabel data tersebut, Jika belum, eksekusi fungsi untuk menentukan kode KD baru kemudian ambil datanya. Idenya sih demikian, hanya saja untuk implementasinya ... :D

    Silahkan dicoba dahulu file terlampir. Semoga berhasil ... :)

  10. tetap tidak bisa mas @Caton...
    saya tadi coba ke PC di sekolah ternyata tidak ada masalah bisa scriptnya itu bisa berjalan dengan baik tapi ketika saya coba ke laptop punya teman saya (ada2 laptop yg saya cobakan) hasilnya sama seperti kasus punya saya yg mana scriptnya tidak tereksekusi ke targetnya. di laptop ketika AcakIndeksBarisnya saya non aktifkan bisa jalan mas baik kelaptop punya saya ataupun ke 2 laptop punya teman saya.
    saya berpikir apa bedanya laptop dgn PC dengan file yg sama tapi hasil yg berbeda..mungkin mas @Caton bisa mencoba ke laptop

  11. dan file dari mas @Caton ini ketika AcakIndeksBaris di non aktifkan baru bisa mas

  12. Caton

    5 Sep 2018 Terverifikasi Indonesia + 20.101 Poin

    @farud ... saya berpikir apa bedanya laptop dgn PC dengan file yg sama tapi hasil yg berbeda ...

    Iya ya ... apa bedanya ya... (^_^)/

    @farud ... ketika AcakIndeksBaris di non aktifkan baru bisa mas ...

    Aneh. Saya coba telusur berulang, prosedur AcakIndeksBaris tetap akan dieksekusi setelah prosedur Worksheet_Change. Namun prosedur AcakIndeksBaris memang dieksekusi cukup sering, ± sekitar 800 sampai 1200 kali dieksekusi setiap terjadi pergantian kelas. Mungkin, karena Excel memanfaatkan Multithreading process, proses menyembunyikan atau menampilkan malah terganggu oleh karena banyaknya jumlah eksekusi ke prosedur AcakIndeksBaris ... :D

    @farud ... mungkin mas @Caton bisa mencoba ke laptop ...

    Mungkin nanti ...

  13. AcakIndeksBaris memang dieksekusi cukup sering, ± sekitar 800 sampai 1200 kali

    banyak juga ya mas..
    mudah-mudahan masalah ini bisa teratasi dengan bantuan mas @Caton.. mungkin klo tetap tidak bisa berarti harus saya kubur impian punya aplikasi hasil sendiri (tentunya dengan bantuan dari group ini khususnya @Caton ) tapi dengan bantuan mas @Caton yg sudah sejauh ini saya sangat berterimakasih

    Iya ya ... apa bedanya ya... (^_^)/

    mas @Caton saja bingung apalagi saya . :D :D

  14. Di sunting 6 tahun lalu oleh manweljs_

    @Caton [2]. Menunda proses update kode KD pada sheet RAPORT. Pada dasarnya, yang saya coba usahakan adalah proses update pada sheet RAPORT dilakukan setelah proses eksekusi script selesai....

    kalau pakai sheet bantuan gimana mas?
    mungkin formulanya mengacu ke sheet bantuan tersebut atau kolom yg hidden adalah berdasarkan sheet bantuan tersebut...dsb

  15. Caton

    5 Sep 2018 Terverifikasi Indonesia + 20.101 Poin

    @manweljs_ ... kalau pakai sheet bantuan gimana mas? mungkin formulanya mengacu ke sheet bantuan tersebut atau kolom yg hidden adalah berdasarkan sheet bantuan tersebut ...

    Script awalnya itu gak ada masalah. Hanya saja, meskipun tereksekusi, hasilnya tidak terwujud. Bahkan dengan cara step by step debugging, hasilnya sama. Pada saat saya coba step by step debugging, saya lihat di StatusBar masih terjadi kalkulasi seperti ini:

    Calculating.png

    Dugaan saya, disaat mengeksekusi script VBA, Excel juga sedang melakukan proses kalkulasi terhadap setiap sheet yang memiliki referensi ke sheet SEKOLAH. Mengapa saya menduga demikian? Karena pada saat saya coba menyembunyikan atau menampilkan baris atau kolom tanpa prosedur Worksheet_Change, proses berhasil ... Yang membuat saya tidak paham:

    — ini hanya per kasus saja, namun terjadi di beberapa laptop. Apakah karena versi Excel-nya? Apakah karena Multithreading Process pada Excel?
    — mengapa proses menyembunyikan kolom atau baris Worksheet seakan dilewati begitu saja meskipun baris scriptnya tereksekusi?

    Anyway ... sebelumnya saya sudah pernah menyampaikan ide yang (mungkin) ± sama dengan yang mas @manweljs_ sampaikan, yakni di bagian akhir diskusi ini. Dan pada komentar saya di atas juga sudah saya sampaikan kembali:

    ... Idenya, ada tabel data yang menyimpan hasil penentuan kode KD yang akan diacu oleh sheet RAPORT menggunakan formula biasa. Jika data sudah ada, maka ambil nilai pada tabel data tersebut, Jika belum, eksekusi fungsi untuk menentukan kode KD baru kemudian ambil datanya ...

    Hanya saja untuk implementasinya ... :D

    Mungkin mas @manweljs_ bisa mengimplementasikannya, silahkan mas ... :)

  16. mungkin ada pengaruh sama RAM komputernya...

    @Caton ...Mungkin mas @manweljs_ bisa mengimplementasikannya, silahkan mas ...

    masalahnya file nya dikomputer sy baik2 aja mas, seperti sudah sy konfirmasi sebelumnya :)

  17. Caton

    5 Sep 2018 Terverifikasi Indonesia + 20.101 Poin
    Di sunting 6 tahun lalu oleh Caton

    @manweljs_ ... mungkin ada pengaruh sama RAM komputernya ...

    Sepertinya, ini bukan masalah RAM ...

    @manweljs_ ... masalahnya file nya dikomputer sy baik2 aja ...

    Di PC saya script dan file tersebut juga baik-baik saja, tidak ada masalah dan sudah saya konfirmasikan di atas. Namun terkait pernyataan mas @manweljs_ sendiri:

    @manweljs_ ... kalau pakai sheet bantuan gimana mas? mungkin formulanya mengacu ke sheet bantuan tersebut atau kolom yg hidden adalah berdasarkan sheet bantuan tersebut ...

    mungkin saja ada contoh implementasinya ... :)

  18. Selamat malam mas @Caton.. klo dibuat gini gimana mas,cuma sekedar usul siapa tau bisa hehehe

    1. dgn menggunakan macro ketika klik kelas atau semester sekitar 1 atau 2 detik calculation option otomatis dibuat manual setelah setelah 1 atau 2 detik itu calculation option dibuat otomatis lagi atau
    2. selama berada di sheet sekolah AcakIndeksBaris dibuat non aktif dulu baru setelah pindah ke sheet lain AcakIndeksBaris baru aktif atau AcakIndeksBaris aktif ketika hanya berada di sheet raport

    itupun klo macro bisa dibuat seperti itu...terimakasih sebelumnya mas

  19. Caton

    6 Sep 2018 Terverifikasi Indonesia + 20.101 Poin
    Di sunting 6 tahun lalu oleh Caton

    @farud ... menggunakan macro ketika klik kelas atau semester sekitar 1 atau 2 detik calculation option otomatis dibuat manual ...

    Pada file yang saya lampirkan terakhir, sebenarnya sudah ada opsi tersebut. Kalau secara teori, katakanlah dibuat menunggu sambil membiarkan proses lainnya bekerja dahulu, mungkin saja bisa. Namun kelemahannya, aplikasi akan terlihat lag atau Not Responding. Beberapa opsi sementara yang bisa mas @Farud lakukan sesuai ide mas @Farud sendiri:

    [1]. Apabila sheet SEKOLAH sedang aktif, opsi kalkulasi dibuat manual. Sedangkan saat tidak aktif, ubah kembali opsi kalkulasi menjadi otomatis. Scriptnya ± sebagai berikut:

    Private Sub Worksheet_Activate()
        Application.Calculation = xlCalculationManual
    End Sub
    
    Private Sub Worksheet_Deactivate()
        Application.Calculation = xlCalculationAutomatic
        '+-- Paksa semua formula dikalkulasi.
        Application.Calculate
    End Sub

    Tambahkan kedua prosedur tersebut pada modul objek sheet SEKOLAH.

    [2]. Serupa dengan opsi pertama, hanya saja opsi kalkulasi merupakan kebalikan proses di atas, dan diletakkan pada modul sheet-sheet tertentu (misalkan hanya untuk sheet RAPORT dan sheet NILAI). Pada opsi ini, secara keseluruhan opsi kalkulasi dibuat menjadi manual. Scriptnya ± sebagai berikut:

    Private Sub Worksheet_Activate()
        Application.Calculation = xlCalculationAutomatic
    End Sub
    
    Private Sub Worksheet_Deactivate()
        Application.Calculation = xlCalculationManual
    End Sub

    Letakkan kedua prosedur tersebut pada modul-modul objek sheet yang ditargetkan, misalkan pada sheet RAPORT dan sheet NILAI.

    [3]. Menunggu sesaat sebelum proses menampilkan atau menyembunyikan kolom atau baris dilakukan. Sebenarnya, saya kurang yakin dengan opsi ini, namun bisa dicoba saja dahulu. Pertama, buat deklarasi sebagai berikut pada modul Module1 (Penting, deklarasi ini harus berada dibagian paling atas modul).

    #If VBA7 Then 
        Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr) 
    #Else
         Private Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long) 
    #End If

    kemudian tambahkan prosedur berikut pada modul yang sama:

    Public Sub Tunggu(Jeda As Long) 
         Dim lCurr As Long, lEnd As Long
    
         lEnd = GetTickCount + (Jeda * 1000)     
         Do
              lCurr = GetTickCount
              DoEvents 
         Loop Until lCurr >= lEnd 
    End Sub

    Pada modul objek Sheet1 (sheet SEKOLAH), pada prosedur Worksheet_Change, panggil prosedur di atas pada baris awal script, misalkan:

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, [Sekolah.Data.Area]) Is Nothing Then
            If Not Intersect(Target, [E20:E21]) Is Nothing Then
                Dim lIdx As Long
                Dim xAddr
        
                On Error Resume Next
                
                Call Tunggu(5) '+-- Tunggu 5 detik.
                Application.ScreenUpdating = False
                Application.Calculation = xlCalculationManual
                ...
            End If
        End If
    End Sub

    atau pada baris-baris dimana kira-kira dibutuhkan.

    @farud ... selama berada di sheet sekolah AcakIndeksBaris dibuat non aktif dulu baru setelah pindah ke sheet lain AcakIndeksBaris baru aktif atau AcakIndeksBaris aktif ketika hanya berada di sheet raport ...

    Sepertinya ini juga sudah saya buatkan. Jika mas @Farud pelajari kembali scriptnya, perintah berikut:

    ...
    Module1.OnProcess = True
    ...

    sebenarnya bertujuan agar proses keluar dari prosedur AcakIndeksBaris. Tapi sepertinya, meski telah ditandai untuk tidak diproses, Excel tetap mencoba mengeksekusi prosedur tersebut. Untuk sementara ini, mas @Farud bisa mencoba opsi-opsi di atas. Jika tidak berhasil juga, maka jalan terakhir adalah menggunakan tabel acuan yang bersifat statis sebagaimana yang saya sarankan di sini.

    Demikian.

  20. Apabila sheet SEKOLAH sedang aktif, opsi kalkulasi dibuat manual. Sedangkan saat tidak aktif, ubah kembali opsi kalkulasi menjadi otomatis. Scriptnya ± sebagai berikut:

    Private Sub Worksheet_Activate()
    Application.Calculation = xlCalculationManual
    End Sub

    Private Sub Worksheet_Deactivate()
    Application.Calculation = xlCalculationAutomatic
    '+-- Paksa semua formula dikalkulasi.
    Application.Calculate
    End Sub
    Tambahkan kedua prosedur tersebut pada modul objek sheet SEKOLAH.

    dengan menggunakan cara ini Alhamdulillah bisa mas @Caton.. dan semoga tidak ada kendala lagi.
    terimakasih banyak mas dan semoga ilmu yg mas @Caton berikan menjadi ilmu yg barokah.aamiinn

  21. Caton

    6 Sep 2018 Terverifikasi Indonesia + 20.101 Poin

    @farud ... dengan menggunakan cara ini Alhamdulillah bisa mas ...

    Alhamdulillah ... Aamiin. Semoga tidak lagi ada kendala ... ;)

    @farud ... terimakasih banyak mas dan semoga ilmu yg mas @Caton berikan menjadi ilmu yg barokah.aamiinn ...

    Sama-sama mas @Farud, semoga keberkahan juga kepada mas @Farud. Aamiin.

 

atau Mendaftar untuk ikut berdiskusi!