Dear Mr Toby,
Berhasil...... Terima kasih atas masukannya, sangat bermanfaat sekali. Salam, DD From: Toby kenzo [email protected] [belajar-excel] [mailto:[email protected]] Sent: 19 Juni 2015 13:17 To: [email protected] Subject: Re: RE: RE: [belajar-excel] Tidak bisa Locked Range coba di ganti dari worksheets("Sheet5").visible = xlveryhidden= false di ganti menjadi: sheet5.visible= true untuk membuka file yg terhidden. Sent from Yahoo Mail on Android <http://overview.mail.yahoo.com/mobile/?.src=Android> _____ From: Dede [email protected] [belajar-excel] <[email protected]>; To: <[email protected]>; Subject: RE: RE: [belajar-excel] Tidak bisa Locked Range Sent: Fri, Jun 19, 2015 4:06:57 AM Dear Mr Toby, Sukses....., Ternyata masalahnya adalah di cell C1 ada karakter “/”. Soalnya a = Sheet4.Range("c1").Value saya gunakan sebagai acuan NamaFileBaru. Mohon koreksi untuk makro ini karena pada saat dijalankan file terSaveAs namun sheet5 yang very hidden tidak tampil, padahal sudah sata tambahkan script seperti warna merah dibawah ini : Private Sub CommandButton1_Click() Dim a As String a = Sheet4.Range("c1").Value On Error Resume Next Worksheet.Active Worksheet("Sheet5").Visible = xlVeryHidden = False Worksheet("Sheet5").Visible = xlVisible = True Sheets("Persetujuan Pemusnahan BS").Select Range("B3:D4").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False ActiveWorkbook.Save ChDir _ "D:\BAD STOCK\2015\06 Juni 2015\Persetujuan Pemusnahan BS PMA GT-MT Nasional Juni 2015\Email Kirim ke Mr. Tian" ActiveWorkbook.SaveAs Filename:= _ "D:\BAD STOCK\2015\06 Juni 2015\Persetujuan Pemusnahan BS PMA GT-MT Nasional Juni 2015\Email Kirim ke Mr. Tian\" & a & ".xlsb" _ , FileFormat:=xlExcel12, CreateBackup:=False ActiveWindow.Close End Sub Harapannya adalah di file yang baru sheet tersebut tampil dengan sendirinya, mohon arahannya. Salam, DD From: Toby kenzo [email protected] [belajar-excel] [mailto:[email protected]] Sent: 19 Juni 2015 10:22 To: [email protected] Subject: Re: RE: [belajar-excel] Tidak bisa Locked Range coba pake string Sent from Yahoo Mail on Android <http://overview.mail.yahoo.com/mobile/?.src=Android> _____ From: Dede [email protected] [belajar-excel] <[email protected]>; To: <[email protected]>; Subject: RE: [belajar-excel] Tidak bisa Locked Range Sent: Fri, Jun 19, 2015 2:54:50 AM Dear Mr Toby, Sukses..... Terima kasih atas ilmunya. Sedikit saja saya mau bertanya, ini tentang “ Dim jmlhbaris As Integer”. Analisa saya jmlhbaris = dalam bentuk angka Pertanyaan saya kalau jmlhbaris = dalam bentuk text, “Dim jmlhbaris As .......?” Saya sedang bereksperimen masalah : Dim a As String a = Sheet4.Range("c1").Value Makro Sukses kalau “Sheet4 cell C1” dalam bentuk angka, tapi begitu saya rubah “Sheet4 cell C1” dalam bentuk text makro tidak berjalan sebagaimana yang diharapkan. Moho arahannya. Salam, DD From: Toby kenzo [email protected] [belajar-excel] [mailto:[email protected]] Sent: 18 Juni 2015 11:29 To: [email protected] Subject: Bls: [belajar-excel] Tidak bisa Locked Range Dear DD, Coba buat commonButton dan masukan program ini: Dim jmlhbaris As Integer jmlhbaris = Sheet4.Range("C4").Value ActiveSheet.Unprotect Password:="a" Sheet4.Range(Cells(6, 3), Cells(jmlhbaris, 3)).Select Selection.Locked = True Selection.FormulaHidden = True ActiveSheet.Protect DrawingObjects:=False, _ Contents:=True, Scenarios:=False, _ AllowFormattingCells:=True, _ AllowFormattingColumns:=True, _ AllowFormattingRows:=True, _ AllowInsertingColumns:=True, _ AllowInsertingRows:=True, _ AllowInsertingHyperlinks:=True, _ AllowDeletingColumns:=True, _ AllowDeletingRows:=True, _ AllowSorting:=True, _ AllowFiltering:=True, _ AllowUsingPivotTables:=True ActiveSheet.Protect Password:="a" TerimaKasih. Pada Kamis, 18 Juni 2015 9:37, "Dede [email protected] [belajar-excel]" <[email protected]> menulis: Dear Be Exceller, Mohon bantuannya untuk koreksi makro ini : Sub Macro4() Jmlhbrs = Range("a1").Value Range("a2").Select For i = 1 To Jmlhbrs ActiveCell.Offset(1, 0).Select j = ActiveCell.Value On Error Resume Next Workbooks.Open Filename:="D:\BAD STOCK\Master\STD-STM PMA - Copy.xlsb" ActiveSheet.Unprotect Password:="a" Range("a6").Value = j Range("c4").Value = s Range("" & s & "+1,1:C100").Select Selection.Locked = False Selection.FormulaHidden = True Range("c6:" & s & "").Select Selection.Locked = True Selection.FormulaHidden = True ActiveSheet.Protect Password:="a" ChDir "D:\BAD STOCK\Email" ActiveWorkbook.SaveAs Filename:="D:\BAD STOCK\Email\" & j & ".xlsb", _ FileFormat:=xlExcel12, CreateBackup:=False ActiveWindow.Close Next i End Sub Harapannya pada saat makro dijalankan, kolom C pada file terlampir dimana terdapat kode barang maka range tersebut dalam keadaan ter-“Locked”. Demikian dan terima kasih. Salam, DD _____ This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual to whom it is addressed. If you have received this email in error please send it back to the person that sent it to you. Any views or opinions presented are solely those of its author and do not necessarily represent those of NABATI or any of its subsidiary companies. Unauthorized publication, use, dissemination, forwarding, printing or copying of this email and its associated attachments is strictly prohibited.
