PLEASE HELP ignorant student!

LMF · Jan 14th, 2002, 03:36 PM

can anyone read this reply?

how come my post is gone???

Ian Mac · Jan 14th, 2002, 04:31 PM

Where did this post go????

Jeanie · Apr 14th, 2002, 09:42 AM

I am about to scream in frustration about a question I am sure someone here will find laughably easy! Please help anyway!
I want to create a form out of a worksheet with lots of pretty buttons etc that adds the data I enter onto another sheet within the same file. I know Access handles this alot more efficiently, but Excel is the required program for this assignment. I have tried the template wizard, but firstly it created a new file and secondly I had no idea what I had done!
If anyone can help me avoid insanity, I am waiting!

daleyman · Apr 14th, 2002, 09:53 AM

is a bit of a vague question.

Do you know about the built in data entry form? Accessed under DATA > FORM... you can bring up a form to enter new data onto an existing list, gives you lots of buttons.

If you want to activate it automatically, say from sheet1 to manipulate the data on sheet2, put a button from the forms toolbar onto sheet1 and assign it to the following code (which you will need to add in the Visual Basic Editor in a new module)...

Sub open_dataentry_form()
Sheets("Sheet2").ShowDataForm
End Sub

if you want to do this, go into the vb editor and add a new module, cut & paste this code into it, substituting the name of the sheet where the data list is. Then goto your sheet where you want the button to be, show the forms toolbar, add a button, it will ask you to assign it to a macro, so select the one you just pasted in.

then you can change the button name etc...

Jeanie · Apr 14th, 2002, 10:11 AM

thanks so much- I didn't expect a reply so quickly! Actually I am not allowed to use code in the assignment (can you believe it?)
I did know about the automatic form on the data menu, but wanted to create my own form on a separate sheet and then be able to enter data into it that then was added to a table in another sheet in the same file. I don't know if this is possible, especially without writing code, but if you know then I would be soooo appreciative!
This assignment is only hypothetical hence why I am being vague - sorry!

Yogi Anand · Apr 14th, 2002, 10:38 AM

Hi daleyman:
That's BEAUTIFUL!
With some further tweaking it can be made into a very useful tool.

Jeanie · Apr 14th, 2002, 10:43 AM

I am sure it is very beautiful Yogi but I am still v v stuck!!! Maybe I am asking the impossible to do that without code?

Yogi Anand · Apr 14th, 2002, 10:53 AM

Hi Jeanie:
Let me try walking you through creating a Data Entry Form, say in Sheet1. so here we go...
1. in cell A1 key-in First
2. in cell B1 key-in Last
3. in cell C1 key-in City
4. in cell D1 key-in Phone

Now click in cell A1; then click on DATA|FORM
... you will see a Data Entry Form pop up to facilitate entering First, Last, City and Phone records.
As you enter data in this form for one record and you go to New, the data for the first record will be written in row 2, and then for the next record as you fill in the data entry form into row 3, and so on.
I hope this will get you started!

_________________
Yogi Anand
Edit: Deleted inactive web site reference from signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 18:09 ]

Jeanie · Apr 14th, 2002, 10:58 AM

Thanks - I have done that, but the automatic form that Excel provides is so boring! I wanted to add list boxes, tick boxes etc and then have that info added into my table automatically.(that was why I said I wanted to create a form on a separate 'sheet') Problem is, I have looked on every help topic and now think it is impossible! Any ideas? please?!

Yogi Anand · Apr 14th, 2002, 11:10 AM

Hi Jeanie:
Looks like you are well on your way to learning exciting stuff. It is not clear to me what is your scope -- you said you want to use lots of fancy buttons, check boxes (you called them tick boxes, and then there are others like option buttons, and List boxes, and conbo-boxes, ...
You have two choices:
1) You can create some of these controls right in a worksheet -- for this you will go to VIEW|TOOL_BARS, and check on FORMS ... this will bring up your FORM controls tool-box on the worksheet. Now look at the various controls that are available in the tool-box, and install the controls of interest to you on the worksheet, and then later on you can arrange them to suit your fancy. Working with each of the controls and what type of data is handled by a particular control, and in what fashion will be an evolving process, and then the other approach is ...
2)While in a Worksheet, say Sheet1, click on ALT+F11, and that will take you the VBA (Visual basic for Applications) environment. If you want to create a form in VBA environment, goto INSERT|UserForm, this will bring up a UserForm for you to design. Use the associated tool-box to install various controls on the UserForm, and start having fun
By no means have I given you a complete solution, but this will get you started in exploring the various possibilities!

_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line

[ This Message was edited by: Yogi Anand on 2003-01-19 17:50 ]

Jan 14th, 2002, 04:31 PM	#2
Ian Mac MrExcel MVP Join Date: Feb 2002 Location: Newcastle, UK Posts: 1,174	Where did this post go???? __________________ "Have a good time......all the time" Ian Mac

