On Tuesday, October 1, 2013 10:00:50 AM UTC+5:30, wrote:
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.....
:(