help! user form issues; can't find subroutines; appearance issue

I am writing a simple front-end for a sql server database out of Excel. I
have done something similar out of Access, but I am constrained to Excel this
time around and the slight differences between the two in approach is causing
me problem.

Two problems:
I have built a user form which alllows the user to select a report (the name
of a sql stored procedure) that he wants to run. I have written a generic vba
subroutine to run the user-selected procedure named, oh so cleverly,
runselectedprocedure.
The call runselectedprocedure line coded in the double-click event of a list
box bombs, saying it can't find the subroutine (sub or fuction not defined).
Where do I put the code for the subroutine ( I don't want to put it on the
form, because I will be having multiple forms needing to access the same
subroutine)

I have tried putting the subroutine both under "sheet1" and "thisworkbook"
sections under the VBA Project Explorer. I have tried Public Sub and Private
Sub --neither works.

Second,
I open the user form with a .show command under the workbook_open event.
It doesn't look like I want it to. For instance, I have tried both a
docmd.maximize and docmd.minimize line under the userform_open event. The
form looks the same regardless of whether it is minimized or maximized. Takes
up most of the screen but not all. Any suggestions?

I would appreciate any help anyone can give.

put it in a general module

In the vbe, do Insert=>Module

See Stephen Bullens site and down load formfun.zip and oncompress it to see
how you can manipulate a userform.


http://www.oaltd.co.uk/MVP/Default.htm

--
Regards,
Tom Ogilvy



"in-over-his-head-bill" wrote:

> I am writing a simple front-end for a sql server database out of Excel. I
> have done something similar out of Access, but I am constrained to Excel this
> time around and the slight differences between the two in approach is causing
> me problem.
>
> Two problems:
> I have built a user form which alllows the user to select a report (the name
> of a sql stored procedure) that he wants to run. I have written a generic vba
> subroutine to run the user-selected procedure named, oh so cleverly,
> runselectedprocedure.
> The call runselectedprocedure line coded in the double-click event of a list
> box bombs, saying it can't find the subroutine (sub or fuction not defined).
> Where do I put the code for the subroutine ( I don't want to put it on the
> form, because I will be having multiple forms needing to access the same
> subroutine)
>
> I have tried putting the subroutine both under "sheet1" and "thisworkbook"
> sections under the VBA Project Explorer. I have tried Public Sub and Private
> Sub --neither works.
>
> Second,
> I open the user form with a .show command under the workbook_open event.
> It doesn't look like I want it to. For instance, I have tried both a
> docmd.maximize and docmd.minimize line under the userform_open event. The
> form looks the same regardless of whether it is minimized or maximized. Takes
> up most of the screen but not all. Any suggestions?
>
> I would appreciate any help anyone can give.
>
>
>
>
>

You may need to reference the subroutine in this way:

Call Module1.runselectedprocedure (as long as you put it in Module1)

Do not set it up as a private sub or this probably won't work.

JK

in-over-his-head-bill wrote:
> I am writing a simple front-end for a sql server database out of Excel. I
> have done something similar out of Access, but I am constrained to Excel this
> time around and the slight differences between the two in approach is causing
> me problem.
>
> Two problems:
> I have built a user form which alllows the user to select a report (the name
> of a sql stored procedure) that he wants to run. I have written a generic vba
> subroutine to run the user-selected procedure named, oh so cleverly,
> runselectedprocedure.
> The call runselectedprocedure line coded in the double-click event of a list
> box bombs, saying it can't find the subroutine (sub or fuction not defined).
> Where do I put the code for the subroutine ( I don't want to put it on the
> form, because I will be having multiple forms needing to access the same
> subroutine)
>
> I have tried putting the subroutine both under "sheet1" and "thisworkbook"
> sections under the VBA Project Explorer. I have tried Public Sub and Private
> Sub --neither works.
>
> Second,
> I open the user form with a .show command under the workbook_open event.
> It doesn't look like I want it to. For instance, I have tried both a
> docmd.maximize and docmd.minimize line under the userform_open event. The
> form looks the same regardless of whether it is minimized or maximized. Takes
> up most of the screen but not all. Any suggestions?
>
> I would appreciate any help anyone can give.

Thanks -- as long as I make them public routines, it works just fine.


"Tom Ogilvy" wrote:

> put it in a general module
>
> In the vbe, do Insert=>Module
>
> See Stephen Bullens site and down load formfun.zip and oncompress it to see
> how you can manipulate a userform.
>
>
> http://www.oaltd.co.uk/MVP/Default.htm
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "in-over-his-head-bill" wrote:
>
> > I am writing a simple front-end for a sql server database out of Excel. I
> > have done something similar out of Access, but I am constrained to Excel this
> > time around and the slight differences between the two in approach is causing
> > me problem.
> >
> > Two problems:
> > I have built a user form which alllows the user to select a report (the name
> > of a sql stored procedure) that he wants to run. I have written a generic vba
> > subroutine to run the user-selected procedure named, oh so cleverly,
> > runselectedprocedure.
> > The call runselectedprocedure line coded in the double-click event of a list
> > box bombs, saying it can't find the subroutine (sub or fuction not defined).
> > Where do I put the code for the subroutine ( I don't want to put it on the
> > form, because I will be having multiple forms needing to access the same
> > subroutine)
> >
> > I have tried putting the subroutine both under "sheet1" and "thisworkbook"
> > sections under the VBA Project Explorer. I have tried Public Sub and Private
> > Sub --neither works.
> >
> > Second,
> > I open the user form with a .show command under the workbook_open event.
> > It doesn't look like I want it to. For instance, I have tried both a
> > docmd.maximize and docmd.minimize line under the userform_open event. The
> > form looks the same regardless of whether it is minimized or maximized. Takes
> > up most of the screen but not all. Any suggestions?
> >
> > I would appreciate any help anyone can give.
> >
> >
> >
> >
> >