Solved: Excel Crashing Problems HELP

[BigJC]

<--- thats me, right now

Okay here is the problem

I ahve a userform with a multipage control and a command button

frmSchedule and multipage1 and cmd_Add

I CANNOT add a page the multipage control WITHOUT excel crashing. Error handling does NOTHING. i can report the error but not stop excel from crashing.

My code is simple:

[vba]
Option Explicit 'Always have it

Sub cmd_Add_Click()
Dim newPage as Page
Set newPage = Me.MultiPage1.Pages.Add
End Sub
[/vba]
That should add a blank page. I cant even get it to do that. It won't.

I have tried declaring newPage the following ways:
[vba]
Dim newPage
Dim newPage as Page
Dim newPage as object
[/vba]
I Tried to invoke the function in the following ways:
[vba]
Set newPage = frmSchedule.MultiPage1.Pages.Add
Set newPage = Me.MultiPage1.Pages.Add
Set newPage = MultiPage1.Pages.Add
[/vba]
I have tried all combinations of all three and every time i get the same damn error:

Automation Error.

Object invoked in disconnected from its client.

Then excel crashes NO MATTER what i do..

The other week, the code worked fine. not a problem, must have added and delete several thousand pages during testing. Now... NOTHING.


Please help.. what is it doing..

[Aflatoon]

Have you tried removing the form, choosing Yes when prompted to export, then re-importing it, in case there is any garbage in the op code?

[Rob342]

bigJC

Add the form 1st & initialize it before anything else, have tried this & works ok for me ( The form must be multipage)

[VBA]
Private Sub Ok_Click()
Me.MultiPage1.Pages.Add
End Sub
[/VBA]

[frank_m]

Are you using any Frames?

The reason I ask is because in my testing, Excel is crashing if I have a Frame on the Form, (seperate from) the Multipage control.
Within (or surronding) is ok.
[vba]
Option Explicit
Private Sub CommandButton1_Click()
Dim newPage As Page
Set newPage = Me.MultiPage1.Pages.Add
End Sub
[/vba]
Using Excel 2003 and Windows XP (both with up to date service packs)

[BigJC]

Alfatoon:

I have tried to do that upon your advice. No luck. Changes nothing.

Rob342:

I added the form first and ran it first before i added my code module. (i assume thats what you meant)

Didnt help.


frank_m

I had a frame inside a frame on the first page of the multipage. So i removed both frames.

Didn't help.


This is driving me mad.

[BigJC]

I though it may help if my code was on here to.

Here is my code it is commented out well both for me and for situations like this where i need help.

[vba]
Option Explicit

Dim inp
Dim LastUsedRow, NextRow, AddArea, AreaName, RowCnt, PageCount
Dim newPage

'This is the button the user clicks to add a new area (page)
Private Sub cmd_Add_Click()
'Get area name from user
ReDo:
inp = InputBox("Enter an area name:" & vbCrLf & "Note: Area names must be less than 31 characters and contain only letters, numbers and spaces")
If inp = "" Then Exit Sub
If Len(inp) > 31 Then
MsgBox ("Too Long!")
GoTo ReDo
Else
'Get las used row from sched
LastUsedRow = ThisWorkbook.Worksheets("Sched").Range("B" & Rows.Count).End(xlUp).Row

'Proceed to next row
NextRow = LastUsedRow + 1

'Clean the input string (function stored in code module to remove everything but a-z,A-Z,0-9 and spaces)
inp = strClean(inp)

'Add the area name to the sched
ThisWorkbook.Worksheets("Sched").Range("B" & NextRow).Value = inp

'Make a new page and add the new area to it.
Call AddNewArea((NextRow), (inp), True)

End If

End Sub

'This is my function to add the new page to the multipage and populate it
Sub AddNewArea(PageNum As Long, PageCaption As String, IsNew As Boolean)
Dim PageName
Dim newlblID As Control, newlblQTY As Control, newLstBx As Control
Dim c, LastCol, x1Val, x2Val, celVal, celHead

