Form not working, Cracking me up!

[Sir Babydum GBE]

Hi

What's wrong with the form on this workbook?

I complete it and what's supposed to happen is that it submits the entries onto a "Records" sheet, saves itself, unloads and reloads itself (to clear all the entries and reset the dropdowns).

This worked fine but now it isn't. I click submit (for testing purposes, in the first box choose "inappropriate for test & learn" and that will allow you to submit the form with no further data entry). And I can see from the status bar that it has moved the data to the records sheet because it's saving (and the save instruction comes at the end). But it seems to be the unloading and the reloading. My PC hangs.

All my users are getting problems (they each have a copy). Please can someone look through my coding and see what's going wrong?

Also there is a macro called create agent sheets. It's supposed to just copy itself and rename itself after each agent in the list (on the sheet "agents"). It does this - but it ends up being over 2mb big - whereas the original is much smaller.

Thanks

BD

[Bob Phillips]

You shouldn't try and reload it from within the form just after you unloaded it.

In the module that launches the form, use

[vba]

Sub ShowToolkitForm()

Application.Calculate
FormRetentionToolkit.Show
Unload FormRetentionToolkit
FormRetentionToolkit.Show
End Sub
[/vba]

In your code, how did you propose to end, as both submit and Cancel resubmit?

and wouldn't it just be better to have a method that resets all of the fields and give a proper exit?

[Sir Babydum GBE]

Thanks Bob

You shouldn't try and reload it from within the form just after you unloaded it.

I see. I'm a newbie with forms, but now I know.

In your code, how did you propose to end, as both submit and Cancel resubmit?

Sorry, not sure what you mean... My cancel button means "Cancel the current record" not "Close the form", reloading the form seemed to work well initially

and wouldn't it just be better to have a method that resets all of the fields and give a proper exit?

Probably, it just seemed quicker to re-initialise the form. But I guess I could just copy the code from the initialize bit a paste it to the end of submit and cancel.

[Bob Phillips]

Sorry, not sure what you mean... My cancel button means "Cancel the current record" not "Close the form", reloading the form seemed to work well initially

I know that, but I couldn't see in your code (if it had worked) how you would ever unload the form.

Probably, it just seemed quicker to re-initialise the form. But I guess I could just copy the code from the initialize bit a paste it to the end of submit and cancel.

Create a method called Initialize with all that code it, and call that befor eyou show the form, and on Reset.

[Sir Babydum GBE]

I know that, but I couldn't see in your code (if it had worked) how you would ever unload the form.

I just told the guys to click the x on the top of the form to close it at the end of their shift.

Create a method called Initialize with all that code in, and call that before you show the form, and on Reset.

I already have initialize, but I'll do what you suggest re calling it.

Thanks again

[Bob Phillips]

Personally, I would ALWAYS have a proper exit/unload button.

And just to be sure, I mean a method called Initialize (Public Function Initialize), not the built-in event (Userform_Initialize).

[Sir Babydum GBE]

Personally, I would ALWAYS have a proper exit/unload button.

Why? I'm not being cheeky - just curious.

And just to be sure, I mean a method called Initialize (Public Function Initialize), not the built-in event (Userform_Initialize).

i see what you mean - will do.

Any ideas on why the 171kb workbook becomes a 2mb one when the form is used?

[Bob Phillips]

It's a useability thing BD. Form design is an art, making sure that the key information is prominent, making sure that the navigational flow is good (for instance, it is generally considered to go left to right, row by row, not column by column as yours does), and that the form management is simple and intuitive. Most people think of buttons in this context, and having a button that clearly says Exit/Finish or whatever makes it all more obvious (not saying I am good at it, but I do at least try).

Excel workbooks do bloat, and for some odd reason it bloats by far more than is added to the workbook. I always clean a project before shipping it, using the Code Cleaner app by Rob Bovey http://www.appspro.com/Utilities/CodeCleaner.htm

[YellowLabPro]

Babydum,
I am reading the chapter on Userforms in Wrox's book VBA2002. Chapter 13 has some stuff on this. I don't know if you can get a copy. I was looking around for a ebook that I could copy the pages to you, but have not found it yet. It deals w/ cancelling the form and closing down the form w/ the "X" button.
Here are the snippets of code:
[vba]
Option Explicit
Public Cancelled As Boolean

Private Sub bnCancel_Click()
Cancelled = True
Me.Hide
End Sub

Private Sub bnOK_Click()
Cancelled = False
Me.Hide
End Sub
[/vba]

I cannot find the "X" so this is typed from the book:
[vba]
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode=vbFormControlMenu Then
Msgbox "Please use only the OK or Cancel Buttons", vbcritical
cancel = true
end if
end sub
[/vba]

[Sir Babydum GBE]

It's a useability thing BD. Form design is an art, making sure that the key information is prominent, making sure that the navigational flow is good (for instance, it is generally considered to go left to right, row by row, not column by column as yours does), and that the form management is simple and intuitive. Most people think of buttons in this context, and having a button that clearly says Exit/Finish or whatever makes it all more obvious (not saying I am good at it, but I do at least try).

Excel workbooks do bloat, and for some odd reason it bloats by far more than is added to the workbook. I always clean a project before shipping it, using the Code Cleaner app by Rob Bovey http://www.appspro.com/Utilities/CodeCleaner.htm

Ok, I see your point.

Our firewall won't let me get the cleaner - oh well.

One more question... In Initialize (the new one) is there a simple way of saying with vba: make everything except the frames enabled=false? Or do I need to name each component of the form, one at a time?

...Here are the snippets of code...

Cheers for that too!

[Bob Phillips]

[vba]

On Error Resume Next
For Each ctl In Me.Controls
If ctl.Name <> "FrameName" Then
ctl.Enabled = False
End If
Next ctl
On Error Goto 0
[/vba]

[Sir Babydum GBE]

[vba]

On Error Resume Next
For Each ctl In Me.Controls
If ctl.Name <> "FrameName" Then
ctl.Enabled = False
End If
Next ctl
On Error Goto 0
[/vba]

That's odd: Though I can see the "For" with my very own eyes, I get an error saying "Next without For"

[rory]

You must be missing some other terminating statement (e.g. End If) in another part of the code, or you have them out of sequence. (I'm assuming this has been put in amongst some other code?)

[Sir Babydum GBE]

You must be missing some other terminating statement (e.g. End If) in another part of the code, or you have them out of sequence. (I'm assuming this has been put in amongst some other code?)

Nope - just ran the code on its own to test it

[rory]

Works fine for me - you sure you didn't move the ctl.enabled = False line up onto the same line as the If... line?

[Sir Babydum GBE]

oops...

Bob might kill me now for suggesting his code didn't work - I couldn't have pasted it properly. Sorry.

(Just glad I don't live in the same town)

[Bob Phillips]

you mean the same country!

[YellowLabPro]

ahhhhh Bob is in Texas anyway....
I think near Bush....

[Sir Babydum GBE]

you mean the same country!

What, you really are in Chile?

I always thought you were lying.

[Bob Phillips]

ahhhhh Bob is in Texas anyway....
I think near Bush....

Dubblyaa is a good friend of mine. Actually Doug, I used to live in Austin Tx, great town.