Apr 14th, 2002, 09:53 AM	#4
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	is a bit of a vague question. Do you know about the built in data entry form? Accessed under DATA > FORM... you can bring up a form to enter new data onto an existing list, gives you lots of buttons. If you want to activate it automatically, say from sheet1 to manipulate the data on sheet2, put a button from the forms toolbar onto sheet1 and assign it to the following code (which you will need to add in the Visual Basic Editor in a new module)... Sub open_dataentry_form() Sheets("Sheet2").ShowDataForm End Sub if you want to do this, go into the vb editor and add a new module, cut & paste this code into it, substituting the name of the sheet where the data list is. Then goto your sheet where you want the button to be, show the forms toolbar, add a button, it will ask you to assign it to a macro, so select the one you just pasted in. then you can change the button name etc... __________________ <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;">*DALEY :P**</td></table>

Apr 14th, 2002, 10:38 AM	#6
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi daleyman: That's BEAUTIFUL! With some further tweaking it can be made into a very useful tool. __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Jan 14th, 2002, 03:36 PM	#1
LMF Board Regular Join Date: Mar 2002 Posts: 73	can anyone read this reply? how come my post is gone???

Apr 14th, 2002, 09:42 AM	#3
Jeanie New Member Join Date: Apr 2002 Posts: 7	I am about to scream in frustration about a question I am sure someone here will find laughably easy! Please help anyway! I want to create a form out of a worksheet with lots of pretty buttons etc that adds the data I enter onto another sheet within the same file. I know Access handles this alot more efficiently, but Excel is the required program for this assignment. I have tried the template wizard, but firstly it created a new file and secondly I had no idea what I had done! If anyone can help me avoid insanity, I am waiting!

Apr 14th, 2002, 10:11 AM	#5
Jeanie New Member Join Date: Apr 2002 Posts: 7	thanks so much- I didn't expect a reply so quickly! Actually I am not allowed to use code in the assignment (can you believe it?) I did know about the automatic form on the data menu, but wanted to create my own form on a separate sheet and then be able to enter data into it that then was added to a table in another sheet in the same file. I don't know if this is possible, especially without writing code, but if you know then I would be soooo appreciative! This assignment is only hypothetical hence why I am being vague - sorry!

Apr 14th, 2002, 10:43 AM	#7
Jeanie New Member Join Date: Apr 2002 Posts: 7	I am sure it is very beautiful Yogi but I am still v v stuck!!! Maybe I am asking the impossible to do that without code?

Apr 14th, 2002, 10:53 AM	#8
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Jeanie: Let me try walking you through creating a Data Entry Form, say in Sheet1. so here we go... 1. in cell A1 key-in First 2. in cell B1 key-in Last 3. in cell C1 key-in City 4. in cell D1 key-in Phone Now click in cell A1; then click on DATA\|FORM ... you will see a Data Entry Form pop up to facilitate entering First, Last, City and Phone records. As you enter data in this form for one record and you go to New, the data for the first record will be written in row 2, and then for the next record as you fill in the data entry form into row 3, and so on. I hope this will get you started! _________________ Yogi Anand Edit: Deleted inactive web site reference from signature line [ This Message was edited by: Yogi Anand on 2003-01-19 18:09 ]

Apr 14th, 2002, 10:58 AM	#9
Jeanie New Member Join Date: Apr 2002 Posts: 7	Thanks - I have done that, but the automatic form that Excel provides is so boring! I wanted to add list boxes, tick boxes etc and then have that info added into my table automatically.(that was why I said I wanted to create a form on a separate 'sheet') Problem is, I have looked on every help topic and now think it is impossible! Any ideas? please?!

Apr 14th, 2002, 11:10 AM	#10
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Jeanie: Looks like you are well on your way to learning exciting stuff. It is not clear to me what is your scope -- you said you want to use lots of fancy buttons, check boxes (you called them tick boxes, and then there are others like option buttons, and List boxes, and conbo-boxes, ... You have two choices: 1) You can create some of these controls right in a worksheet -- for this you will go to VIEW\|TOOL_BARS, and check on FORMS ... this will bring up your FORM controls tool-box on the worksheet. Now look at the various controls that are available in the tool-box, and install the controls of interest to you on the worksheet, and then later on you can arrange them to suit your fancy. Working with each of the controls and what type of data is handled by a particular control, and in what fashion will be an evolving process, and then the other approach is ... 2)While in a Worksheet, say Sheet1, click on ALT+F11, and that will take you the VBA (Visual basic for Applications) environment. If you want to create a form in VBA environment, goto INSERT\|UserForm, this will bring up a UserForm for you to design. Use the associated tool-box to install various controls on the UserForm, and start having fun By no means have I given you a complete solution, but this will get you started in exploring the various possibilities! _________________ Yogi Anand Edit: Deleted inactive web site reference from hard code signature line [ This Message was edited by: Yogi Anand on 2003-01-19 17:50 ]