6 Messages

Digest #3974

menampilkan data di listbox sesuai apa yang di checkbox by praztmath

Re: menampilkan data di listbox sesuai apa yang di checkbox [1 Attac by "Mr. Kid" nmkid.family@ymail.com

Re: Bantuan Looping by "Nang Agus"

Re: Bantuan Looping by "Mr. Kid" nmkid.family@ymail.com

Re: (ask) Membuat unsur teks dalam formula menjadi huruf tebal by sondyargy

Re: Cell Style (sampah) [1 Attachment] by "Mr. Kid" nmkid.family@ymail.com

Messages

1a

menampilkan data di listbox sesuai apa yang di checkbox

Fri Sep 23, 2016 8:02 pm (PDT) . Posted by:

praztmath

Selamat siang sedulur dan para master
Mohon bantuannya bagaimana cara menampildan data di listbox sesuai pilihan yang ada pada checkbox, serta checkbox dengan cara bergantian dengan kata lain checkbox hanya bias di check salah satu...file berikut saya lampirkan dan sudah ada sedikit cekripnya yang saya copas dari beberapa web dan saya modif sedikit sedikit karena masih belum mudeng dengan macro VBA..

Sent from Mail for Windows 10

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

1b

Re: menampilkan data di listbox sesuai apa yang di checkbox [1 Attac

Sat Sep 24, 2016 1:49 am (PDT) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Coba lihat file yang ada disini
<https://app.box.com/s/a0lf5qh7awv4hl7h4us4yfbtq7st8y6j>.

Regards,
Kid

2016-09-24 9:54 GMT+07:00 praztmath@gmail.com [belajar-excel] <
belajar-excel@yahoogroups.com>:

>
>
> Selamat siang sedulur dan para master
>
> Mohon bantuannya bagaimana cara menampildan data di listbox sesuai pilihan
> yang ada pada checkbox, serta checkbox dengan cara bergantian dengan kata
> lain checkbox hanya bias di check salah satu...file berikut saya lampirkan
> dan sudah ada sedikit cekripnya yang saya copas dari beberapa web dan saya
> modif sedikit sedikit karena masih belum mudeng dengan macro VBA..
>
>
>
> Sent from Mail <https://go.microsoft.com/fwlink/?LinkId=550986> for
> Windows 10
>
>
>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

2a

Re: Bantuan Looping

Fri Sep 23, 2016 8:58 pm (PDT) . Posted by:

"Nang Agus"

Coba begini:
Dim rDates As Range
Dim x As Variant
Dim y As Variant
Dim z As Variant
*Dim o As Integer*
With Sheet3
Set rDates = .Range(.Cells(11, 7), .Cells(.Rows.Count, 7).End(xlUp))
End With
y = "Date Supplier -> No. Document"
ListBox1.AddItem y
ListBox2.AddItem y
Cells.Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A1").Select

'Dokumen Sudah Expired
For Each cl In rDates
Select Case cl.Value
Case Is = Date
x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
ListBox1.AddItem x
cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 7
End Select
Next cl

'Dokumen Akan Expired +7
*For o = 1 To 6*
For Each cl In rDates
Select Case cl.Value
Case Is = Date + o
x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
ListBox2.AddItem x
cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 6
End Select
Next cl
*Next o*
End Sub

On Fri, Sep 23, 2016 at 1:49 PM, 'Heri Pamungkas ( Gmail )'
ghery.dharma@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com>
wrote:

>
>
> Assalamualaikum.
>
> Dear Teman-Teman Be-Exceller,
>
> Saya ada kendala di panjangnya script berikut,
> nah rencana saya mau looping untuk bagian berwarna biru.
>
> Mohon bantuan idenya.
>
> Berikut Scriptnya :
>
> 'credit to Slamet <slametharto@gmail.com> <slametharto@gmail.com> @
> belajar-excel@yahoogroups.com
> 'Adjustment Heri Pamungkas
>
> Private Sub UserForm_Initialize()
> Dim rDates As Range
> Dim x As Variant
> Dim y As Variant
> Dim z As Variant
>
> With Sheet3
> Set rDates = .Range(.Cells(11, 7), .Cells(.Rows.Count, 7).End(xlUp))
> End With
> y = "Date Supplier -> No. Document"
> ListBox1.AddItem y
> ListBox2.AddItem y
> Cells.Select
> With Selection.Interior
> .Pattern = xlNone
> .TintAndShade = 0
> .PatternTintAndShade = 0
> End With
> Range("A1").Select
>
> 'Dokumen Sudah Expired
> For Each cl In rDates
> Select Case cl.Value
> Case Is = Date
> x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
> Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
> ListBox1.AddItem x
> cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 7
> End Select
> Next cl
>
> 'Dokumen Akan Expired +7
> For Each cl In rDates
> Select Case cl.Value
> *Case Is = Date + 1*
> x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
> Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
> ListBox2.AddItem x
> cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 6
> End Select
> Next cl
> For Each cl In rDates
> Select Case cl.Value
> *Case Is = Date + 2*
> x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
> Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
> ListBox2.AddItem x
> cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 6
> End Select
> Next cl
> For Each cl In rDates
> Select Case cl.Value
> *Case Is = Date + 3*
> x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
> Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
> ListBox2.AddItem x
> cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 6
> End Select
> Next cl
> For Each cl In rDates
> Select Case cl.Value
> *Case Is = Date + 4*
> x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
> Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
> ListBox2.AddItem x
> cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 6
> End Select
> Next cl
> For Each cl In rDates
> Select Case cl.Value
> *Case Is = Date + 5*
> x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
> Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
> ListBox2.AddItem x
> cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 6
> End Select
> Next cl
> For Each cl In rDates
> Select Case cl.Value
> *Case Is = Date + 6*
> x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
> Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
> ListBox2.AddItem x
> cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 6
> End Select
> Next cl
> End Sub
>
>
>
> وَالسَّلَامُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ
>
> Warm Regards,
>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (3) . Top ^

2b

Re: Bantuan Looping

Sat Sep 24, 2016 12:41 am (PDT) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

hmmm...

kalo ndak salah, kondisinya hanya ada 2, yaitu :
A. tanggal data = tanggal hari
ini
-> expired (kalau tanggal data < tanggal hari ini bagaimana ?)
B. tanggal data > tanggal hari ini DAN tanggal data < tanggal hari ini +
7 -> akan expired (kalau lebih dari tanggal hari ini + 6 gimana ?)

perlakuan berdasar kondisi ada 2, yaitu :
1. isi listbox
kalau kondisi A : isi listbox1
kalau kondisi B : isi listbox2
2. set warna cells
kalau kondisi A : warna index 6
kalau kondisi B : warna index 7

Andai sudah ditulis lengkap seperti di atas, tentu akan lebih mudah
menyusun alur prosesnya dari awal, contoh proses :
1. isi listbox dengan header (header di letakkan di item ke-1 listbox [item
ke-1 listbx ber-index-0])
> mungkin pembuatnya ingin membuat 1 variabel penyimpan teks header
supaya kelak kalau mengganti hanya mengganti 1 variabel saja
dim y as *string* 'tidak perlu Variant, supaya hemat dan cepat
(menurut konsep datatype)
y="Date Supplier -> No. Document"
ListBox1.AddItem y
ListBox2.AddItem y
'kalau tidak ingin pakai dim, baris additem bunyinya :
'ListBox1.AddItem "Date Supplier -> No. Document"
'ListBox2.AddItem "Date Supplier -> No. Document"

2. menentukan area range tanggal data
dim rDates As Range
with sheet3
Set rDates = .Range(.Cells(11, 7), .Cells(.Rows.Count,
7).End(xlUp))
End With

3. set warna cells di area range tanggal data, mulai dari kolom ke-4
sebelum kolom tanggal data sampai kolom tanggal data (5 kolom)
rdates.offset(0,-4).resize(,5).interior.colorindex=0

4. menyimpan nilai tanggal hari ini ke dalam sebuah variabel, karena akan
dibandingkan berulang-ulang
dim dtHariIni as date
dthariini=date

5. memeriksa setiap tanggal data (dalam range tanggal data) dan
memperlakukan setiap cells baris tersebut mulai 4 kolom sebelum kolom
tanggal data sebanyak 5 kolom
dim cl as range
for each cl in rdates
with cl 'karena cl akan dipakai secara aktif
if .value>dthariini then 'kondisi B bagian >
if .value<dthariini+7 then 'kondisi B bagian <
'kondisi B terpenuhi secara utuh disini
ListBox2.AddItem Format(.Offset(0, 0).Value,
"dd-mmm-yy") & " " & Format(.Offset(0, -3).Value, "") & " -> " &
.Offset(0, -1) 'perlakuan 1 : isi listbox2
.Offset(0, -4).Resize(, 5).Interior.ColorIndex =
7 'perlakuan 2 : set warna cells dengan 7
endif
elseif .value=dthariini then 'kondisi A
'kondisi A terpenuhi secara utuh disini
ListBox1.AddItem Format(.Offset(0, 0).Value,
"dd-mmm-yy") & " " & Format(.Offset(0, -3).Value, "") & " -> " &
.Offset(0, -1) 'perlakuan 1 : isi listbox1
.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 6
'perlakuan 2 : set warna cells dengan 6
endif
end with
next cl

Kalau semuanya dikumpulkan jadi satu :
'deklarasi variabel
dim y as *string* 'tidak perlu Variant, supaya hemat dan cepat
(menurut konsep datatype)
dim rDates As Range
dim dtHariIni as date
dim cl as range

'init area kerja
'1 : header listboxes
y="Date Supplier -> No. Document"
ListBox1.AddItem y
ListBox2.AddItem y

'2. range tanggal data
with sheet3
Set rDates = .Range(.Cells(11, 7), .Cells(.Rows.Count,
7).End(xlUp))
End With

'3. warna cells di 5 kolom berurutan sampai kolom tanggal data
rdates.offset(0,-4).resize(,5).interior.colorindex=0

'4. nilai tanggal hari ini
dthariini=date

'proses utama
for each cl in rdates
with cl 'karena cl akan dipakai secara aktif
if .value>dthariini then 'kondisi B bagian >
if .value<dthariini+7 then 'kondisi B bagian <
'kondisi B terpenuhi secara utuh disini
ListBox2.AddItem Format(.Offset(0, 0).Value,
"dd-mmm-yy") & " " & Format(.Offset(0, -3).Value, "") & " -> " &
.Offset(0, -1) 'perlakuan 1 : isi listbox2
.Offset(0, -4).Resize(, 5).Interior.ColorIndex =
7 'perlakuan 2 : set warna cells dengan 7
endif
elseif .value=dthariini then 'kondisi A
'kondisi A terpenuhi secara utuh disini
ListBox1.AddItem Format(.Offset(0, 0).Value,
"dd-mmm-yy") & " " & Format(.Offset(0, -3).Value, "") & " -> " &
.Offset(0, -1) 'perlakuan 1 : isi listbox1
.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 6
'perlakuan 2 : set warna cells dengan 6
endif
end with
next cl

Regards,
Kid

On Sat, Sep 24, 2016 at 10:58 AM, Nang Agus nangagus@gmail.com
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:

>
>
> Coba begini:
> Dim rDates As Range
> Dim x As Variant
> Dim y As Variant
> Dim z As Variant
> *Dim o As Integer*
> With Sheet3
> Set rDates = .Range(.Cells(11, 7), .Cells(.Rows.Count, 7).End(xlUp))
> End With
> y = "Date Supplier -> No. Document"
> ListBox1.AddItem y
> ListBox2.AddItem y
> Cells.Select
> With Selection.Interior
> .Pattern = xlNone
> .TintAndShade = 0
> .PatternTintAndShade = 0
> End With
> Range("A1").Select
>
> 'Dokumen Sudah Expired
> For Each cl In rDates
> Select Case cl.Value
> Case Is = Date
> x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
> Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
> ListBox1.AddItem x
> cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 7
> End Select
> Next cl
>
> 'Dokumen Akan Expired +7
> *For o = 1 To 6*
> For Each cl In rDates
> Select Case cl.Value
> Case Is = Date + o
> x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
> Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
> ListBox2.AddItem x
> cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 6
> End Select
> Next cl
> *Next o*
> End Sub
>
>
> On Fri, Sep 23, 2016 at 1:49 PM, 'Heri Pamungkas ( Gmail )'
> ghery.dharma@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com>
> wrote:
>
>>
>>
>> Assalamualaikum.
>>
>> Dear Teman-Teman Be-Exceller,
>>
>> Saya ada kendala di panjangnya script berikut,
>> nah rencana saya mau looping untuk bagian berwarna biru.
>>
>> Mohon bantuan idenya.
>>
>> Berikut Scriptnya :
>>
>> 'credit to Slamet <slametharto@gmail.com> <slametharto@gmail.com> @
>> belajar-excel@yahoogroups.com
>> 'Adjustment Heri Pamungkas
>>
>> Private Sub UserForm_Initialize()
>> Dim rDates As Range
>> Dim x As Variant
>> Dim y As Variant
>> Dim z As Variant
>>
>> With Sheet3
>> Set rDates = .Range(.Cells(11, 7), .Cells(.Rows.Count, 7).End(xlUp))
>> End With
>> y = "Date Supplier -> No. Document"
>> ListBox1.AddItem y
>> ListBox2.AddItem y
>> Cells.Select
>> With Selection.Interior
>> .Pattern = xlNone
>> .TintAndShade = 0
>> .PatternTintAndShade = 0
>> End With
>> Range("A1").Select
>>
>> 'Dokumen Sudah Expired
>> For Each cl In rDates
>> Select Case cl.Value
>> Case Is = Date
>> x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
>> Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
>> ListBox1.AddItem x
>> cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 7
>> End Select
>> Next cl
>>
>> 'Dokumen Akan Expired +7
>> For Each cl In rDates
>> Select Case cl.Value
>> *Case Is = Date + 1*
>> x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
>> Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
>> ListBox2.AddItem x
>> cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 6
>> End Select
>> Next cl
>> For Each cl In rDates
>> Select Case cl.Value
>> *Case Is = Date + 2*
>> x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
>> Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
>> ListBox2.AddItem x
>> cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 6
>> End Select
>> Next cl
>> For Each cl In rDates
>> Select Case cl.Value
>> *Case Is = Date + 3*
>> x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
>> Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
>> ListBox2.AddItem x
>> cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 6
>> End Select
>> Next cl
>> For Each cl In rDates
>> Select Case cl.Value
>> *Case Is = Date + 4*
>> x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
>> Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
>> ListBox2.AddItem x
>> cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 6
>> End Select
>> Next cl
>> For Each cl In rDates
>> Select Case cl.Value
>> *Case Is = Date + 5*
>> x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
>> Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
>> ListBox2.AddItem x
>> cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 6
>> End Select
>> Next cl
>> For Each cl In rDates
>> Select Case cl.Value
>> *Case Is = Date + 6*
>> x = Format(cl.Offset(0, 0).Value, "dd-mmm-yy") & " " &
>> Format(cl.Offset(0, -3).Value, "") & " -> " & cl.Offset(0, -1)
>> ListBox2.AddItem x
>> cl.Offset(0, -4).Resize(, 5).Interior.ColorIndex = 6
>> End Select
>> Next cl
>> End Sub
>>
>>
>> وَالسَّلَامُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ
>>
>> Warm Regards,
>>
>>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (3) . Top ^

3a

Re: (ask) Membuat unsur teks dalam formula menjadi huruf tebal

Fri Sep 23, 2016 9:06 pm (PDT) . Posted by:

sondyargy

baik,

terima kasih atas penjelasannya Mr. Kid.

Reply to sender . Reply to group . Reply via Web Post . All Messages (3) . Top ^

4a

Re: Cell Style (sampah) [1 Attachment]

Sat Sep 24, 2016 1:09 am (PDT) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

wa'alaikumussalam wr wb

Baris kode untuk membuang custom styles

dim st as style
on error resume next
for each st in thisworkbook.styles
if not st.builtin then
st.delete
endif
next st
err.clear
on error goto 0

Wassalamu'alaikum wr wb
Kid

2016-09-22 8:37 GMT+07:00 idz.iman@yahoo.com [belajar-excel] <
belajar-excel@yahoogroups.com>:

>
>
> Assalammualaikum wr.wb.
>
> Semangat PAGI!!
>
> Halo para master, saya mau nanya nih siapa tahu ada yang bisa bantu.
>
> saya ada file excel given dari orang2 sebelum saya (hehe), nah disini
> banyak sekali "sampahnya".
> salah satunya Cell Style nih seperti gambar terlampir bagaimana cara
> "me-Normalkan-nya" ya?
>
> salam
> Iman DZ
>
>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com
posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com
berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------

Visit Your Group

New Members 2

• Privacy • Unsubscribe • Terms of Use

EXCEL FEED

Sabtu, 24 September 2016

[belajar-excel] Digest Number 3974

6 Messages

Messages

1a

menampilkan data di listbox sesuai apa yang di checkbox

Fri Sep 23, 2016 8:02 pm (PDT) . Posted by:

praztmath

1b

Re: menampilkan data di listbox sesuai apa yang di checkbox [1 Attac

Sat Sep 24, 2016 1:49 am (PDT) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

2a

Re: Bantuan Looping

Fri Sep 23, 2016 8:58 pm (PDT) . Posted by:

"Nang Agus"

2b

Re: Bantuan Looping

Sat Sep 24, 2016 12:41 am (PDT) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

3a

Re: (ask) Membuat unsur teks dalam formula menjadi huruf tebal

Fri Sep 23, 2016 9:06 pm (PDT) . Posted by:

sondyargy

4a

Re: Cell Style (sampah) [1 Attachment]

Sat Sep 24, 2016 1:09 am (PDT) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Tidak ada komentar:

Posting Komentar

Followers

Blog Archive

About Me