This problem is driving me up the wall I have spent ages working on a user
form and now it wont open with the show method despite the fact that the
show method appears on the drop down list after the object name.

I did not realize that VBA in Excel was such a buggy fragile environment
becasue my only explanation is that the file has become corrupted in some
way or another. I'm looking at losing weeks of work here !

Any help or suggestions would be welcome.

--
Richard Finnigan

Richard,

Your code looks ok to me.<g

You could try removing the form, but export it first to save it.
Save the project then import the form back in.
Sometimes that can "clean" things up a little.

The other thing to check is the size of the form file.
I would suspect that if it is 50kb + then the size could be contributing
to the problem. A lot of form code can be placed in a standard
module and called from the form module.

If you have to rebuild it, you can copy the code out to Notepad
and save it. Then you just have to add new controls to a blank form.

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Richard Finnigan"
wrote in message...
This problem is driving me up the wall I have spent ages working on a user
form and now it wont open with the show method despite the fact that the
show method appears on the drop down list after the object name.

I did not realize that VBA in Excel was such a buggy fragile environment
becasue my only explanation is that the file has become corrupted in some
way or another. I'm looking at losing weeks of work here !

Any help or suggestions would be welcome.
--
Richard Finnigan

"Richard Finnigan"
wrote in message...
This problem is driving me up the wall I have spent ages working on a user
form and now it wont open with the show method despite the fact that the
show method appears on the drop down list after the object name.

I did not realize that VBA in Excel was such a buggy fragile environment
becasue my only explanation is that the file has become corrupted in some
way or another. I'm looking at losing weeks of work here !

Any help or suggestions would be welcome.
--
Richard Finnigan

Richard,

Your code looks ok to me.<g

You could try removing the form, but export it first to save it.
Save the project then import the form back in.
Sometimes that can "clean" things up a little.

The other thing to check is the size of the form file.
I would suspect that if it is 50kb + then the size could be contributing
to the problem. A lot of form code can be placed in a standard
module and called from the form module.

If you have to rebuild it, you can copy the code out to Notepad
and save it. Then you just have to add new controls to a blank form.

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Thanks for your advice on this one. It looks like a known problem, I'm glad
its not just me!

Richard,

One more thing to keep in mind for the next form you create...
It is very easy to increase the form file size but very difficult to reduce
the form file size. Removing code or controls does not decrease the
file size proportionally. Therefore, it is best to experiment and test
in a separate dummy/test form before creating your final version.

Jim Cone
http://www.realezsites.com/bus/primitivesoftware



"Richard Finnigan"
wrote in message
Thanks for your advice on this one. It looks like a known problem, I'm glad
its not just me!

Richard,

One more thing to keep in mind for the next form you create...
It is very easy to increase the form file size but very difficult to
reduce
the form file size. Removing code or controls does not decrease the
file size proportionally. Therefore, it is best to experiment and test
in a separate dummy/test form before creating your final version.

Jim Cone

Thanks for that though I have to say I am extremely disappionted that Excel
should be so flawed when it comes to handling forms...I havent encountered
these issues in VB6 or Access. Its a shame, is this a long running issue
with Excel ?

Richard

Richard,

I don't recall this issue with Excel 5.
However, xl5 used dialog sheets instead of user forms.
So I guess you would say its been there since xl 97 (xl 8).

There are major changes coming in the next XL release (xl 12).
Whether the issue is fixed or compounded has not been mentioned
in any discussions I've seen.

Regards,
Jim Cone
San Francisco, USA


"Richard Finnigan"
wrote in message
...

Richard,

One more thing to keep in mind for the next form you create...
It is very easy to increase the form file size but very difficult to
reduce
the form file size. Removing code or controls does not decrease the
file size proportionally. Therefore, it is best to experiment and test
in a separate dummy/test form before creating your final version.

Jim Cone

Thanks for that though I have to say I am extremely disappionted that Excel
should be so flawed when it comes to handling forms...I havent encountered
these issues in VB6 or Access. Its a shame, is this a long running issue
with Excel ?

Richard

Why not post the code which is not working?



--
Tim Williams
Palo Alto, CA


