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 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' [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
>
> --
>
>
>
>
> --
>
>
>
>
> --
>
>
>
>
> --
>
>
>
