Re: [belajar-excel] VB Worksheet_Change_2 [1 Attachment]

Darwis Arief [email protected] [belajar-excel] Mon, 29 Sep 2014 22:22:38 -0700

Assalamu Alaikum wr,wb.


Terima kasih ats bantuannya Mr. Nmkid,
Sy sdh tambahkan sesuai dgn code yg diberikan, namun blm bs diexecusi.
Mohon pencerahannya lg...(terlampir)


Wassalam,


--------------------------------------------
On Mon, 9/29/14, 'Mr. Kid' [email protected] [belajar-excel] 
<[email protected]> wrote:


 Subject: Re: [belajar-excel] VB Worksheet_Change
 To: "BeExcel" <[email protected]>
 Date: Monday, September 29, 2014, 5:00 AM


  









       Hai Arief,

 Tambahkan tepat sebelum
 End Sub.
 application.enableevents=false
 select case target.address
 case
 "$B$11"
     select
 case cstr(target.value)   'karena akan cek yang pasti
 berisi teks
     case
 "a","b"
           
 range("b13,d13").clearcontents
     end select
 case "$B$15"
     select case
 cstr(target.value)
     case
 "c","d"
           
 range("b17,d17").clearcontents
            if
 cstr(target.value)="d" then
                 
 range("b17").value=10
            endif
     end select
 end select
 application.enableevents=true

 Wassalam,
 Kid.



 On Sun, Sep 28, 2014 at
 6:51 PM, Darwis Arief [email protected]
 [belajar-excel] <[email protected]>
 wrote:









 






  









       Aslm,

 Yang saya banggakan teman2 di Forum ini.

 Saya punya VB hasil copas dari internet sbb:



 Private Sub Worksheet_Change(ByVal Target As Range)

 On Error Resume Next

 Dim ws As Worksheet

 Dim str As String

 Dim i As Integer

 Dim rngDV As Range

 Dim rng As Range



 If Target.Count > 1 Then Exit Sub

 Set ws = Worksheets("LIST_KPL")



 If Target.Row > 1 Then

   On Error Resume Next

   Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)

   On Error GoTo 0

   If rngDV Is Nothing Then Exit Sub



   If Intersect(Target, rngDV) Is Nothing Then Exit Sub



   str = Target.Validation.Formula1

   str = Right(str, Len(str) - 1)

   On Error Resume Next

   Set rng = ws.Range(str)

   On Error GoTo 0

   If rng Is Nothing Then Exit Sub



   If Application.WorksheetFunction _

     .CountIf(rng, Target.Value) Then

     Exit Sub


   Else

     i = ws.Cells(Rows.Count, rng.Column).End(xlUp).Row +
 1


     ws.Cells(i, rng.Column).Value = Target.Value

     rng.Sort Key1:=ws.Cells(1, rng.Column), _

       Order1:=xlAscending, Header:=xlNo, _

       OrderCustom:=1, MatchCase:=False, _

       Orientation:=xlTopToBottom

   End If



 End If



 End Sub



 Hasilnya sih sdh jalan, tapi saya ingin lagi menambahkan
 lagi Event Change di kolom yang sama, dgn ignin bertanya
 seperti di file terlampir.



 Terima kasih sebelumnya.

 Wassalam,

 d_arief








 























 #yiv1364311866 #yiv1364311866 --
   #yiv1364311866ygrp-mkp {
 border:1px solid #d8d8d8;font-family:Arial;margin:10px
 0;padding:0 10px;}

 #yiv1364311866 #yiv1364311866ygrp-mkp hr {
 border:1px solid #d8d8d8;}

 #yiv1364311866 #yiv1364311866ygrp-mkp #yiv1364311866hd {
 color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
 0;}

 #yiv1364311866 #yiv1364311866ygrp-mkp #yiv1364311866ads {
 margin-bottom:10px;}

 #yiv1364311866 #yiv1364311866ygrp-mkp .yiv1364311866ad {
 padding:0 0;}

 #yiv1364311866 #yiv1364311866ygrp-mkp .yiv1364311866ad p {
 margin:0;}

 #yiv1364311866 #yiv1364311866ygrp-mkp .yiv1364311866ad a {
 color:#0000ff;text-decoration:none;}
 #yiv1364311866 #yiv1364311866ygrp-sponsor
 #yiv1364311866ygrp-lc {
 font-family:Arial;}

 #yiv1364311866 #yiv1364311866ygrp-sponsor
 #yiv1364311866ygrp-lc #yiv1364311866hd {
 margin:10px
 0px;font-weight:700;font-size:78%;line-height:122%;}

 #yiv1364311866 #yiv1364311866ygrp-sponsor
 #yiv1364311866ygrp-lc .yiv1364311866ad {
 margin-bottom:10px;padding:0 0;}

 #yiv1364311866 #yiv1364311866actions {
 font-family:Verdana;font-size:11px;padding:10px 0;}

 #yiv1364311866 #yiv1364311866activity {
 
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}

 #yiv1364311866 #yiv1364311866activity span {
 font-weight:700;}

 #yiv1364311866 #yiv1364311866activity span:first-child {
 text-transform:uppercase;}

 #yiv1364311866 #yiv1364311866activity span a {
 color:#5085b6;text-decoration:none;}

 #yiv1364311866 #yiv1364311866activity span span {
 color:#ff7900;}
 

 #yiv1364311866 #yiv1364311866activity span
 .yiv1364311866underline {
 text-decoration:underline;}

 #yiv1364311866 .yiv1364311866attach {
 clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
 0;width:400px;}

 #yiv1364311866 .yiv1364311866attach div a {
 text-decoration:none;}

 #yiv1364311866 .yiv1364311866attach img {
 border:none;padding-right:5px;}

 #yiv1364311866 .yiv1364311866attach label {
 display:block;margin-bottom:5px;}

 #yiv1364311866 .yiv1364311866attach label a {
 text-decoration:none;}

 #yiv1364311866 blockquote {
 margin:0 0 0 4px;}

 #yiv1364311866 .yiv1364311866bold {
 font-family:Arial;font-size:13px;font-weight:700;}

 #yiv1364311866 .yiv1364311866bold a {
 text-decoration:none;}

 #yiv1364311866 dd.yiv1364311866last p a {
 font-family:Verdana;font-weight:700;}


 #yiv1364311866 dd.yiv1364311866last p span {
 margin-right:10px;font-family:Verdana;font-weight:700;}

 #yiv1364311866 dd.yiv1364311866last p
 span.yiv1364311866yshortcuts {
 margin-right:0;}

 #yiv1364311866 div.yiv1364311866attach-table div div a {
 text-decoration:none;}

 #yiv1364311866 div.yiv1364311866attach-table {
 width:400px;}

 #yiv1364311866 div.yiv1364311866file-title a, #yiv1364311866
 div.yiv1364311866file-title a:active, #yiv1364311866
 div.yiv1364311866file-title a:hover, #yiv1364311866
 div.yiv1364311866file-title a:visited {
 text-decoration:none;}

 #yiv1364311866 div.yiv1364311866photo-title a,
 #yiv1364311866 div.yiv1364311866photo-title a:active,
 #yiv1364311866 div.yiv1364311866photo-title a:hover,
 #yiv1364311866 div.yiv1364311866photo-title a:visited {
 text-decoration:none;}

 #yiv1364311866 div#yiv1364311866ygrp-mlmsg
 #yiv1364311866ygrp-msg p a span.yiv1364311866yshortcuts {
 font-family:Verdana;font-size:10px;font-weight:normal;}

 #yiv1364311866 .yiv1364311866green {
 color:#628c2a;}

 #yiv1364311866 .yiv1364311866MsoNormal {
 margin:0 0 0 0;}

 #yiv1364311866 o {
 font-size:0;}

 #yiv1364311866 #yiv1364311866photos div {
 float:left;width:72px;}

 #yiv1364311866 #yiv1364311866photos div div {
 border:1px solid
 #666666;height:62px;overflow:hidden;width:62px;}

 #yiv1364311866 #yiv1364311866photos div label {
 
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

 #yiv1364311866 #yiv1364311866reco-category {
 font-size:77%;}

 #yiv1364311866 #yiv1364311866reco-desc {
 font-size:77%;}

 #yiv1364311866 .yiv1364311866replbq {
 margin:4px;}

 #yiv1364311866 #yiv1364311866ygrp-actbar div a:first-child {
 margin-right:2px;padding-right:5px;}

 #yiv1364311866 #yiv1364311866ygrp-mlmsg {
 font-size:13px;font-family:Arial, helvetica, clean,
 sans-serif;}

 #yiv1364311866 #yiv1364311866ygrp-mlmsg table {
 font-size:inherit;font:100%;}


 #yiv1364311866 #yiv1364311866ygrp-mlmsg select,
 #yiv1364311866 input, #yiv1364311866 textarea {
 font:99% Arial, Helvetica, clean, sans-serif;}
 

 #yiv1364311866 #yiv1364311866ygrp-mlmsg pre, #yiv1364311866
 code {
 font:115% monospace;}

 #yiv1364311866 #yiv1364311866ygrp-mlmsg * {
 line-height:1.22em;}

 #yiv1364311866 #yiv1364311866ygrp-mlmsg #yiv1364311866logo {

 padding-bottom:10px;}


 #yiv1364311866 #yiv1364311866ygrp-msg p a {
 font-family:Verdana;}

 #yiv1364311866 #yiv1364311866ygrp-msg
 p#yiv1364311866attach-count span {
 color:#1E66AE;font-weight:700;}

 #yiv1364311866 #yiv1364311866ygrp-reco
 #yiv1364311866reco-head {
 color:#ff7900;font-weight:700;}

 #yiv1364311866 #yiv1364311866ygrp-reco {
 margin-bottom:20px;padding:0px;}

 #yiv1364311866 #yiv1364311866ygrp-sponsor #yiv1364311866ov
 li a {
 font-size:130%;text-decoration:none;}

 #yiv1364311866 #yiv1364311866ygrp-sponsor #yiv1364311866ov
 li {
 font-size:77%;list-style-type:square;padding:6px 0;}

 #yiv1364311866 #yiv1364311866ygrp-sponsor #yiv1364311866ov
 ul {
 margin:0;padding:0 0 0 8px;}

 #yiv1364311866 #yiv1364311866ygrp-text {
 font-family:Georgia;}

 #yiv1364311866 #yiv1364311866ygrp-text p {
 margin:0 0 1em 0;}

 #yiv1364311866 #yiv1364311866ygrp-text tt {
 font-size:120%;}

 #yiv1364311866 #yiv1364311866ygrp-vital ul li:last-child {
 border-right:none !important;
 }
 #yiv1364311866

Data Change_2.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12

Re: [belajar-excel] VB Worksheet_Change_2 [1 Attachment]

Kirim email ke