Excel crashes and restarts when accessing newly created worksheet

[avneesh]

I am very new in VBA. Here is problem I am facing - I have to create multiple worksheets (Each having a button, I use a command button ) in a workbook.. All goes well when I create my first worksheet but after that I check allsheets for next worksheet creation (I check it to make sure that worksheetdoesn't already exist). When I access name of newly created excel sheet excel closes and restarts.

Here's my code:

' This function i am using for creating sheet

Function CreateNewSheet(SheetName As String, Mainbook As String)

Set MainWkbk = Workbooks(Mainbook)
MainWkbk.Activate
ActiveWorkbook.Sheets(SheetName).Activate
Call AddButtonsGDemand(SheetName, "Delete Demand Forecast", Mainbook)
Workbooks(Mainbook).Save
End Function

' this is the function for adding button with its event
Public Sub AddButtonsGDemand(strSheetName As String, caption As String, Mainbook As String)
Dim btn As OLEObject
Dim cLeft As Double
Dim cTop As Double
Dim cWidth As Double
Dim cHeight As Double

cHeight = 24
cWidth = 186.75
Workbooks(Mainbook).Activate

With Worksheets(strSheetName).Range("D" & (2))
cLeft = .Left + 5
cTop = .Top + 3
End With

With Worksheets(strSheetName)
Set btn = .OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=True, _
DisplayAsIcon:=False, Left:=cLeft, Top:=cTop, Width:=cWidth, _
Height:=cHeight)
End With
btn.Object.caption = caption
btn.Object.Font.Bold = True
btn.Name = "Del"

With ActiveWorkbook.VBProject.VBComponents( _
ActiveWorkbook.Worksheets(strSheetName).CodeName).CodeModule
.InsertLines 1, "Private Sub " & btn.Name & "_Click()" & vbCrLf & _`enter code here`
"Dim ob As New Class1 " & vbCrLf & _
"ob.DeleteWorksheet (ActiveSheet.Name)" & vbCrLf & _
"End Sub"
End With
Workbooks(Mainbook).Save
End Sub

 

[Auric__]

I am very new in VBA. Here is problem I am facing - I have to create
multiple worksheets (Each having a button, I use a command button ) in a
workbook. All goes well when I create my first worksheet but after that
I check all sheets for next worksheet creation (I check it to make sure
that worksheet doesn't already exist). When I access name of newly
created excel sheet excel closes and restarts.

Your code isn't actually adding a new sheet. Try this instead:

Sub CreateNewSheet(SheetName As String, Mainbook As String)
Dim sht As Worksheet, MainWkbk As Workbook
Set MainWkbk = Workbooks(Mainbook)
MainWkbk.Activate
Set sht = MainWkbk.Sheets.Add
sht.Name = SheetName
sht.Activate
Call AddButtonsGDemand(SheetName, "Delete Demand Forecast", Mainbook)
Workbooks(Mainbook).Save
End Sub

That should solve the crashing problem. Some other problems I noticed:

- This line:

With Worksheets(strSheetName).Range("D" & (2))

....should probably be replaced with this:

With Worksheets(strSheetName).Range("D2")

- This line in AddButtonsGDemand() has a small problem ...

.InsertLines 1, [...] & vbCrLf & _`enter code here`

Remove the part that says "`enter code here`", including both backticks.
(Unless that's some feature that's supposed to work and I've never heard of
it. Keeps the code from running under my install of Office 2010, though.)

- Finally, your code should be passing Worksheet and Workbook objects, not
just their names as strings. I tried rewriting it as such, but I'm waaaaaaay
too tired for it to actually get it working. :-(

 

[avneesh]

I am very new in VBA. Here is problem I am facing - I have to create multiple worksheets (Each having a button, I use a command button ) in a workbook. All goes well when I create my first worksheet but after that I check all sheets for next worksheet creation (I check it to make sure that worksheet doesn't already exist). When I access name of newly created excel sheet excel closes and restarts.



Here's my code:



' This function i am using for creating sheet



Function CreateNewSheet(SheetName As String, Mainbook As String)



Set MainWkbk = Workbooks(Mainbook)

MainWkbk.Activate

ActiveWorkbook.Sheets(SheetName).Activate

Call AddButtonsGDemand(SheetName, "Delete Demand Forecast", Mainbook)

Workbooks(Mainbook).Save

End Function



' this is the function for adding button with its event

Public Sub AddButtonsGDemand(strSheetName As String, caption As String, Mainbook As String)

Dim btn As OLEObject

Dim cLeft As Double

Dim cTop As Double

Dim cWidth As Double

Dim cHeight As Double



cHeight = 24

cWidth = 186.75

Workbooks(Mainbook).Activate



With Worksheets(strSheetName).Range("D" & (2))

cLeft = .Left + 5

cTop = .Top + 3

End With



With Worksheets(strSheetName)

Set btn = .OLEObjects.Add(ClassType:="Forms.CommandButton.1",Link:=True, _

DisplayAsIcon:=False, Left:=cLeft, Top:=cTop, Width:=cWidth, _

Height:=cHeight)

End With

btn.Object.caption = caption

btn.Object.Font.Bold = True

btn.Name = "Del"



With ActiveWorkbook.VBProject.VBComponents( _

ActiveWorkbook.Worksheets(strSheetName).CodeName).CodeModule

.InsertLines 1, "Private Sub " & btn.Name & "_Click()" & vbCrLf &_`enter code here`

"Dim ob As New Class1 " & vbCrLf & _

"ob.DeleteWorksheet (ActiveSheet.Name)" & vbCrLf & _

"End Sub"

End With

Workbooks(Mainbook).Save

End Sub

....Thx for Ur precious time but I try that code but its not working.
And the code That I use previously was working fine (In term of adding new Sheet on validating and formatting its column).
I think there is problem in code where I write event of buttons coz after calling that function That sheet become unreadable or i can say unreachable coz excel restarted when I used that sheet in my code for any purpose.....