[belajar-excel] Digest Number 3255

Milis Belajar Microsoft Excel Group

11 Messages

Digest #3255

1a

Macro pilih allsheet kecuali satu sheet by "M.Ade Alfarid" aderyo

1b

Re: Macro pilih allsheet kecuali satu sheet by "Hendrik Karnadi" hendrikkarnadi

1c

Re: Macro pilih allsheet kecuali satu sheet by "M.Ade Alfarid" aderyo

1d

Re: Macro pilih allsheet kecuali satu sheet by "Hendrik Karnadi" hendrikkarnadi

1e

Re: Macro pilih allsheet kecuali satu sheet by "M.Ade Alfarid" aderyo

1f

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

1g

Re: Macro pilih allsheet kecuali satu sheet by "M.Ade Alfarid" aderyo

1h

Re: Macro pilih allsheet kecuali satu sheet by "hendrik karnadi" hendrikkarnadi

2a

Re: Fungsi IF by "Hendrik Karnadi" hendrikkarnadi

3a

Re: Macro pilih allsheet kecuali satu sheet [1 Attachment] by "Hendrik Karnadi" hendrikkarnadi

3b

Re: Macro pilih allsheet kecuali satu sheet by "M.Ade Alfarid" aderyo

Messages

1a

Macro pilih allsheet kecuali satu sheet

Thu Jan 8, 2015 4:32 am (PST) . Posted by:

"M.Ade Alfarid" aderyo

Dear,

Bagaimana macro memilih all worksheet kecuali satu atau lebih?

Ade

--

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

1b

Re: Macro pilih allsheet kecuali satu sheet

Thu Jan 8, 2015 5:21 am (PST) . Posted by:

"Hendrik Karnadi" hendrikkarnadi

CODE berisi langkah2 program yang akan dilakukan...

Sent from Samsung Mobile

<div>-------- Original message --------</div><div>From: "'M.Ade Alfarid' QA@kkmi.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> </div><div>Date:08/01/2015 19:49 (GMT+07:00) </div><div>To: belajar-excel@yahoogroups.com </div><div>Subject: Re: [belajar-excel] Macro pilih allsheet kecuali satu sheet </div><div>
</div>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 (8) . Top ^

1c

Re: Macro pilih allsheet kecuali satu sheet

Thu Jan 8, 2015 5:42 am (PST) . Posted by:

