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

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

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

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

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

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

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