|
Hide and Seek
I have a worksheet in my workbook that is used as a master copy. I hav a macro that creates new worksheets based on this worksheet. I want t hide the master worksheet so that it can not be seen an therefore i less likly to be altered. However when I hide this sheet all of th copies that are made from this sheet also hide. Is there a way or a code that will allow me to hide this sheet bu still allow the copies that are made from it to be visible? I would really like to know. I am learning vba as I go so if this is stupid or easy question don't hammer me to hard about it This is the code I am using to create and copy my worksheet from Code ------------------- Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Dim newSheetName As String Set ws = Worksheets("CGS") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a part number If Trim(Me.LstNm.Value) = "" Then Me.LstNm.SetFocus MsgBox "Please enter last name" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.LstNm.Value ws.Cells(iRow, 2).Value = Me.FrstNm.Value newSheetName = ws.Cells(iRow, 1) & "," & ws.Cells(iRow, 2) 'clear the data Me.LstNm.Value = "" Me.FrstNm.Value = "" Me.LstNm.SetFocus For Each ws In Worksheets If ws.Name = newSheetName Or _ newSheetName = "" Or _ IsNumeric(newSheetName) Then MsgBox "Sheet already exists or name is invalid", vbInformation Exit Sub End If Next Sheets("SS").Copy befo=Sheets(1) Sheets(1).Name = newSheetName Sheets(newSheetName).Move After:=Sheets(Sheets.Count) 'close userform Unload Me End Sub ------------------- -- oberon.blac ----------------------------------------------------------------------- oberon.black's Profile: http://www.excelforum.com/member.php...fo&userid=2673 View this thread: http://www.excelforum.com/showthread.php?threadid=46644 |
Hide and Seek
I don't see in your code the part where you hide your "master" sheet.. There seems nothing wrong w/ your code, really. Just be carefu though: newSheetName = ws.Cells(iRow, 1) *& "," &* ws.Cells(iRow, 2) ... For Each ws In Worksheets If ws.Name = newSheetName Or _ *newSheetName = ""* Or _ IsNumeric(newSheetName) Then ... *newSheetName* will never be an empty String ("")... Anywayz... I also once created an Excel prog wherein I have a hidde (master) sheet (xlSheetVeryHidden). I also create copies of this hidde sheet. What I did was to show it, create a copy, then hide it again When I hide it (master), the copied sheet doesn't hide. So, I'm no sure why you have the problem... :confused: I did something like: ... TheHiddenSheet.Visible = xlSheetVisible TheHiddenSheet.Copy After:=Worksheets(Worksheets.Count) TheHiddenSheet.Visible = xlSheetVeryHidden ... oberon.black Wrote: I have a worksheet in my workbook that is used as a master copy. I hav a macro that creates new worksheets based on this worksheet. I want t hide the master worksheet so that it can not be seen an therefore i less likly to be altered. However when I hide this sheet all of th copies that are made from this sheet also hide. Is there a way or a code that will allow me to hide this sheet bu still allow the copies that are made from it to be visible? I would really like to know. I am learning vba as I go so if this is stupid or easy question don't hammer me to hard about it This is the code I am using to create and copy my worksheet from Code ------------------- Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Dim newSheetName As String Set ws = Worksheets("CGS") 'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for a part number If Trim(Me.LstNm.Value) = "" Then Me.LstNm.SetFocus MsgBox "Please enter last name" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 1).Value = Me.LstNm.Value ws.Cells(iRow, 2).Value = Me.FrstNm.Value newSheetName = ws.Cells(iRow, 1) & "," & ws.Cells(iRow, 2) 'clear the data Me.LstNm.Value = "" Me.FrstNm.Value = "" Me.LstNm.SetFocus For Each ws In Worksheets If ws.Name = newSheetName Or _ newSheetName = "" Or _ IsNumeric(newSheetName) Then MsgBox "Sheet already exists or name is invalid", vbInformation Exit Sub End If Next Sheets("SS").Copy befo=Sheets(1) Sheets(1).Name = newSheetName Sheets(newSheetName).Move After:=Sheets(Sheets.Count) 'close userform Unload Me End Sub ------------------- -- T-容 ----------------------------------------------------------------------- T-容x's Profile: http://www.excelforum.com/member.php...fo&userid=2657 View this thread: http://www.excelforum.com/showthread.php?threadid=46644 |
Hide and Seek
you are correct i have not hidden my sheet, and that because I do no know how to hide it. I am very new to vba code i construct the code piece by piece with lot of help. like the code you gave me i am sure it would probably solve my issu however I do not know where or how to add it to my existing code. please help. I need to get a step by step book on creating vba code in excel or jus in general. I am currently a student but have not started int programming yet class does not kick off until 9-26-05. thanx and hope to hear from you soo -- oberon.blac ----------------------------------------------------------------------- oberon.black's Profile: http://www.excelforum.com/member.php...fo&userid=2673 View this thread: http://www.excelforum.com/showthread.php?threadid=46644 |
Hide and Seek
A Sheet has a 'Visible' property (look in the Properties Explorer). You can set it to 'xlSheetVisible' (default), 'xlSheetHidden', or 'xlSheetVeryHidden'. A Sheet with its Visible property set to xlSheetHidden can be easily unhidden through menu 'Format | Sheet | Unhide...'. A Sheet set to Visible = xlSheetVeryHidden can only be unhidden through VBA (code or Properties Explorer). Anywayz... In your code I noticed this: SHEETS(\"SS\").COPY BEFO=SHEETS(1) Sheets(1).Name = newSheetName Sheets(newSheetName).Move After:=Sheets(Sheets.Count) Is Sheet "SS" your hidden sheet? If it is, then you can simply add: SHEETS(\"SS\").VISIBLE = XLSHEETVISIBLE before you copy, then, add: SHEETS(\"SS\").VISIBLE = XLSHEETVERYHIDDEN[/b] 'OR XLSHEETHIDDEN, IF YOU LIKE AFTER YOU COPY... (THE SHEET MUST BE VISIBLE BEFORE IT CAN BE COPIED...) NOTE: I THINK IT WOULD BE BETTER TO ACCESS THE MASTER SHEET OBJECT DIRECTLY RATHER THAN ACCESS IT THROUGH THE SHEETS COLLECTION. SO, IF YOUR MASTER SHEET (\"SS\") IS NAMED (CODENAME - THE '(NAME)' PROPERTY IN THE PROPERTIES EXPLORER) \"SHEETSS\", THEN YOU'D DO SOMETHING LIKE: [b]SHEETSS.VISIBLE = XLSHEETVISIBLE SHEETSS.COPY BEFO=SHEETS(1) SHEETSS.VISIBLE = XLSHEETVERYHIDDEN Sheets(1).Name = newSheetName Sheets(newSheetName).Move After:=Sheets(Sheets.Count) I learned VBA - Excel mostly by browsing the Help and Object Explorer (and a lot of trial-and-error coding and, of course, help from nice people in this forum :) )... I'm sorry I can't suggest a book or another website... oberon.black Wrote: you are correct i have not hidden my sheet, and that because I do not know how to hide it. I am very new to vba code i construct the code piece by piece with lots of help. like the code you gave me i am sure it would probably solve my issue however I do not know where or how to add it to my existing code. please help. I need to get a step by step book on creating vba code in excel or just in general. I am currently a student but have not started into programming yet class does not kick off until 9-26-05. thanx and hope to hear from you soon -- T-容x ------------------------------------------------------------------------ T-容x's Profile: http://www.excelforum.com/member.php...o&userid=26572 View this thread: http://www.excelforum.com/showthread...hreadid=466447 |
Hide and Seek
This worked perfect... Thanx for all your help -- oberon.black ------------------------------------------------------------------------ oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732 View this thread: http://www.excelforum.com/showthread...hreadid=466447 |
| All times are GMT +1. The time now is 04:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com