1 Message

Digest #3256

Re: Macro pilih allsheet kecuali satu sheet by "Nang Agus"

Message

1a

Re: Macro pilih allsheet kecuali satu sheet

Fri Jan 9, 2015 8:04 pm (PST) . Posted by:

"Nang Agus"

Di modul

On Fri, Jan 9, 2015 at 9:50 AM, 'M.Ade Alfarid' QA@kkmi.co.id
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:

>
>
> 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 nangagus@gmail.com
> [belajar-excel] <belajar-excel@yahoogroups.com> 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' QA@kkmi.co.id
> [belajar-excel] <belajar-excel@yahoogroups.com> 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
>> hendrikkarnadi@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' QA@kkmi.co.id [belajar-excel]"
>> Date:08/01/2015 20:13 (GMT+07:00)
>> To: belajar-excel@yahoogroups.com
>> 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
>> hendrikkarnadi@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' QA@kkmi.co.id [belajar-excel]"
>> Date:08/01/2015 19:49 (GMT+07:00)
>> To: belajar-excel@yahoogroups.com
>> 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
>> hendrikkarnadi@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' QA@kkmi.co.id [belajar-excel]"
>> Date:08/01/2015 19:05 (GMT+07:00)
>> To: belajar-excel@yahoogroups.com
>> Subject: [belajar-excel] Macro pilih allsheet kecuali satu sheet [1
>> Attachment]
>>
>>
>>
>> Dear,
>>
>> Bagaimana macro memilih all worksheet kecuali satu atau lebih?
>>
>> Ade
>>
>> --
>>
>>
>>
>>
>> --
>>
>>
>>
>>
>> --
>>
>>
>>
>>
>> --
>>
>>
>
>
>
> --
>
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com

posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com

berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages

melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/

menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com

keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------

Visit Your Group