Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Template for tab not whole document
I have a worksheet that the first tab is the template named "Template", what
I want to happen is I have a command button on the form called "New Entry" which copies the tab and makes "Template (2)" but thinking ahead some people may forget to hit the command button and start entering into the original template. I tried to just lock the sheet but then the lock carries over to the copied template. My current code is: Sub NewForm() Sheets("Template").Select Sheets("Template").Copy After:=Sheets(1) End Sub Could I add some code at the end to tell the copy to unprotect? Thanks!! Stacey |
#2
|
|||
|
|||
Password protect the worksheet Template.
Then unprotect the copy: Option Explicit Sub NewForm2() Sheets("Template").Copy After:=Sheets(1) ActiveSheet.Unprotect Password:="hi" End Sub ???? wrote: I have a worksheet that the first tab is the template named "Template", what I want to happen is I have a command button on the form called "New Entry" which copies the tab and makes "Template (2)" but thinking ahead some people may forget to hit the command button and start entering into the original template. I tried to just lock the sheet but then the lock carries over to the copied template. My current code is: Sub NewForm() Sheets("Template").Select Sheets("Template").Copy After:=Sheets(1) End Sub Could I add some code at the end to tell the copy to unprotect? Thanks!! Stacey -- Dave Peterson |
#3
|
|||
|
|||
Worked like a charm!!
I have another question for you, on this form there is another command button "Save Form" which I would like it to take data from cell K8 and use that as the tab name, if there isn't a name to use "Need Asset #-1" but if there is already an "Need Asset #-1" make the next one +1 "Need Asset #-2". Sounds like a messy way but when this form is first filled out there isn't an Asset # assigned until its been approved by all directors then #'s are assigned. Thanks again!! "Dave Peterson" wrote: Password protect the worksheet Template. Then unprotect the copy: Option Explicit Sub NewForm2() Sheets("Template").Copy After:=Sheets(1) ActiveSheet.Unprotect Password:="hi" End Sub ???? wrote: I have a worksheet that the first tab is the template named "Template", what I want to happen is I have a command button on the form called "New Entry" which copies the tab and makes "Template (2)" but thinking ahead some people may forget to hit the command button and start entering into the original template. I tried to just lock the sheet but then the lock carries over to the copied template. My current code is: Sub NewForm() Sheets("Template").Select Sheets("Template").Copy After:=Sheets(1) End Sub Could I add some code at the end to tell the copy to unprotect? Thanks!! Stacey -- Dave Peterson |
#4
|
|||
|
|||
What happens if the value in K8 already has a worksheet associated with it?
Option Explicit Sub testme02() Dim myStr As String Dim iCtr As Long With ActiveSheet myStr = .Range("K8").Value If Trim(myStr) < "" Then On Error Resume Next .Name = myStr If Err.Number < 0 Then MsgBox "Duplicate Asset in K8--fix and try again" Err.Clear End If On Error GoTo 0 Else myStr = "Need Asset #-" iCtr = 1 Do On Error Resume Next .Name = myStr & iCtr If Err.Number = 0 Then 'get out Exit Do Else iCtr = iCtr + 1 Err.Clear End If Loop On Error GoTo 0 End If End With End Sub SMac wrote: Worked like a charm!! I have another question for you, on this form there is another command button "Save Form" which I would like it to take data from cell K8 and use that as the tab name, if there isn't a name to use "Need Asset #-1" but if there is already an "Need Asset #-1" make the next one +1 "Need Asset #-2". Sounds like a messy way but when this form is first filled out there isn't an Asset # assigned until its been approved by all directors then #'s are assigned. Thanks again!! "Dave Peterson" wrote: Password protect the worksheet Template. Then unprotect the copy: Option Explicit Sub NewForm2() Sheets("Template").Copy After:=Sheets(1) ActiveSheet.Unprotect Password:="hi" End Sub ???? wrote: I have a worksheet that the first tab is the template named "Template", what I want to happen is I have a command button on the form called "New Entry" which copies the tab and makes "Template (2)" but thinking ahead some people may forget to hit the command button and start entering into the original template. I tried to just lock the sheet but then the lock carries over to the copied template. My current code is: Sub NewForm() Sheets("Template").Select Sheets("Template").Copy After:=Sheets(1) End Sub Could I add some code at the end to tell the copy to unprotect? Thanks!! Stacey -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
Sweet!!!! 2 for 2, that was a lot more complicated then I was trying to do.
Thanks for your time! "Dave Peterson" wrote: What happens if the value in K8 already has a worksheet associated with it? Option Explicit Sub testme02() Dim myStr As String Dim iCtr As Long With ActiveSheet myStr = .Range("K8").Value If Trim(myStr) < "" Then On Error Resume Next .Name = myStr If Err.Number < 0 Then MsgBox "Duplicate Asset in K8--fix and try again" Err.Clear End If On Error GoTo 0 Else myStr = "Need Asset #-" iCtr = 1 Do On Error Resume Next .Name = myStr & iCtr If Err.Number = 0 Then 'get out Exit Do Else iCtr = iCtr + 1 Err.Clear End If Loop On Error GoTo 0 End If End With End Sub SMac wrote: Worked like a charm!! I have another question for you, on this form there is another command button "Save Form" which I would like it to take data from cell K8 and use that as the tab name, if there isn't a name to use "Need Asset #-1" but if there is already an "Need Asset #-1" make the next one +1 "Need Asset #-2". Sounds like a messy way but when this form is first filled out there isn't an Asset # assigned until its been approved by all directors then #'s are assigned. Thanks again!! "Dave Peterson" wrote: Password protect the worksheet Template. Then unprotect the copy: Option Explicit Sub NewForm2() Sheets("Template").Copy After:=Sheets(1) ActiveSheet.Unprotect Password:="hi" End Sub ???? wrote: I have a worksheet that the first tab is the template named "Template", what I want to happen is I have a command button on the form called "New Entry" which copies the tab and makes "Template (2)" but thinking ahead some people may forget to hit the command button and start entering into the original template. I tried to just lock the sheet but then the lock carries over to the copied template. My current code is: Sub NewForm() Sheets("Template").Select Sheets("Template").Copy After:=Sheets(1) End Sub Could I add some code at the end to tell the copy to unprotect? Thanks!! Stacey -- Dave Peterson -- Dave Peterson |
#6
|
|||
|
|||
Sorry Dave, Excel code is not my forte but I have thought of one more
challenge, is there code I can write so none of the sheets can be deleted without a password? Thanks! "Dave Peterson" wrote: What happens if the value in K8 already has a worksheet associated with it? Option Explicit Sub testme02() Dim myStr As String Dim iCtr As Long With ActiveSheet myStr = .Range("K8").Value If Trim(myStr) < "" Then On Error Resume Next .Name = myStr If Err.Number < 0 Then MsgBox "Duplicate Asset in K8--fix and try again" Err.Clear End If On Error GoTo 0 Else myStr = "Need Asset #-" iCtr = 1 Do On Error Resume Next .Name = myStr & iCtr If Err.Number = 0 Then 'get out Exit Do Else iCtr = iCtr + 1 Err.Clear End If Loop On Error GoTo 0 End If End With End Sub SMac wrote: Worked like a charm!! I have another question for you, on this form there is another command button "Save Form" which I would like it to take data from cell K8 and use that as the tab name, if there isn't a name to use "Need Asset #-1" but if there is already an "Need Asset #-1" make the next one +1 "Need Asset #-2". Sounds like a messy way but when this form is first filled out there isn't an Asset # assigned until its been approved by all directors then #'s are assigned. Thanks again!! "Dave Peterson" wrote: Password protect the worksheet Template. Then unprotect the copy: Option Explicit Sub NewForm2() Sheets("Template").Copy After:=Sheets(1) ActiveSheet.Unprotect Password:="hi" End Sub ???? wrote: I have a worksheet that the first tab is the template named "Template", what I want to happen is I have a command button on the form called "New Entry" which copies the tab and makes "Template (2)" but thinking ahead some people may forget to hit the command button and start entering into the original template. I tried to just lock the sheet but then the lock carries over to the copied template. My current code is: Sub NewForm() Sheets("Template").Select Sheets("Template").Copy After:=Sheets(1) End Sub Could I add some code at the end to tell the copy to unprotect? Thanks!! Stacey -- Dave Peterson -- Dave Peterson |
#7
|
|||
|
|||
You could protect the workbook:
Tools|Protection|Protect Workbook|and make sure Structure is checked. This means that you can't move worksheets, add or delete worksheets or even rename them. If you record a macro when you lock the workbook, you'll see the code you need to add so that your copying of Template still works (as well as the renaming of sheets). Your code would look something like: ThisWorkbook.Unprotect Password:="hithere" 'do all your stuff ThisWorkbook.Protect structu=True, Windows:=False, Password:="hithere" Thisworkbook is the workbook that contains the code. I assumed that your code is in the same workbook--if it isn't, then maybe activeworkbook.unprotect.... And you'll have to provide a macro that gives the user the ability to delete any sheet. It would unprotect the workbook, delete the sheet, and then reprotect the workbook. Be aware that both this type of workbook protection and worksheet protection is easily broken by almost anyone interested enough to find these newsgroups. SMac wrote: Sorry Dave, Excel code is not my forte but I have thought of one more challenge, is there code I can write so none of the sheets can be deleted without a password? Thanks! "Dave Peterson" wrote: What happens if the value in K8 already has a worksheet associated with it? Option Explicit Sub testme02() Dim myStr As String Dim iCtr As Long With ActiveSheet myStr = .Range("K8").Value If Trim(myStr) < "" Then On Error Resume Next .Name = myStr If Err.Number < 0 Then MsgBox "Duplicate Asset in K8--fix and try again" Err.Clear End If On Error GoTo 0 Else myStr = "Need Asset #-" iCtr = 1 Do On Error Resume Next .Name = myStr & iCtr If Err.Number = 0 Then 'get out Exit Do Else iCtr = iCtr + 1 Err.Clear End If Loop On Error GoTo 0 End If End With End Sub SMac wrote: Worked like a charm!! I have another question for you, on this form there is another command button "Save Form" which I would like it to take data from cell K8 and use that as the tab name, if there isn't a name to use "Need Asset #-1" but if there is already an "Need Asset #-1" make the next one +1 "Need Asset #-2". Sounds like a messy way but when this form is first filled out there isn't an Asset # assigned until its been approved by all directors then #'s are assigned. Thanks again!! "Dave Peterson" wrote: Password protect the worksheet Template. Then unprotect the copy: Option Explicit Sub NewForm2() Sheets("Template").Copy After:=Sheets(1) ActiveSheet.Unprotect Password:="hi" End Sub ???? wrote: I have a worksheet that the first tab is the template named "Template", what I want to happen is I have a command button on the form called "New Entry" which copies the tab and makes "Template (2)" but thinking ahead some people may forget to hit the command button and start entering into the original template. I tried to just lock the sheet but then the lock carries over to the copied template. My current code is: Sub NewForm() Sheets("Template").Select Sheets("Template").Copy After:=Sheets(1) End Sub Could I add some code at the end to tell the copy to unprotect? Thanks!! Stacey -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to embed Word document into Excel and retain sizing, formatti. | Excel Discussion (Misc queries) | |||
how to send documents from document imaging to word? | Excel Discussion (Misc queries) | |||
Excel should not delete document after trying to send email | Excel Discussion (Misc queries) | |||
Need TEMPLATE for Document Management | Excel Discussion (Misc queries) | |||
Hyperlink to word document problem | Links and Linking in Excel |