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