Making a form into a file...

HFB · 01-19-2005, 08:06 PM

I'm using visual basic (the one that is part of Excel) to make a form for
people to fill out and print. How do I make it so I can open the form
without having to go Tools>Macros>Visual Basic Editor? (or is this a
function reserved for the complete version of VB?)
thanks

Jim Thomlinson · 01-19-2005, 08:06 PM

Put a command button on a sheet and attach the following code to the button...

form1.show

You can get rid of the for using

unload me

or just hide the form with

form1.hide

HTH

"HFB" wrote:

> I'm using visual basic (the one that is part of Excel) to make a form for
> people to fill out and print. How do I make it so I can open the form
> without having to go Tools>Macros>Visual Basic Editor? (or is this a
> function reserved for the complete version of VB?)
> thanks

HFB · 01-19-2005, 09:06 PM

Um. I can't even figure out how to attach code to a button in a worksheet.
You DO mean a worksheet?

"Jim Thomlinson" wrote:

> Put a command button on a sheet and attach the following code to the button...
>
> form1.show
>
> You can get rid of the for using
>
> unload me
>
> or just hide the form with
>
> form1.hide
>
>
> HTH
>
> "HFB" wrote:
>
> > I'm using visual basic (the one that is part of Excel) to make a form for
> > people to fill out and print. How do I make it so I can open the form
> > without having to go Tools>Macros>Visual Basic Editor? (or is this a
> > function reserved for the complete version of VB?)
> > thanks

Jim Thomlinson · 01-19-2005, 09:06 PM

Ok... Here we go... In Excel right click on the toolbar at the top of the
page. You will get a list of Items with Check marks beside some of them...
Check "Control Toolbox"
and select OK. You will now have the Control Toolbox Toolbar. One of the
item on that toolbar looks like a Grey Button. Click on it. Your Cursor will
change to a Cross Hair. Now you need to select a spot on a work sheet and
click... Tada... You have now created a Command Button. Right click on the
button and select Properties. Change (Name) to cmdShowForm. Change Caption to
Show Form. Now right click on the button and Select View Code. Paste my code
into the code window.

Should look like this...

Private Sub cmdShowForm_Click()
Form1.Show
End Sub

Select the spread sheet again. On the Control Toolbax there is a button that
looks like a ruler triangle and pencil. Click it to exit the design mode. You
now have a button that will show the form... I hope...

HTH

"HFB" wrote:

> Um. I can't even figure out how to attach code to a button in a worksheet.
> You DO mean a worksheet?
>
> "Jim Thomlinson" wrote:
>
> > Put a command button on a sheet and attach the following code to the button...
> >
> > form1.show
> >
> > You can get rid of the for using
> >
> > unload me
> >
> > or just hide the form with
> >
> > form1.hide
> >
> >
> > HTH
> >
> > "HFB" wrote:
> >
> > > I'm using visual basic (the one that is part of Excel) to make a form for
> > > people to fill out and print. How do I make it so I can open the form
> > > without having to go Tools>Macros>Visual Basic Editor? (or is this a
> > > function reserved for the complete version of VB?)
> > > thanks

HFB · 01-19-2005, 09:06 PM

Aha! I was trying to use a form command button rather than a control
toolboxy one

Thanks heaps Jim

"Jim Thomlinson" wrote:

> Ok... Here we go... In Excel right click on the toolbar at the top of the
> page. You will get a list of Items with Check marks beside some of them...
> Check "Control Toolbox"
> and select OK. You will now have the Control Toolbox Toolbar. One of the
> item on that toolbar looks like a Grey Button. Click on it. Your Cursor will
> change to a Cross Hair. Now you need to select a spot on a work sheet and
> click... Tada... You have now created a Command Button. Right click on the
> button and select Properties. Change (Name) to cmdShowForm. Change Caption to
> Show Form. Now right click on the button and Select View Code. Paste my code
> into the code window.
>
> Should look like this...
>
> Private Sub cmdShowForm_Click()
> Form1.Show
> End Sub
>
> Select the spread sheet again. On the Control Toolbax there is a button that
> looks like a ruler triangle and pencil. Click it to exit the design mode. You
> now have a button that will show the form... I hope...
>
> HTH
>
>
>
> "HFB" wrote:
>
> > Um. I can't even figure out how to attach code to a button in a worksheet.
> > You DO mean a worksheet?
> >
> > "Jim Thomlinson" wrote:
> >
> > > Put a command button on a sheet and attach the following code to the button...
> > >
> > > form1.show
> > >
> > > You can get rid of the for using
> > >
> > > unload me
> > >
> > > or just hide the form with
> > >
> > > form1.hide
> > >
> > >
> > > HTH
> > >
> > > "HFB" wrote:
> > >
> > > > I'm using visual basic (the one that is part of Excel) to make a form for
> > > > people to fill out and print. How do I make it so I can open the form
> > > > without having to go Tools>Macros>Visual Basic Editor? (or is this a
> > > > function reserved for the complete version of VB?)
> > > > thanks

Jim Thomlinson · 01-19-2005, 10:06 PM

You are now ahead of a good chunk of Excel users in that you know that there
are 2 kinds of buttons...

Forms Toolbar buttons are good when you need a button that you can copy and
it will stay attached to its code. It attaches to a Macro. If you recorded a
macro in VB you would get a module. You can attach this kind of button to any
public sub in a module.