"M.Ade Alfarid" aderyo

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.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
> 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.comSubject:
>> [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 (8) . Top ^

1d

Re: Macro pilih allsheet kecuali satu sheet

Thu Jan 8, 2015 5:29 pm (PST) . Posted by:

"Hendrik Karnadi" hendrikkarnadi

jadi maksudnya:
- 2 sht (x dan y) very hidden
- sht yang lain diselect sekaligus?

Salam,
HK

Sent from Samsung Mobile

<div>-------- Original message --------</div><div>From: "'M.Ade Alfarid' QA@kkmi.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> </div><div>Date:08/01/2015 20:13 (GMT+07:00) </div><div>To: belajar-excel@yahoogroups.com </div><div>Subject: Re: [belajar-excel] Macro pilih allsheet kecuali satu sheet </div><div>
</div>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 (8) . Top ^

1e

Re: Macro pilih allsheet kecuali satu sheet

Thu Jan 8, 2015 6:22 pm (PST) . Posted by:

"M.Ade Alfarid" aderyo

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.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
> 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.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
>> 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.comSubject:
>>> [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 (8) . Top ^

1f

Re: Macro pilih allsheet kecuali satu sheet

Thu Jan 8, 2015 7:06 pm (PST) . Posted by:

"Nang Agus"

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 (8) . Top ^

1g

Re: Macro pilih allsheet kecuali satu sheet

Thu Jan 8, 2015 7:16 pm (PST) . Posted by:

"M.Ade Alfarid" aderyo

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 ExplicitPrivate 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.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
>>> 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.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
>>>> 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.comSubject:
>>>>> [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 (8) . Top ^

1h

Re: Macro pilih allsheet kecuali satu sheet

Thu Jan 8, 2015 10:21 pm (PST) . Posted by:

"hendrik karnadi" hendrikkarnadi

Macronya ditulis di Module pada VBE Sheet.
Salam,HK

From: "'M.Ade Alfarid' QA@kkmi.co.id [belajar-excel]" <belajar-excel@yahoogroups.com>
To: belajar-excel@yahoogroups.com
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 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 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' 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

--


--


--


--


--
#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

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

2a

Re: Fungsi IF

Thu Jan 8, 2015 4:52 am (PST) . Posted by:

"Hendrik Karnadi" hendrikkarnadi

Pak ASL,
Jika yang jadi patokan adalah MNI setelah tanda "-" maka coba gunakan fungsi FIND untuk menemukan posisi karakter "-"
sebagai titik awal, kmdn kombinasikan dengan fungsi FIND dan MID untuk menemukan kata "MNI" setelah karakter tsb .
Jadi bahasa manusianya kira2 begini...
- Tentukan posisi karakter "-"
- Cari kata "MNI" setelah karakter "-"
- Jika ditemukan tulis "MNI" dan jika tidak ditemukan tulis "Salah".

Salam,
HK

Sent from Samsung Mobile

<div>-------- Original message --------</div><div>From: "Ahmad Syaifullah Lubis syaifullah@hotmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> </div><div>Date:08/01/2015 10:50 (GMT+07:00) </div><div>To: belajar-excel@yahoogroups.com </div><div>Subject: [belajar-excel] Fungsi IF </div><div>
</div>.

Dear BeExceller,
Mohon bantuannya bagaimana cara menggunakan fungsi "IF" untuk mengambil karakter yang sesuai.
contoh saya ingin mendapatkan karakter "MNI", apabila data ada unsur "MNI" maka hasilnya adalah "MNI", namun bila tidak ada karakter "MNI" maka hasilnya dibuatkan "Salah".
123/PRKM/MNI/X/2014 - OVB from MNI Jkt -> hasilnya "MNI"
124/PR/MNI/X/2014 - OVB from MMP -> hasilnya "SALAH"
125/PR/MNI/X/2014 - OVB from Achmad MNI -> hasilnya "MNI"
126/PK/MNI/X/2014 - MNI advanced Jkt -> hasilnya "MNI"
127/PK/MMP/X/2014 - Advanced MMP Bdg -> hasilnya "SALAH"

Data tsb saya letakkan di row A2 dan seterusnya.
kemudian di row B2 saya masukkan rumus if
=IF(A16="*MNI*";"MNI";"SALAH&quot;)
tetapi hasilnya tidak akurat.

sekali lagi mohon dibantu.

Terima kasih,
ASL

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

3a

Re: Macro pilih allsheet kecuali satu sheet [1 Attachment]

Thu Jan 8, 2015 5:04 am (PST) . Posted by:

"Hendrik Karnadi" hendrikkarnadi

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

<div>-------- Original message --------</div><div>From: "'M.Ade Alfarid' QA@kkmi.co.id [belajar-excel]" <belajar-excel@yahoogroups.com> </div><div>Date:08/01/2015 19:05 (GMT+07:00) </div><div>To: belajar-excel@yahoogroups.com </div><div>Subject: [belajar-excel] Macro pilih allsheet kecuali satu sheet [1 Attachment] </div><div>
</div>Dear,

Bagaimana macro memilih all worksheet kecuali satu atau lebih?

Ade

--

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

3b

Re: Macro pilih allsheet kecuali satu sheet

Thu Jan 8, 2015 5:16 am (PST) . Posted by:

"M.Ade Alfarid" aderyo

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.comSubject:
> [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 (2) . 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

• New Members 6

• Privacy • Unsubscribe • Terms of Use