Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel crashes and restarts when accessing newly created worksheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel crashes and restarts when accessing newly created worksheet
avneesh 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. 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. :-( -- What you see can depend on what you look for. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel crashes and restarts when accessing newly created worksheet
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..... :( |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Refresh data from original worksheet to newly created copy | Excel Discussion (Misc queries) | |||
code to attach a macro to a newly created worksheet | Excel Programming | |||
Copying & Pasting into a newly created worksheet | Excel Programming | |||
Referencing a newly created worksheet | Excel Worksheet Functions | |||
How do I return to newly created worksheet? | Excel Programming |