The Control Toolbox button is different in that it is embeded right in the
sheet. The code that is written for it is part of the sheet. This kind of
button does not copy well as it will not stay attached to it's code.

One button is not better htan the other. Depending on the situation they
both have merit.

HTH

"HFB" wrote:

> Aha! I was trying to use a form command button rather than a control
> toolboxy one

Thanks heaps Jim
>
> "Jim Thomlinson" wrote:
>
> > Ok... Here we go... In Excel right click on the toolbar at the top of the
> > page. You will get a list of Items with Check marks beside some of them...
> > Check "Control Toolbox"
> > and select OK. You will now have the Control Toolbox Toolbar. One of the
> > item on that toolbar looks like a Grey Button. Click on it. Your Cursor will
> > change to a Cross Hair. Now you need to select a spot on a work sheet and
> > click... Tada... You have now created a Command Button. Right click on the
> > button and select Properties. Change (Name) to cmdShowForm. Change Caption to
> > Show Form. Now right click on the button and Select View Code. Paste my code
> > into the code window.
> >
> > Should look like this...
> >
> > Private Sub cmdShowForm_Click()
> > Form1.Show
> > End Sub
> >
> > Select the spread sheet again. On the Control Toolbax there is a button that
> > looks like a ruler triangle and pencil. Click it to exit the design mode. You
> > now have a button that will show the form... I hope...
> >
> > HTH
> >
> >
> >
> > "HFB" wrote:
> >
> > > Um. I can't even figure out how to attach code to a button in a worksheet.
> > > You DO mean a worksheet?
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > Put a command button on a sheet and attach the following code to the button...
> > > >
> > > > form1.show
> > > >
> > > > You can get rid of the for using
> > > >
> > > > unload me
> > > >
> > > > or just hide the form with
> > > >
> > > > form1.hide
> > > >
> > > >
> > > > HTH
> > > >
> > > > "HFB" wrote:
> > > >
> > > > > I'm using visual basic (the one that is part of Excel) to make a form for
> > > > > people to fill out and print. How do I make it so I can open the form
> > > > > without having to go Tools>Macros>Visual Basic Editor? (or is this a
> > > > > function reserved for the complete version of VB?)
> > > > > thanks

HFB · 01-19-2005, 10:06 PM

Ooh, nice for the ego

I think I'll stick with the Control Toolbox ones -
may be more work in the long run, but I'm more comfortable with actually
writing code rather than recording macros.... Either way, it works now which
is cool.

"Jim Thomlinson" wrote:

> You are now ahead of a good chunk of Excel users in that you know that there
> are 2 kinds of buttons...
>
> Forms Toolbar buttons are good when you need a button that you can copy and
> it will stay attached to its code. It attaches to a Macro. If you recorded a
> macro in VB you would get a module. You can attach this kind of button to any
> public sub in a module.
>
> The Control Toolbox button is different in that it is embeded right in the
> sheet. The code that is written for it is part of the sheet. This kind of
> button does not copy well as it will not stay attached to it's code.
>
> One button is not better htan the other. Depending on the situation they
> both have merit.
>
> HTH
>
> "HFB" wrote:
>
> > Aha! I was trying to use a form command button rather than a control
> > toolboxy one

Thanks heaps Jim
> >
> > "Jim Thomlinson" wrote:
> >
> > > Ok... Here we go... In Excel right click on the toolbar at the top of the
> > > page. You will get a list of Items with Check marks beside some of them...
> > > Check "Control Toolbox"
> > > and select OK. You will now have the Control Toolbox Toolbar. One of the
> > > item on that toolbar looks like a Grey Button. Click on it. Your Cursor will
> > > change to a Cross Hair. Now you need to select a spot on a work sheet and
> > > click... Tada... You have now created a Command Button. Right click on the
> > > button and select Properties. Change (Name) to cmdShowForm. Change Caption to
> > > Show Form. Now right click on the button and Select View Code. Paste my code
> > > into the code window.
> > >
> > > Should look like this...
> > >
> > > Private Sub cmdShowForm_Click()
> > > Form1.Show
> > > End Sub
> > >
> > > Select the spread sheet again. On the Control Toolbax there is a button that
> > > looks like a ruler triangle and pencil. Click it to exit the design mode. You
> > > now have a button that will show the form... I hope...
> > >
> > > HTH
> > >
> > >
> > >
> > > "HFB" wrote:
> > >
> > > > Um. I can't even figure out how to attach code to a button in a worksheet.
> > > > You DO mean a worksheet?
> > > >
> > > > "Jim Thomlinson" wrote:
> > > >
> > > > > Put a command button on a sheet and attach the following code to the button...
> > > > >
> > > > > form1.show
> > > > >
> > > > > You can get rid of the for using
> > > > >
> > > > > unload me
> > > > >
> > > > > or just hide the form with
> > > > >
> > > > > form1.hide
> > > > >
> > > > >
> > > > > HTH
> > > > >
> > > > > "HFB" wrote:
> > > > >
> > > > > > I'm using visual basic (the one that is part of Excel) to make a form for
> > > > > > people to fill out and print. How do I make it so I can open the form
> > > > > > without having to go Tools>Macros>Visual Basic Editor? (or is this a
> > > > > > function reserved for the complete version of VB?)
> > > > > > thanks