PDA

View Full Version : How to add only 1 worksheet from an Add-in



ball_richard
08-22-2008, 06:20 AM
Hello, I have made an add-in that adds an item to the 'tools' bar and then opens a userform. The add-in relies on a hidden sheet to store data in the userform so that it is still available once the userform is closed/reopened.

For the add-in to work, I have put in a piece of code that adds a new worksheet with the correct name to whatever workbook the user is using... the only problem is that it adds a new sheet everytime the userform is opened. Is there a way of getting it to add just the first time the macro button is clicked so that you don't end up with a new sheet everytime....

The code used is..

Sub ShowForm()

On Error Resume Next

Set NewSheet = Worksheets.Add
NewSheet.Name = "dd"
Worksheets("dd").Visible = False
UndPileCalcForm.Show

End Sub

Any help would be greatly appreciated.. thanks!

Bob Phillips
08-22-2008, 08:11 AM
Sub ShowForm()
Dim sh As Worksheet

With ActiveWorkbook

On Error Resume Next
Set NewSheet = .Worksheets("dd")
On Error GoTo 0
If sh Is Nothing Then
Set sh = .Worksheets.Add
sh.Name = "dd"
End If
sh.Visible = False
UndPileCalcForm.Show
End With
End Sub

ball_richard
08-22-2008, 08:30 AM
Cheers xld - that worked a treat, changed the on error goto 0 line to on error resume next and it's perfect.

Bob Phillips
08-22-2008, 08:35 AM
What problem did Goto 0 give you then?

ball_richard
08-22-2008, 08:57 AM
It said 'cannot rename spreadsheet to a name that is already in use'

Bob Phillips
08-22-2008, 09:09 AM
I think that is because my code had an error. Try



Sub ShowForm()
Dim sh As Worksheet

With ActiveWorkbook

On Error Resume Next
Set sh = .Worksheets("dd")
On Error Goto 0
If sh Is Nothing Then
Set sh = .Worksheets.Add
sh.Name = "dd"
End If
sh.Visible = False
UndPileCalcForm.Show
End With
End Sub