Re: [belajar-excel] VB Worksheet_Change_2

Tue, 30 Sep 2014 12:44:44 -0700 

Assalamu Alaikum Wr. Wb.
Terima kasih ats bantuannya Mr. Nmkid, Semuanya sudah berjalan dgn sempurna.
Sukses selalu....
Wassalamu Alaikum Wr.Wb.
d_arief
--------------------------------------------
On Mon, 9/29/14, 'Mr. Kid' [email protected] [belajar-excel] 
<[email protected]> wrote:


 Subject: Re: [belajar-excel] VB Worksheet_Change_2
 To: "BeExcel" <[email protected]>
 Date: Monday, September 29, 2014, 11:39 PM


  









       oh gak bisa ya...
 kalo gitu coba dipindah ke atas, yaitu tepat
 setelah baris-baris dim terakhir, sebelum masuk ke script
 yang sudah ada.


 On Tue, Sep 30, 2014 at
 12:24 PM, Darwis Arief [email protected]
 [belajar-excel] <[email protected]>
 wrote:















  






  


       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































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

 #yiv5841519828 #yiv5841519828ygrp-mkp hr {
 border:1px solid #d8d8d8;}

 #yiv5841519828 #yiv5841519828ygrp-mkp #yiv5841519828hd {
 color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
 0;}

 #yiv5841519828 #yiv5841519828ygrp-mkp #yiv5841519828ads {
 margin-bottom:10px;}

 #yiv5841519828 #yiv5841519828ygrp-mkp .yiv5841519828ad {
 padding:0 0;}

 #yiv5841519828 #yiv5841519828ygrp-mkp .yiv5841519828ad p {
 margin:0;}

 #yiv5841519828 #yiv5841519828ygrp-mkp .yiv5841519828ad a {
 color:#0000ff;text-decoration:none;}
 #yiv5841519828 #yiv5841519828ygrp-sponsor
 #yiv5841519828ygrp-lc {
 font-family:Arial;}

 #yiv5841519828 #yiv5841519828ygrp-sponsor
 #yiv5841519828ygrp-lc #yiv5841519828hd {
 margin:10px
 0px;font-weight:700;font-size:78%;line-height:122%;}

 #yiv5841519828 #yiv5841519828ygrp-sponsor
 #yiv5841519828ygrp-lc .yiv5841519828ad {
 margin-bottom:10px;padding:0 0;}

 #yiv5841519828 #yiv5841519828actions {
 font-family:Verdana;font-size:11px;padding:10px 0;}

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

 #yiv5841519828 #yiv5841519828activity span {
 font-weight:700;}

 #yiv5841519828 #yiv5841519828activity span:first-child {
 text-transform:uppercase;}

 #yiv5841519828 #yiv5841519828activity span a {
 color:#5085b6;text-decoration:none;}


 #yiv5841519828 #yiv5841519828activity span span {
 color:#ff7900;}

 #yiv5841519828 #yiv5841519828activity span
 .yiv5841519828underline {
 text-decoration:underline;}

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

 #yiv5841519828 .yiv5841519828attach div a {
 text-decoration:none;}
 

 #yiv5841519828 .yiv5841519828attach img {
 border:none;padding-right:5px;}

 #yiv5841519828 .yiv5841519828attach label {
 display:block;margin-bottom:5px;}

 #yiv5841519828 .yiv5841519828attach label a {
 text-decoration:none;}

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

 #yiv5841519828 .yiv5841519828bold {
 font-family:Arial;font-size:13px;font-weight:700;}
 

 #yiv5841519828 .yiv5841519828bold a {
 text-decoration:none;}

 #yiv5841519828 dd.yiv5841519828last p a {
 font-family:Verdana;font-weight:700;}

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

 #yiv5841519828 dd.yiv5841519828last p
 span.yiv5841519828yshortcuts {
 margin-right:0;}

 #yiv5841519828 div.yiv5841519828attach-table div div a {
 text-decoration:none;}

 #yiv5841519828 div.yiv5841519828attach-table {
 width:400px;}

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

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

 #yiv5841519828 div#yiv5841519828ygrp-mlmsg
 #yiv5841519828ygrp-msg p a span.yiv5841519828yshortcuts {
 font-family:Verdana;font-size:10px;font-weight:normal;}

 #yiv5841519828 .yiv5841519828green {
 color:#628c2a;}

 #yiv5841519828 .yiv5841519828MsoNormal {
 margin:0 0 0 0;}

 #yiv5841519828 o {
 font-size:0;}

 #yiv5841519828 #yiv5841519828photos div {
 float:left;width:72px;}

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

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

 #yiv5841519828 #yiv5841519828reco-category {
 font-size:77%;}

 #yiv5841519828 #yiv5841519828reco-desc {
 font-size:77%;}

 #yiv5841519828 .yiv5841519828replbq {
 margin:4px;}

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

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

 #yiv5841519828 #yiv5841519828ygrp-mlmsg table {
 font-size:inherit;font:100%;}

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

 #yiv5841519828 #yiv5841519828ygrp-mlmsg pre, #yiv5841519828
 code {
 font:115% monospace;}

 #yiv5841519828 #yiv5841519828ygrp-mlmsg * {
 line-height:1.22em;}

 #yiv5841519828 #yiv5841519828ygrp-mlmsg #yiv5841519828logo {
 padding-bottom:10px;}


 #yiv5841519828 #yiv5841519828ygrp-msg p a {
 font-family:Verdana;}

 #yiv5841519828 #yiv5841519828ygrp-msg
 p#yiv5841519828attach-count span {
 color:#1E66AE;font-weight:700;}

 #yiv5841519828 #yiv5841519828ygrp-reco
 #yiv5841519828reco-head {
 color:#ff7900;font-weight:700;}

 #yiv5841519828 #yiv5841519828ygrp-reco {
 margin-bottom:20px;padding:0px;}

 #yiv5841519828 #yiv5841519828ygrp-sponsor #yiv5841519828ov
 li a {
 font-size:130%;text-decoration:none;}

 #yiv5841519828 #yiv5841519828ygrp-sponsor #yiv5841519828ov
 li {
 font-size:77%;list-style-type:square;padding:6px 0;}

 #yiv5841519828 #yiv5841519828ygrp-sponsor #yiv5841519828ov

 ul {
 margin:0;padding:0 0 0 8px;}

 #yiv5841519828 #yiv5841519828ygrp-text {
 font-family:Georgia;}

 #yiv5841519828 #yiv5841519828ygrp-text p {
 margin:0 0 1em 0;}

 #yiv5841519828 #yiv5841519828ygrp-text tt {
 font-size:120%;}

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

Kirim email ke