Forms wont show in VBA.... is this a known bug or do I have a corrupted Excel file ?
 

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

Forms wont show in VBA.... is this a known bug or do I have a corrupted Excel file ?
 

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

Forms wont show in VBA.... is this a known bug or do I have a corrupted Excel file ?
 

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

Forms wont show in VBA.... is this a known bug or do I have a corrupted Excel file ?
 

"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!

Forms wont show in VBA.... is this a known bug or do I have a corrupted Excel file ?
 

"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

Forms wont show in VBA.... is this a known bug or do I have a corrupted Excel file ?
 

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!

Forms wont show in VBA.... is this a known bug or do I have a corrupted Excel file ?
 

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

Forms wont show in VBA.... is this a known bug or do I have a corrupted Excel file ?
 

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

Forms wont show in VBA.... is this a known bug or do I have a corrupted Excel file ?
 

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


Thanks for your advice and support on this ...I am actaully quite upset
about this problem...I'll follow your advice and I'll try completely redoing
the form and I'll put the code in module so that the form is as light as
possible...so whats the score then do developers test their forms over and
over again as they work on a project or just use a version of VB to
manipulate excel instead of using VBA ?

Richard

Forms wont show in VBA.... is this a known bug or do I have a corrupted Excel file ?
 

Richard,

I don't know that it is form size or corruption that is causing the problem.
It could be some change to your unseen code that has
decided not to function. Do you have an older version that
still functions? What are the changes made since then?

If you go ahead and create a new form, my general guideline
would be...don't add controls or code to the form unless you
are reasonably sure they do what you want. Of course you have
to test it, but the testing is not what balloons the size it is the
code changes and the adding and removing of controls.
If for instance, you discover a need for a text box and must limit
user input to numbers only. Do you use the enter event, the exit
event or the keypress event or one of the other events? What code
do you use in the event? Test all that on a separate form and when
its perfected then add it to your project.

My Special Sort Excel add-in might be used to provide some perspective
on what file size you might expect...
The principal form has 44 controls and 625 lines of code
The .frm size is 20 KB. The project uses three additional modules
with about 1900 lines of code. There are also two other much smaller
forms. The entire project is 414KB.
When the design goals changed for the project, I created a
brand new form from scratch and am very glad I did.

Good luck with yours.

Regards,
Jim Cone
San Francisco, USA



"Richard Finnigan" wrote in message ...

Thanks for your advice and support on this ...I am actaully quite upset
about this problem...I'll follow your advice and I'll try completely redoing
the form and I'll put the code in module so that the form is as light as
possible...so whats the score then do developers test their forms over and
over again as they work on a project or just use a version of VB to
manipulate excel instead of using VBA ?

Richard

Forms wont show in VBA.... is this a known bug or do I have a corrupted Excel file ?
 

"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

Forms wont show in VBA.... is this a known bug or do I have a corrupted Excel file ?
 

"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