Calling procedure from UserForm Code Module

[rob nobel]

I have quite a number of user forms for which I want the big X disabled.
What I have is the following which does it well, but is there a way to apply
this to all the userforms?

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
MsgBox "Please use the cancel button to exit"
Cancel = True
End If
End Sub

Rob

 

[John Wilson]

Rob,

Is it all that hard to copy/paste that code to each UserForm??

Take a look at the downloads from this site for other ways to
do this:

http://www.bmsltd.co.uk/Excel/Default.htm

John

 

[rob nobel]

No John, not at all. I'm sorry, I did not explain what I wanted very well.
It's just a matter of saving bytes. So I thought putting the procedure
somewhere once and calling it from all the userforms might reduce the file
size.
But then again.....will that slow it down having to "look" for the procedure
elsewhere??
Rob

 

[John Wilson]

Rob,

I'll probably get beat up for my reply (but I'll still learn from it).

It's just a matter of saving bytes.

That's a point well taken but it also depends on your file size
and how much you'll save. If you can cut a 1.1 mb file down
to 1.0 mb, it'll surely load faster, but how much faster??
There are a lot of variables to consider there.

I've seen a lot of discussions in the ng that relate to the
speed of functions, procedures, etc.
Example: From a previous post by Harlan.....

x+0 executed in about 1/4 the time that x*1

That's great but how long did it take for x+0 to
execute?? A hundreth of a nanosecond??
How many hundreths of nanoseconds can I save by
rewriting my code??
Of course, if you're going to do a loop and use this a
hundred thousand times, well.............maybe the whole
procedure will run a millisecond faster???

Other than using up space and increasing file size, placing
that code on each of your UserForms (unless you have a
couple of hundred or so), probably isn't going to make that
much of a difference in the speed of your workbook.

As far as the code is concerned, I do not believe that having
a lot of code slows the workbook (unless you call it). Extra code
will inflate the workbook size and will delay it from opening
(and saving) but once it's opened, I don't think it makes a
bit of a difference in the speed of any particular sub that you
might have hundreds of other subs included in the workbook.

Be gentle guys (and gals),
John

 

[rob nobel]

Thanks for that John and I do hope you'll be treated kindly as that makes a
good deal of sense.
In creating this particular workbook, each time I add a bit of code, etc.,
it continues to grow and so I'm tending to look for ways to reduce the size.
By itself, the particular code in question is only a few lines and could
easily be transferred to each Userform (which I'll probably do). It seems to
me though, that by the time I've finished this wkbk, that if I haven't tried
to conserve bytes at the outset, the whole thing will be getting to a size I
can't fit onto a floppy. (It's already approaching that without much data.)
Nevertheless, I appreciate your comments as I also consider speed of
operation a huge priority.
Rob