Macronya ditulis di Module pada VBE Sheet.
Salam,HK
From: "'M.Ade Alfarid' [email protected] [belajar-excel]"
<[email protected]>
To: [email protected]
Sent: Friday, 9 January 2015, 9:50
Subject: Re: [belajar-excel] Macro pilih allsheet kecuali satu sheet
Walaikumsalam,macronya di sisipkan di sheet HIT tsb atau di macro class
modulenya mas?
Ade
On Fri, 09 Jan 2015 10:06:16 +0700, Nang Agus [email protected]
[belajar-excel] <[email protected]> wrote:
Dear Mas Ade
Asalamualaikum warahmatullah
Boleh ikut nimbrung ya..
Cara membuat sheet Veryhidden ada beberapa cara:
1. sheets("HIT").visible = 2
2. sheets("HIT").visible = xlSheetVeryHidden
3. sheet1.visible = xlSheetVeryHidden 'dengan asumsi sheet "HIT" terletak
pada sheet1
atau menggunakan:
for each w in worksheets
if w.Name = "HIT" then w.Visible = xlSheetVeryHidden
next w
wasalamualaikumwarahmartullah
NangAgus
On Fri, Jan 9, 2015 at 8:55 AM, 'M.Ade Alfarid' [email protected] [belajar-excel]
<[email protected]> wrote:
Betul Pak HK,
saya kutip dari master CTV almarhum, sheet manager macro.tetapi semua sheet
jadi muncul,
Berikut Macro nya,
Worksheet MainSheet :Option Explicit
Private Sub CommandButton1_Click()
fm_SheetMgr.Show
End Sub
Private Sub Worksheet_Activate()
Dim n As Integer
If Me.ChkHideAll Then
For n = 2 To Sheets.Count
Sheets(n).Visible = False
Next n
End If
End Sub
Form Sheet Manager :
Option Explicit
Dim AllSht(), StrAllSht As String
Dim MainSht As Worksheet
Dim ShtArr()
Dim vis As Integer
Private Sub UserForm_Initialize()
Dim i As Integer
Set MainSht = Sheets("HeadSheet")
MainSht.OLEObjects("chkHideAll").Object.Value = False
StrAllSht = MainSht.Name & "|"
MainSht.Move Before:=Sheets(1)
With ListBox1
.MultiSelect = fmMultiSelectExtended
.ListStyle = fmListStyleOption
.Clear
Me.Caption = "Made_Sheets Manager"
For i = 2 To Worksheets.Count
.AddItem Sheets(i).Name
ReDim Preserve AllSht(1 To i - 1)
AllSht(i - 1) = Sheets(i).Name
StrAllSht = StrAllSht & Sheets(i).Name & "|"
If Sheets(i).Visible = -1 Then
.Selected(i - 2) = True
vis = vis + 1
Else
.Selected(i - 2) = False
End If
Next i
.ListIndex = 0
End With
lbSi = vis & "/ of " & (Sheets.Count - 1)
If vis > 0 Then
CmdUnSele.Enabled = True
Else
CmdUnSele.Enabled = False
End If
' MainSht.ChkHideAll = False
End Sub
Private Sub CmdSelAll_Click()
'-- Select All names --
Dim n As Integer
Application.EnableEvents = False
For n = 0 To ListBox1.ListCount - 1
ListBox1.Selected(n) = True
Next n
Application.EnableEvents = True
Sheet_Show
End Sub
Sub CmdUnSele_Click()
'-- UnSelect all Name ---
Dim n As Integer
ListBox1.ListIndex = -1
MainSht.Select
Application.EnableEvents = False
For n = 0 To ListBox1.ListCount - 1
ListBox1.Selected(n) = False
Next n
Application.EnableEvents = True
Sheet_Show
Dim ShtArr()
End Sub
Private Sub ListBox1_MouseUp(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
' memilih sheet secara individual
Sheet_Show
End Sub
Private Sub ListBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
' memilih sheet secara individual
Sheet_Show
End Sub
Private Sub CmdCancel_Click()
' Yu Ser form di-tup-tup !!
Unload Me
End Sub
Private Sub Sheet_Show()
' siti Vi / 22 Febru 2011
' milis [belajar-excel]
'------------------------
Dim n As Integer, vis As Integer
vis = 0
Application.ScreenUpdating = False
For n = 2 To Sheets.Count
Sheets(n).Visible = False
Next n
With ListBox1
For n = 0 To .ListCount - 1
If .Selected(n) = True Then
vis = vis + 1
ReDim Preserve ShtArr(1 To vis)
ShtArr(vis) = Sheets(n + 2).Name
End If
Next n
End With
If vis > 0 Then
For n = 1 To UBound(ShtArr)
Sheets(ShtArr(n)).Visible = True
Next n
CmdUnSele.Enabled = True
If vis = Sheets.Count - 1 Then
CmdSelAll.Enabled = False
Else
CmdSelAll.Enabled = True
End If
Else
CmdUnSele.Enabled = False
CmdSelAll.Enabled = True
If vis < Sheets.Count - 1 Then
CmdSelAll.Enabled = True
End If
End If
If vis > 0 Then Sheets(ShtArr(1)).Activate
lbSi = vis & " / of " & (Sheets.Count - 1)
Application.ScreenUpdating = True
End Sub
Jika saya sisipkan satu sheet yang akan divery hidden dg nama "HIT",program
macronya seperti apa ?
Salam,Ade
On Fri, 09 Jan 2015 08:30:22 +0700, Hendrik Karnadi [email protected]
[belajar-excel] <[email protected]> wrote:
jadi maksudnya:- 2 sht (x dan y) very hidden- sht yang lain diselect
sekaligus?
Salam,HK
Sent from Samsung Mobile
-------- Original message --------From: "'M.Ade Alfarid' [email protected]
[belajar-excel]" Date:08/01/2015 20:13 (GMT+07:00) To:
[email protected] Subject: Re: [belajar-excel] Macro pilih allsheet
kecuali satu sheet
Dear Pak HK,
Supaya worksheet tersebut tidak ikut terselect dan selalu very hidden
bagaimana?karena macro class module nya menselect semua sheet.Apakah bisa
independent si worksheet tidak mau ikut terselect?
M.Ade
On Thu, 08 Jan 2015 20:22:30 +0700, Hendrik Karnadi [email protected]
[belajar-excel] <[email protected]> wrote:
CODE berisi langkah2 program yang akan dilakukan...
Sent from Samsung Mobile
-------- Original message --------From: "'M.Ade Alfarid' [email protected]
[belajar-excel]" Date:08/01/2015 19:49 (GMT+07:00) To:
[email protected] Subject: Re: [belajar-excel] Macro pilih allsheet
kecuali satu sheet
Kok compile error, ya Pak HK?
Private Sub kecuali_satu()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
If wks.Name <> "1" Or wks.Name <> "2" Then
code....
End If
Next wks
End Sub
Salam,Ade
On Thu, 08 Jan 2015 20:05:33 +0700, Hendrik Karnadi [email protected]
[belajar-excel] <[email protected]> wrote:
Hai Ade,Coba begini...(milihnya satu2 pake looping)
Dim wks as worksheetFor each wks in thisworkbook.worksheetsIf wks .name <> "x"
or wks.name <> "y" thenCODE....End IfNext wks
Salam,HK
Sent from Samsung Mobile
-------- Original message --------From: "'M.Ade Alfarid' [email protected]
[belajar-excel]" Date:08/01/2015 19:05 (GMT+07:00) To:
[email protected] Subject: [belajar-excel] Macro pilih allsheet
kecuali satu sheet [1 Attachment]
Dear,
Bagaimana macro memilih all worksheet kecuali satu atau lebih?
Ade
--
--
--
--
--
#yiv6986029945 #yiv6986029945 -- #yiv6986029945ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv6986029945
#yiv6986029945ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv6986029945
#yiv6986029945ygrp-mkp #yiv6986029945hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv6986029945 #yiv6986029945ygrp-mkp #yiv6986029945ads
{margin-bottom:10px;}#yiv6986029945 #yiv6986029945ygrp-mkp .yiv6986029945ad
{padding:0 0;}#yiv6986029945 #yiv6986029945ygrp-mkp .yiv6986029945ad p
{margin:0;}#yiv6986029945 #yiv6986029945ygrp-mkp .yiv6986029945ad a
{color:#0000ff;text-decoration:none;}#yiv6986029945 #yiv6986029945ygrp-sponsor
#yiv6986029945ygrp-lc {font-family:Arial;}#yiv6986029945
#yiv6986029945ygrp-sponsor #yiv6986029945ygrp-lc #yiv6986029945hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv6986029945
#yiv6986029945ygrp-sponsor #yiv6986029945ygrp-lc .yiv6986029945ad
{margin-bottom:10px;padding:0 0;}#yiv6986029945 #yiv6986029945actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv6986029945
#yiv6986029945activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv6986029945
#yiv6986029945activity span {font-weight:700;}#yiv6986029945
#yiv6986029945activity span:first-child
{text-transform:uppercase;}#yiv6986029945 #yiv6986029945activity span a
{color:#5085b6;text-decoration:none;}#yiv6986029945 #yiv6986029945activity span
span {color:#ff7900;}#yiv6986029945 #yiv6986029945activity span
.yiv6986029945underline {text-decoration:underline;}#yiv6986029945
.yiv6986029945attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv6986029945 .yiv6986029945attach div a
{text-decoration:none;}#yiv6986029945 .yiv6986029945attach img
{border:none;padding-right:5px;}#yiv6986029945 .yiv6986029945attach label
{display:block;margin-bottom:5px;}#yiv6986029945 .yiv6986029945attach label a
{text-decoration:none;}#yiv6986029945 blockquote {margin:0 0 0
4px;}#yiv6986029945 .yiv6986029945bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv6986029945
.yiv6986029945bold a {text-decoration:none;}#yiv6986029945 dd.yiv6986029945last
p a {font-family:Verdana;font-weight:700;}#yiv6986029945 dd.yiv6986029945last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv6986029945
dd.yiv6986029945last p span.yiv6986029945yshortcuts
{margin-right:0;}#yiv6986029945 div.yiv6986029945attach-table div div a
{text-decoration:none;}#yiv6986029945 div.yiv6986029945attach-table
{width:400px;}#yiv6986029945 div.yiv6986029945file-title a, #yiv6986029945
div.yiv6986029945file-title a:active, #yiv6986029945
div.yiv6986029945file-title a:hover, #yiv6986029945 div.yiv6986029945file-title
a:visited {text-decoration:none;}#yiv6986029945 div.yiv6986029945photo-title a,
#yiv6986029945 div.yiv6986029945photo-title a:active, #yiv6986029945
div.yiv6986029945photo-title a:hover, #yiv6986029945
div.yiv6986029945photo-title a:visited {text-decoration:none;}#yiv6986029945
div#yiv6986029945ygrp-mlmsg #yiv6986029945ygrp-msg p a
span.yiv6986029945yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv6986029945
.yiv6986029945green {color:#628c2a;}#yiv6986029945 .yiv6986029945MsoNormal
{margin:0 0 0 0;}#yiv6986029945 o {font-size:0;}#yiv6986029945
#yiv6986029945photos div {float:left;width:72px;}#yiv6986029945
#yiv6986029945photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv6986029945
#yiv6986029945photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv6986029945
#yiv6986029945reco-category {font-size:77%;}#yiv6986029945
#yiv6986029945reco-desc {font-size:77%;}#yiv6986029945 .yiv6986029945replbq
{margin:4px;}#yiv6986029945 #yiv6986029945ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv6986029945 #yiv6986029945ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv6986029945
#yiv6986029945ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv6986029945
#yiv6986029945ygrp-mlmsg select, #yiv6986029945 input, #yiv6986029945 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv6986029945
#yiv6986029945ygrp-mlmsg pre, #yiv6986029945 code {font:115%
monospace;}#yiv6986029945 #yiv6986029945ygrp-mlmsg *
{line-height:1.22em;}#yiv6986029945 #yiv6986029945ygrp-mlmsg #yiv6986029945logo
{padding-bottom:10px;}#yiv6986029945 #yiv6986029945ygrp-msg p a
{font-family:Verdana;}#yiv6986029945 #yiv6986029945ygrp-msg
p#yiv6986029945attach-count span {color:#1E66AE;font-weight:700;}#yiv6986029945
#yiv6986029945ygrp-reco #yiv6986029945reco-head
{color:#ff7900;font-weight:700;}#yiv6986029945 #yiv6986029945ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv6986029945 #yiv6986029945ygrp-sponsor
#yiv6986029945ov li a {font-size:130%;text-decoration:none;}#yiv6986029945
#yiv6986029945ygrp-sponsor #yiv6986029945ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv6986029945
#yiv6986029945ygrp-sponsor #yiv6986029945ov ul {margin:0;padding:0 0 0
8px;}#yiv6986029945 #yiv6986029945ygrp-text
{font-family:Georgia;}#yiv6986029945 #yiv6986029945ygrp-text p {margin:0 0 1em
0;}#yiv6986029945 #yiv6986029945ygrp-text tt {font-size:120%;}#yiv6986029945
#yiv6986029945ygrp-vital ul li:last-child {border-right:none
!important;}#yiv6986029945
