Ini rumus yang saya gunakan Mr. Kid, rumus ini saya isi di sheet 1,dan CheckBox
yang saya gunakan adalah ActiveX Control.
Private Sub Worksheet_Change(ByVal Target As Range)Dim oOLE As OLEObjectIf
Target.Count = 1 Then If Target.Address = "$K$5" Then For Each oOLE
In Sheets("Sheet1").OLEObjects If oOLE.Name Like "CheckBox1" Then
oOLE.Value = False End If Next oOLE
End IfEnd IfEnd Sub
Masalahnya adalah:setelah saya coba mengisi atau merubah isi Cell di K5 dan
posisi CheckBox tercentang, hasilnya bukan CheckBox tidak tercentang, malah
terdapat keterangan seperti di bawah ini:
Run-time error '438':Object doesn't support this property or method
yang merujuk ke rumus inioOLE.Value = False
Mohon dibantu Mr. Kid dan terima kasih sudah bersedia membantu.
Pada Kamis, 27 Juli 2017 23:53, "'Mr. Kid' [email protected]
[belajar-excel]" <[email protected]> menulis:
Hai Danny,
1. Misal, nama sheetnya adalah 'Kid', semua checkbox adalah activex control,
nama-nama checkbox yang diubah nilainya diawali chkKid (seperti chkKid01,
chkKid02, dan sebagainya), cells trigger (yang diubah adalah A1).
Buat prosedur event di sheet 'Kid' untuk event bernama change. Lalu isi dengan
script seperti ini :
dim oOLE as oleobject
if target.count=1 then 'yang berubah cuma 1 cells doang
if target.address="$A$1" then 'cuma A1 'case sensitive'
for each oole in sheets("kid").oleobjects 'disetiap oleobject
dalam sheet bernama 'Kid'
if oole.name like "chkKid*" then 'si oleobject yang
diproses memilki nama yang diawali 'chkKid' (case sensitive)
oole.value=false
endif
next oole
endif
endif
2. Misal, nama sheetnya adalah 'Kid', semua check box adalah form control,
nama-nama check box yang diubah nilainya diawali shpChk (seperti shpChk01,
shpChk02, dan sebagainya), cells trigger (yang diubah adalah A1).
dim shp as checkbox
if target.count=1 then 'yang berubah cuma 1 cells doang
if target.address="$A$1" then 'cuma A1 'case sensitive'
for each shp in sheets("kid").checkboxes
if shp.name like "shpChk*" then 'case sensitive
shp.value=xloff
endif
next shp
endif
endif
Kira-kiranya demikian.
Regards,
Kid
On Thu, Jul 27, 2017 at 3:20 PM, Danny Prasetyo [email protected]
[belajar-excel] <[email protected]> wrote:
Selamat siang, saya ingin bertanya.
Jika setiap kali saya merubah nilai dalam cell L5 (berapapun nilainya) maka
checkbox yang berada pada sheet aktif tersebut tidak tercentang.
Rumus VBAnya bagaimana yaa?
#yiv8393115930 #yiv8393115930 -- #yiv8393115930ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8393115930
#yiv8393115930ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8393115930
#yiv8393115930ygrp-mkp #yiv8393115930hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv8393115930 #yiv8393115930ygrp-mkp #yiv8393115930ads
{margin-bottom:10px;}#yiv8393115930 #yiv8393115930ygrp-mkp .yiv8393115930ad
{padding:0 0;}#yiv8393115930 #yiv8393115930ygrp-mkp .yiv8393115930ad p
{margin:0;}#yiv8393115930 #yiv8393115930ygrp-mkp .yiv8393115930ad a
{color:#0000ff;text-decoration:none;}#yiv8393115930 #yiv8393115930ygrp-sponsor
#yiv8393115930ygrp-lc {font-family:Arial;}#yiv8393115930
#yiv8393115930ygrp-sponsor #yiv8393115930ygrp-lc #yiv8393115930hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv8393115930
#yiv8393115930ygrp-sponsor #yiv8393115930ygrp-lc .yiv8393115930ad
{margin-bottom:10px;padding:0 0;}#yiv8393115930 #yiv8393115930actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv8393115930
#yiv8393115930activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv8393115930
#yiv8393115930activity span {font-weight:700;}#yiv8393115930
#yiv8393115930activity span:first-child
{text-transform:uppercase;}#yiv8393115930 #yiv8393115930activity span a
{color:#5085b6;text-decoration:none;}#yiv8393115930 #yiv8393115930activity span
span {color:#ff7900;}#yiv8393115930 #yiv8393115930activity span
.yiv8393115930underline {text-decoration:underline;}#yiv8393115930
.yiv8393115930attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv8393115930 .yiv8393115930attach div a
{text-decoration:none;}#yiv8393115930 .yiv8393115930attach img
{border:none;padding-right:5px;}#yiv8393115930 .yiv8393115930attach label
{display:block;margin-bottom:5px;}#yiv8393115930 .yiv8393115930attach label a
{text-decoration:none;}#yiv8393115930 blockquote {margin:0 0 0
4px;}#yiv8393115930 .yiv8393115930bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv8393115930
.yiv8393115930bold a {text-decoration:none;}#yiv8393115930 dd.yiv8393115930last
p a {font-family:Verdana;font-weight:700;}#yiv8393115930 dd.yiv8393115930last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv8393115930
dd.yiv8393115930last p span.yiv8393115930yshortcuts
{margin-right:0;}#yiv8393115930 div.yiv8393115930attach-table div div a
{text-decoration:none;}#yiv8393115930 div.yiv8393115930attach-table
{width:400px;}#yiv8393115930 div.yiv8393115930file-title a, #yiv8393115930
div.yiv8393115930file-title a:active, #yiv8393115930
div.yiv8393115930file-title a:hover, #yiv8393115930 div.yiv8393115930file-title
a:visited {text-decoration:none;}#yiv8393115930 div.yiv8393115930photo-title a,
#yiv8393115930 div.yiv8393115930photo-title a:active, #yiv8393115930
div.yiv8393115930photo-title a:hover, #yiv8393115930
div.yiv8393115930photo-title a:visited {text-decoration:none;}#yiv8393115930
div#yiv8393115930ygrp-mlmsg #yiv8393115930ygrp-msg p a
span.yiv8393115930yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv8393115930
.yiv8393115930green {color:#628c2a;}#yiv8393115930 .yiv8393115930MsoNormal
{margin:0 0 0 0;}#yiv8393115930 o {font-size:0;}#yiv8393115930
#yiv8393115930photos div {float:left;width:72px;}#yiv8393115930
#yiv8393115930photos div div {border:1px solid
#666666;min-height:62px;overflow:hidden;width:62px;}#yiv8393115930
#yiv8393115930photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv8393115930
#yiv8393115930reco-category {font-size:77%;}#yiv8393115930
#yiv8393115930reco-desc {font-size:77%;}#yiv8393115930 .yiv8393115930replbq
{margin:4px;}#yiv8393115930 #yiv8393115930ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv8393115930 #yiv8393115930ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv8393115930
#yiv8393115930ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv8393115930
#yiv8393115930ygrp-mlmsg select, #yiv8393115930 input, #yiv8393115930 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv8393115930
#yiv8393115930ygrp-mlmsg pre, #yiv8393115930 code {font:115%
monospace;}#yiv8393115930 #yiv8393115930ygrp-mlmsg *
{line-height:1.22em;}#yiv8393115930 #yiv8393115930ygrp-mlmsg #yiv8393115930logo
{padding-bottom:10px;}#yiv8393115930 #yiv8393115930ygrp-msg p a
{font-family:Verdana;}#yiv8393115930 #yiv8393115930ygrp-msg
p#yiv8393115930attach-count span {color:#1E66AE;font-weight:700;}#yiv8393115930
#yiv8393115930ygrp-reco #yiv8393115930reco-head
{color:#ff7900;font-weight:700;}#yiv8393115930 #yiv8393115930ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv8393115930 #yiv8393115930ygrp-sponsor
#yiv8393115930ov li a {font-size:130%;text-decoration:none;}#yiv8393115930
#yiv8393115930ygrp-sponsor #yiv8393115930ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv8393115930
#yiv8393115930ygrp-sponsor #yiv8393115930ov ul {margin:0;padding:0 0 0
8px;}#yiv8393115930 #yiv8393115930ygrp-text
{font-family:Georgia;}#yiv8393115930 #yiv8393115930ygrp-text p {margin:0 0 1em
0;}#yiv8393115930 #yiv8393115930ygrp-text tt {font-size:120%;}#yiv8393115930
#yiv8393115930ygrp-vital ul li:last-child {border-right:none
!important;}#yiv8393115930
