Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto Refresh data from original worksheet to newly created copy wolverine Excel Discussion (Misc queries) 0 April 11th 10 04:10 PM
code to attach a macro to a newly created worksheet C. Corodan Excel Programming 2 July 30th 08 03:09 PM
Copying & Pasting into a newly created worksheet JohnUK Excel Programming 1 October 13th 07 02:10 PM
Referencing a newly created worksheet Charyn Excel Worksheet Functions 2 May 2nd 05 04:13 AM
How do I return to newly created worksheet? JJ[_5_] Excel Programming 3 December 29th 03 09:36 PM


All times are GMT +1. The time now is 04:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"