Dear All,

In my little macro, I make entries in a worksheet, then copy this and give
it a new name. then I go back to the "template" and want to remove the
contents of some cells.

1) First I create a copy of the active worksheet
2) Then I want to remove the button from the new worksheet. Currently I only
set it invisible. How can I remove it?
3) After creating the new worksheet I activate the original one again. No I
want to delete the contents (simple text) of a set of cells. How can I do
that. Currently I try by doing "ActiveSheet.Cells("C3").Text = "test"". But
that creates an error.

Moreover, I'm interested in how I check the name of the new worksheet, i.e.
to avoid that the same name exists already. Also interesting is to check
whether only valif characters will be used for the new name. This will be
defined by the user. Is there a validation function?

Many thanks in advance for your help,
Patrick

My code is as follows:

'Ablegen der Eingaben als neues Worksheet
Public Function CopyZufriedenheitsCheck()
'Variablen
Dim wsName As String
Dim MyTime
MyTime = Time

'Kopieren des Worksheets
ActiveSheet.Range("KdNr").Select
wsName = "RufNr. " & ActiveSheet.Range("C4").Text & "_" &
ActiveSheet.Range("I3").Text
Worksheets("Template Zufriedenheitscheck").Copy
After:=Worksheets("Template Zufriedenheitscheck")

ActiveSheet.Name = wsName

'Hinweis für den Benutzer
MsgBox "Ein neues Arbeitsblatt wurde erstellt. Der Name lautet: " &
ActiveSheet.Name

'Entfernen des Buttons auf dem neuen Blatt
ActiveSheet.OLEObjects.Visible = False

'Zurückspringen auf das Template
Worksheets(2).Activate

'Zurücksetzen der Werte
ActiveSheet.Cells("C3").Text = "test"
ActiveSheet.Range("C4").Text = ""
ActiveSheet.Range("C5").Text = ""
ActiveSheet.Range("I3").Text = ""

End Function

When referring to a range, .Text is a read-only attribute. You need
to use .Value to set a cells value.
To check whether a sheet already exists with that name, you can loop
through the collection of sheets.

Dim ws as Worksheet
For Each ws in Activeworkbook.Worksheets
If ws.Name=wsName Then
MsgBox "A sheet already exists with that name"
'Whatver else you want to do goes here
End If
Next ws

Patrick Braunschweig wrote:
Dear All,

In my little macro, I make entries in a worksheet, then copy this and give
it a new name. then I go back to the "template" and want to remove the
contents of some cells.

1) First I create a copy of the active worksheet
2) Then I want to remove the button from the new worksheet. Currently I only
set it invisible. How can I remove it?
3) After creating the new worksheet I activate the original one again. No I
want to delete the contents (simple text) of a set of cells. How can I do
that. Currently I try by doing "ActiveSheet.Cells("C3").Text = "test"". But
that creates an error.

Moreover, I'm interested in how I check the name of the new worksheet, i.e.
to avoid that the same name exists already. Also interesting is to check
whether only valif characters will be used for the new name. This will be
defined by the user. Is there a validation function?

Many thanks in advance for your help,
Patrick

My code is as follows:

'Ablegen der Eingaben als neues Worksheet
Public Function CopyZufriedenheitsCheck()
'Variablen
Dim wsName As String
Dim MyTime
MyTime = Time

'Kopieren des Worksheets
ActiveSheet.Range("KdNr").Select
wsName = "RufNr. " & ActiveSheet.Range("C4").Text & "_" &
ActiveSheet.Range("I3").Text
Worksheets("Template Zufriedenheitscheck").Copy
After:=Worksheets("Template Zufriedenheitscheck")

ActiveSheet.Name = wsName

'Hinweis für den Benutzer
MsgBox "Ein neues Arbeitsblatt wurde erstellt. Der Name lautet: " &
ActiveSheet.Name

'Entfernen des Buttons auf dem neuen Blatt
ActiveSheet.OLEObjects.Visible = False

'Zurückspringen auf das Template
Worksheets(2).Activate

'Zurücksetzen der Werte
ActiveSheet.Cells("C3").Text = "test"
ActiveSheet.Range("C4").Text = ""
ActiveSheet.Range("C5").Text = ""
ActiveSheet.Range("I3").Text = ""

End Function

See comments below.
--
p45cal


"Patrick Braunschweig" wrote:

Dear All,

In my little macro, I make entries in a worksheet, then copy this and give
it a new name. then I go back to the "template" and want to remove the
contents of some cells.

1) First I create a copy of the active worksheet
2) Then I want to remove the button from the new worksheet. Currently I only
set it invisible. How can I remove it?

ActiveSheet.OLEObjects.delete


3) After creating the new worksheet I activate the original one again. No I
want to delete the contents (simple text) of a set of cells. How can I do
that. Currently I try by doing "ActiveSheet.Cells("C3").Text = "test"". But
that creates an error.


ActiveSheet.Range("C3").clearcontents



Moreover, I'm interested in how I check the name of the new worksheet, i.e.
to avoid that the same name exists already.

include this in your code, perhaps in a loop like this:

Do
wsname = InputBox("enter new sheetname")
SheetNameAlreadyExists = False
For Each sht In ActiveWorkbook.Sheets
If wsname = sht.Name Then
SheetNameAlreadyExists = True
Exit For
End If
Next sht
Loop Until Not SheetNameAlreadyExists




Also interesting is to check
whether only valif characters will be used for the new name. This will be
defined by the user. Is there a validation function?

I've just written one:

Function IsValidSheetName(x As String)
IsValidSheetName = True
If Len(x) 31 Or x = "" Then IsValidSheetName = False
For i = 1 To Len(x)
If InStr("\/?*[]", Mid(x, i, 1)) 0 Then
IsValidSheetName = False
Exit For
End If
Next i
End Function



Many thanks in advance for your help,
Patrick

My code is as follows:

'Ablegen der Eingaben als neues Worksheet
Public Function CopyZufriedenheitsCheck()
'Variablen
Dim wsName As String
Dim MyTime
MyTime = Time

'Kopieren des Worksheets
ActiveSheet.Range("KdNr").Select
wsName = "RufNr. " & ActiveSheet.Range("C4").Text & "_" &
ActiveSheet.Range("I3").Text
Worksheets("Template Zufriedenheitscheck").Copy
After:=Worksheets("Template Zufriedenheitscheck")

ActiveSheet.Name = wsName

'Hinweis für den Benutzer
MsgBox "Ein neues Arbeitsblatt wurde erstellt. Der Name lautet: " &
ActiveSheet.Name

'Entfernen des Buttons auf dem neuen Blatt
ActiveSheet.OLEObjects.Visible = False

'Zurückspringen auf das Template
Worksheets(2).Activate

'Zurücksetzen der Werte
ActiveSheet.Cells("C3").Text = "test"
ActiveSheet.Range("C4").Text = ""
ActiveSheet.Range("C5").Text = ""
ActiveSheet.Range("I3").Text = ""

End Function

Patrick wrote:
<<I'm interested in how I check the name of the new worksheet, i.e. to
avoid that the same name exists already.

Public Function SheetExists(SheetName As String) As Boolean
Dim sht As Object

On Error Resume Next

Set sht = Sheets(SheetName)

SheetExists = Not (sht Is Nothing)
End Function

There are routines that are even more general purpose for determinining
if an object is in a collection or not, but the one above should work
for your purposes.
--
Regards,
Bill Renaud