Software

Ask something
Discussion Locked

Help needed creating a program

0 helpful
5 replies

by stephendenny

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi
I am a newbie.

I have a task at work to create a "program" using excel.

What is required:

I need it so when the program is opened it opens in excel but the dialog worksheet.

I require it to allow user to input information into various fields (for example the tasks they have performed) and then they can submit it by pressing OK.
This will then confirm the message and then place this into the various fields within the main excel worksheet (not viewable by the users).

I understand this is difficult and may take a lot of coding but can anyone steer me in the right direction.

For information I am using MS Excel 2003.

Regards

Steve

5 replies

by locrian_lyric

you would want to create the form in the Visual Basic editor, and on the Sub Auto Open, call the form.

Create a series of text boxes, on on the click event of your submit button, send the values to the requried fields through "CELLS"

I.E.

Cells(1,1,) = txtbox1.text

let me know if you need more specifics.

by stephendenny

Yeh that does make sence. The form will consist of (I will use the name "product" as an example):
1) drop down box (smiple) containing a list of products
2) the second field will call the 3 products relating to the choice made in the first drop down box (as each product has different ones) and this will again be in a drop down box.
3) the 3rd will just be a order date number field
4) will be a title
5) will be a text box for information of order to be inputted
6) the last box will be a name field for the person who has processed it.

There will then have a submit button that will put the answers under the product and sub product selected in 1 & 2 above within the orignal worksheet.

Does this make sence and all this needs to be opened as a dialog so users cannot see excel only the form.

The last thing is there will be many people inputting products so they need to go onto a new line after each one has been entered.

Any help will be greatly appreciated.

by locrian_lyric

Good ideas. Excel allows you to populate a drop down box from the spreadsheet.

to get the first free line, just run a simple macro like this

public sub find_newline()
dim i as int
i =1
do while cells(i,1) <>""
i = i+1
loop
'at this point you can either pass the value
'to your form or to a cell.

form1.txtbox_count.text = i
cells(999,999) = i

end sub

either way, you've stored the value of the first free line, which you can later invoke.

Oh, and to hide the excel spreadsheet, just set it to hidden in the vb editor.

by stephendenny

Prehaps its just me tonight,
Do you no of any good step by step help sites in relation to this.

Anything that will be able to guide me through the coding and functions needed to complete this.

Thanks again for all the help.
Regards

This discussion is closed to new replies.