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
hendrikkarn...@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>
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' q...@kkmi.co.id [belajar-excel]"Date:08/01/2015
20:13 (GMT+07:00)To: belajar-excel@yahoogroups.comSubject: 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
hendrikkarn...@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>
>wrote:
CODE berisi langkah2 program yang akan dilakukan...
Sent from Samsung Mobile
-------- Original message --------
From: "'M.Ade Alfarid' q...@kkmi.co.id [belajar-excel]"Date:08/01/2015
19:49 (GMT+07:00)To: belajar-excel@yahoogroups.comSubject: 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
hendrikkarn...@yahoo.com [belajar-excel]
<belajar-excel@yahoogroups.com> >>wrote:
Hai Ade,
Coba begini...(milihnya satu2 pake looping)
Dim wks as worksheet
For each wks in thisworkbook.worksheets
If wks .name <> "x" or wks.name <> "y" then
CODE....
End If
Next wks
Salam,
HK
Sent from Samsung Mobile
-------- Original message --------
From: "'M.Ade Alfarid' q...@kkmi.co.id [belajar-excel]"Date:08/01/2015
19:05 (GMT+07:00)To: belajar-excel@yahoogroups.comSubject:
[belajar-excel] Macro pilih allsheet kecuali satu sheet [1 Attachment]
Dear,
Bagaimana macro memilih all worksheet kecuali satu atau lebih?
Ade
--
--
--
--