'Get pages from multipage and set the count into pagecount
PageCount = frmSchedule.MultiPage1.Pages.Count

'Set the new page name to "PageX" where x is the current row on worksheet sched
PageName = "Page" & PageNum

'Make a new page in the multipage control for the new area
newPage = ""
'Trap any errors
On Error GoTo Err1:
Set newPage = frmSchedule.MultiPage1.Pages.Add((PageName), (PageCaption))

'Scroll to new page ready for user
frmSchedule.MultiPage1.Value = PageCount

'Create the labels for the headers--- ITEM DESCRIPTION --- QUANTITY
Set newlblID = newPage.Controls.Add("Forms.Label.1", "lblItemID" & PageNum, True)
With newlblID
.Caption = "Item Description"
.Height = 10
.Left = 18
.Top = 12
End With

Set newlblQTY = newPage.Controls.Add("Forms.Label.1", "lblQTY" & PageNum, True)
With newlblQTY
.Caption = "Quantity"
.Height = 10
.Left = 510
.Top = 12
End With

'Create a 2 column list box 618 wide with coloumn 1 at 500 and coloumn 2 at 118
Set newLstBx = newPage.Controls.Add("Forms.ListBox.1", "ListItems" & PageNum, True)
With newLstBx
.ColumnCount = 2
.ColumnWidths = "500;100"
.Height = 480
.Left = 12
.Top = 30
.Width = 600
End With

'If adding areas from a list then do the following. (isnew was sent FALSE)
If IsNew <> True Then
'For Each Item in the currentrow of the schedule, get the header column (Row 1 of each column) of each cell and the cell value.
'celHead is the header cell or Item Description
'celVal is the value or Quantity of the items

'Determnine the last coloumn of items
LastCol = ThisWorkbook.Worksheets("Sched").UsedRange.Address(ReferenceStyle:=xlA1)

'Rerieve the column letter from the range address
LastCol = Mid(LastCol, 7, 2)

'Set x1 and x2 limits x1 begin x2 end of headers
'Start at column c as this is the first column of items
x1Val = "C" & PageNum

'Finish as the last column.
x2Val = LastCol & PageNum

'Start to loop through the cells
For Each c In ThisWorkbook.Worksheets("Sched").Range(x1Val, x2Val).Cells
If c.Value <> "" Then

'Get the qty value and store in to celVal
celVal = c.Value

'Get the item description (header) and store in celHead
celHead = ThisWorkbook.Worksheets("Sched").Cells(1, c.Column)

'Write the data in celVal and CelHead to the Listbox
newLstBx.AddItem celHead
newLstBx.Column(1, newLstBx.ListCount - 1) = celVal
End If
Next

newLstBx.Width = 600
newLstBx.Height = 480

End If

'If all went well exit the sub
Exit Sub

'on Error do the following.
Err1:
MsgBox (Err.Number & ": " & Err.Description & vbCrLf & "SOURCE: " & Err.Source & vbCrLf & "LastDLL:" & Err.LastDllError)
newPage = Empty
End Sub
[/vba]
As you can see i have tried my best to go through an qualify every statement as best as possible to try and reduce any errors.

Its annoying as everything worked as it should just last week. now i can't even get it to add a multipage even using the simple code i posted in my first post.

In a new workbook though i can get a multipage to add a new page using the same code but not on this project where i need it to.

[frank_m]