"Richard Finnigan" wrote in message
...
This problem is driving me up the wall I have spent ages working on a user
form and now it wont open with the show method despite the fact that the
show method appears on the drop down list after the object name.

I did not realize that VBA in Excel was such a buggy fragile environment
becasue my only explanation is that the file has become corrupted in some
way or another. I'm looking at losing weeks of work here !

Any help or suggestions would be welcome.

--
Richard Finnigan

"Tim Williams" <timjwilliams at gmail dot com wrote in message
Why not post the code which is not working?

Good Suggestion I will resort to that after transferring the code to modules
in order to keep the form as light as possible

Richard




--
Tim Williams
Palo Alto, CA


"Richard Finnigan" wrote in message
...
This problem is driving me up the wall I have spent ages working on a
user
form and now it wont open with the show method despite the fact that the
show method appears on the drop down list after the object name.

I did not realize that VBA in Excel was such a buggy fragile environment
becasue my only explanation is that the file has become corrupted in some
way or another. I'm looking at losing weeks of work here !

Any help or suggestions would be welcome.

--
Richard Finnigan

"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
Why not post the code which is not working?



--
Tim Williams
Palo Alto, CA

Well Tim here goes if anyone can spot my boo boos please let me know. It
might not llok like much but it took me a long time to figure out how to do
it.

'the vaData and bkData variables have been declared in the workbook module
Dim rgData As Range
Dim doSave As Boolean

Private Sub LoadRecord()
vaData = rgData.Value
txtName.Value = vaData(1, 1)
txtAge.Value = vaData(1, 2)
End Sub

Private Sub butDelete_Click()
Dim Response As Integer
Response = MsgBox("Are you sure that you want to delete this record?",
vbOKCancel, "Deleting a Record")
If Response = 1 Then

Sheets("Cast_Backup").Select
Dim iRowCount As Integer
With Range("CastBackUp")
..Select
iRowCount = .Rows.Count + 1
.Resize(iRowCount).Name = "CastBackUp"
Set bkData = .Rows(iRowCount)
End With
bkData.Value = vaData
Sheets("Casts").Select

If Range("Cast_name").Rows.Count = 1 Then
MsgBox "You cant delete every record", vbCritical
Exit Sub
ElseIf rgData.Row = Range("Cast_name").Rows(1).Row Then
Set rgData = rgData.Offset(1)
rgData.Offset(-1).Delete shift:=xlUp
Call LoadRecord
Else
scrNav.Value = scrNav.Value - 1
rgData.Offset(1).Delete shift:=xlUp
End If
scrNav.Max = scrNav.Max - 1
doSave = False
End If
End Sub

Private Sub butNew_Click()
ActiveSheet.unprotect
Add_Cast
With Range("Cast_name")
scrNav.Max = .Rows.Count
scrNav.Value = .Rows.Count
End With
protect_sheet
End Sub





Private Sub UserForm_Initialize()
With Range("Cast_name")
Set rgData = .Rows(1)
Call LoadRecord
scrNav.Value = 1
scrNav.Max = .Rows.Count
End With
End Sub

Private Sub SaveRecord()
vaData(1, 1) = txtName.Value
vaData(1, 2) = txtAge.Value
rgData.Value = vaData
End Sub

Private Sub scrNav_Change()

If doSave = True Then
Call SaveRecord
End If
Set rgData = Range("Cast_name").Rows(scrNav.Value)
Call LoadRecord
doSave = True
End Sub

"Richard Finnigan" wrote in message
...
This problem is driving me up the wall I have spent ages working on a user
form and now it wont open with the show method despite the fact that the
show method appears on the drop down list after the object name.

I did not realize that VBA in Excel was such a buggy fragile environment
becasue my only explanation is that the file has become corrupted in some
way or another. I'm looking at losing weeks of work here !

Any help or suggestions would be welcome.

--
Richard Finnigan

This problem was solved (hopefully) by taking the code out of the form
initialize event and putting the same code in the form activate event
....what's odd is that the form went gradually faulty rather just not working
straight away so testing the form did not show up any issues. formname.show
worked initially with the code in the form initialize event.

Strange......

Richard