Hi,

Using Excel 2003 here. I've created a form but have a couple questions on
how to implement them. I made a subroutine that calls the form with a show
statement. What I'd like to see happen is when the user clicks a botton on
the form it runs another routine to perform some actions. I have this other
routine working now but I noticed that by default it is public. My concern
with this is that someone may just go to Tools | Macros and run the routine
without running the form. I use the form to force the user into setting a
number of variables needed for the routine. It also seems as though the
called routine doesn't complete until the form is closed. It's performing a
remote query of a SQL server.

My questions are then:
1.) How can I get the remote query to complete without releasing the form?

2.) How can I return a value from the form to the calling program? I'd like
to make the second subroutine private but if I do I can't run it from the
form itself. So I'd like to return a value to the calling subroutine and
continue exicution from there.

Hope this makes sense and thanks in advance for your help.
Linn

On question 2, if the code module is in the same project as the form, which
I assume that it is, add

Option Private Module

at the head of the module, and keep the subs public.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Linn Kubler" wrote in message
...
Hi,

Using Excel 2003 here. I've created a form but have a couple questions
on
how to implement them. I made a subroutine that calls the form with a
show
statement. What I'd like to see happen is when the user clicks a botton
on
the form it runs another routine to perform some actions. I have this
other
routine working now but I noticed that by default it is public. My
concern
with this is that someone may just go to Tools | Macros and run the
routine
without running the form. I use the form to force the user into setting a
number of variables needed for the routine. It also seems as though the
called routine doesn't complete until the form is closed. It's performing
a
remote query of a SQL server.

My questions are then:
1.) How can I get the remote query to complete without releasing the form?

2.) How can I return a value from the form to the calling program? I'd
like
to make the second subroutine private but if I do I can't run it from the
form itself. So I'd like to return a value to the calling subroutine and
continue exicution from there.

Hope this makes sense and thanks in advance for your help.
Linn

Ah, Ok I did see that option but didn't realize what it would do for me,
thanks.

I would also like to run my form when they open the workbook but I can't
seem to figure out how to do that either off hand. I'm guessing it's
relatively simple though. I found the workbookactivate event but I need to
investigate it further. If there is an easier way I'm all ears.

Thanks,
Linn

"Bob Phillips" wrote in message
...
On question 2, if the code module is in the same project as the form,
which
I assume that it is, add

Option Private Module

at the head of the module, and keep the subs public.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Linn Kubler" wrote in message
...
Hi,

Using Excel 2003 here. I've created a form but have a couple questions
on
how to implement them. I made a subroutine that calls the form with a
show
statement. What I'd like to see happen is when the user clicks a botton
on
the form it runs another routine to perform some actions. I have this
other
routine working now but I noticed that by default it is public. My
concern
with this is that someone may just go to Tools | Macros and run the
routine
without running the form. I use the form to force the user into setting
a
number of variables needed for the routine. It also seems as though the
called routine doesn't complete until the form is closed. It's
performing
a
remote query of a SQL server.

My questions are then:
1.) How can I get the remote query to complete without releasing the
form?

2.) How can I return a value from the form to the calling program? I'd
like
to make the second subroutine private but if I do I can't run it from the
form itself. So I'd like to return a value to the calling subroutine and
continue exicution from there.

Hope this makes sense and thanks in advance for your help.
Linn

Private Sub Workbook_Open()
myform.Show
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Linn Kubler" wrote in message
...
Ah, Ok I did see that option but didn't realize what it would do for me,
thanks.

I would also like to run my form when they open the workbook but I can't
seem to figure out how to do that either off hand. I'm guessing it's
relatively simple though. I found the workbookactivate event but I need
to
investigate it further. If there is an easier way I'm all ears.

Thanks,
Linn

"Bob Phillips" wrote in message
...
On question 2, if the code module is in the same project as the form,
which
I assume that it is, add

Option Private Module

at the head of the module, and keep the subs public.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Linn Kubler" wrote in message
...
Hi,

Using Excel 2003 here. I've created a form but have a couple
questions
on
how to implement them. I made a subroutine that calls the form with a
show
statement. What I'd like to see happen is when the user clicks a
botton
on
the form it runs another routine to perform some actions. I have this
other
routine working now but I noticed that by default it is public. My
concern
with this is that someone may just go to Tools | Macros and run the
routine
without running the form. I use the form to force the user into
setting
a
number of variables needed for the routine. It also seems as though
the
called routine doesn't complete until the form is closed. It's
performing
a
remote query of a SQL server.

My questions are then:
1.) How can I get the remote query to complete without releasing the
form?

2.) How can I return a value from the form to the calling program? I'd
like
to make the second subroutine private but if I do I can't run it from
the
form itself. So I'd like to return a value to the calling subroutine
and
continue exicution from there.

Hope this makes sense and thanks in advance for your help.
Linn

Yeah, that worked.
Thanks,
Bob

"Bob Phillips" wrote in message
...
Private Sub Workbook_Open()
myform.Show
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Linn Kubler" wrote in message
...
Ah, Ok I did see that option but didn't realize what it would do for me,
thanks.

I would also like to run my form when they open the workbook but I can't
seem to figure out how to do that either off hand. I'm guessing it's
relatively simple though. I found the workbookactivate event but I need
to
investigate it further. If there is an easier way I'm all ears.

Thanks,
Linn

"Bob Phillips" wrote in message
...
On question 2, if the code module is in the same project as the form,
which
I assume that it is, add

Option Private Module

at the head of the module, and keep the subs public.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Linn Kubler" wrote in message
...
Hi,

Using Excel 2003 here. I've created a form but have a couple
questions
on
how to implement them. I made a subroutine that calls the form with a
show
statement. What I'd like to see happen is when the user clicks a
botton
on
the form it runs another routine to perform some actions. I have this
other
routine working now but I noticed that by default it is public. My
concern
with this is that someone may just go to Tools | Macros and run the
routine
without running the form. I use the form to force the user into
setting
a
number of variables needed for the routine. It also seems as though
the
called routine doesn't complete until the form is closed. It's
performing
a
remote query of a SQL server.

My questions are then:
1.) How can I get the remote query to complete without releasing the
form?

2.) How can I return a value from the form to the calling program?
I'd
like
to make the second subroutine private but if I do I can't run it from
the
form itself. So I'd like to return a value to the calling subroutine
and
continue exicution from there.

Hope this makes sense and thanks in advance for your help.
Linn