Try again Aflatoon's suggestion in post#2, except after you Delete the Form and choose "yes" to export a copy, be sure to:
(1) save the new version of your workbook (that no longer has the Form)
(2) close the workbook.
(3) reopen
(4) import the backed up copy you made of the Form.
(5) save that workbook version (that now has a new clean copy of the Form and it's code)
(6) try running your procedure

[Aflatoon]

If you have Rob Bovey's Code Cleaner addin I would also recommend just running that on your project. Any chance you can post the file itself for review? (no data should be necessary)

[BigJC]

Try again Aflatoon's suggestion in post#2, except after you Delete the Form and choose "yes" to export a copy, be sure to:
(1) save the new version of your workbook (that no longer has the Form)
(2) close the workbook.
(3) reopen
(4) import the backed up copy you made of the Form.
(5) save that workbook version (that now has a new clean copy of the Form and it's code)
(6) try running your procedure

Thats exactly what i did. To be sure though i tried it again now.. still messes up.

Also, this code cleaner, i am going to search for it and give it a try. As its been a loooooong time since i have done vb and its showing haha. my codes pretty messy.

[BigJC]

The code cleaner doesnt work with office 2010 or at least i can't get it to work

[Aflatoon]

It does in fact work with 2010 as I have it installed. What precise problem are you having with it?

[BigJC]

It does in fact work with 2010 as I have it installed. What precise problem are you having with it?

Dammit i can't find it anywhere so when i try to add the dll file manually to the com add ins it says its not a valid office application.

[Aussiebear]

Mate as a fellow Queenslander, may I suggest that you simplify the procedures somewhat. Rebuild each step until it goes awol. Then we know what we are dealing with.

[Jan Karel Pieterse]

Your code is referencing the userform, e.g.:

PageCount = frmSchedule.MultiPage1.Pages.Count

I bet frmSchedule is the actual name of the form in your project, right?

You should use the Me keyword instead, which ensures you refer to the current *instance* of the form:
PageCount = Me.MultiPage1.Pages.Count

[BigJC]

Ok guys. I give up..

I have completely re-written my code in a new workbook from scratch.

Still have the same problem. even if i change the variable names.

I have zipped my xl book and ask someone to have a look. Please...

The problem is in the function AddNewArea of frmSchedule code module.

[Jan Karel Pieterse]

I confirm the problem, it crashes for me too.
Maybe you need to consider a different approach, such as having a listbox with the items to which you add new ones. Clicking on an item in the listbox loads the proper information into other controls.

[BigJC]

having a listbox with the items to which you add new ones. Clicking on an item in the listbox loads the proper information into other controls.

The program is for pricing anything i do. (Electrician). So say for instance a house..

The areas are each area ie bathroom, bedroom living etc.. and within each area has a unspecified number of items and their associated quantities.

So i had the list box to list the items and quantities, 1 listbox per area and each area to be on a new tab of the multipage control.

That seemed the most logical. Im not sure how else i could do it making it simple to use, and simple to code.

I had the damn multipage bit working perfectly last week. Adding them, deleting them, loading information into them, no problems, but now nothing.

the only thing i added was the code to automatically load data from the sheets if the sheets had been filled out. ie the quote has been done and the file saved, but then the file is re-opened.

Thats when the problem arose and even taking that out does nothing.

[Jan Karel Pieterse]

Try removing the multipage and adding a fresh one.

[frank_m]

I downloaded your sample and converted it to 2003 as I don't have 2007.
I was getting the same crash.

On my machine the following solved it. (hope it will for you) fingers crossed
(1) I copied all the controls to the clipboard.
(2) added a Frame to cover most of the form.
(3) pasted the controls onto the Frame.
(4) Reposition the controls.

Sample 2003 version attached

[BigJC]

On my machine the following solved it. (hope it will for you) fingers crossed
(1) I copied all the controls to the clipboard.
(2) added a Frame to cover most of the form.
(3) pasted the controls onto the Frame.
(4) Reposition the controls.

WOW! that totally worked.. Thank you

But i get the annoying Run Time Eror 13 'Type Mismatch' now on that line. **FIXED**

I removed the declare as page option on the newPage variable to make it an unspecified type and it works fine now.

Why the hell would adding the frame like that stop this.. Better yet what the hell was casusing this?