Adding Buttons and/or Front Screen Forms in Excel

17
Found 10th Dec 2008
I need to add some buttons in Excel to act as a frontscreen rather than clicking each inidividual tab (i have loads)

im guessing this combines with the macros but cant seem to do an actual button.

We're using Office 03/07 if that helps

17 Comments

Not looking at excel at the moment but record your macro/s, then go to your front sheet use draw/ shapes to create your button, colour it in/ give it a name then right click and add the appropriate macro.

Banned

Have you turned on your control toolbox?

Click on the end of a toolbar...add or remove buttons....customise

buttons are on the toolbar called "forms"

Original Poster

who which one of these icons/commands do I use

Bearing in mind, im use Excel 2000 here, but have got '07 at home

http://img242.imageshack.us/img242/6091/toolbarlg0.jpg

the 6th one across, it will draw like a text box.

Make the macro, then create the button using the button icon, then you have to assign the macro to the button.
This should help mrexcel.com/tip…tml

Original Poster

Cantona_7;3717336

Make the macro, then create the button using the button icon, then you … Make the macro, then create the button using the button icon, then you have to assign the macro to the button.This should help http://www.mrexcel.com/tip068.shtml



thats great.. now, I need to create a front sheet/control panel to include upto 300 shortcuts/buttons/whatever, (rather than clicking 300 tabs!)

Whats the best way to go about this? 300 seperate buttons or what? The form will include Street Names / Houses. Some have 1 or 2 houses per street, some upto 40. will i do a macro button to the street, then one to house nmumber, or just 1 straight to the actual house?

hmmm sounds to me like its more a database than a spreadsheet, if so access would be much easier to store this information.
If you had the information in access you could use queries to find your information much quicker, ie "Please enter Street" all houses in that street come up, or "Please enter House Number" followed by "Please enter Street" in order to get information on a specific house.

Original Poster

Cantona_7;3717897

hmmm sounds to me like its more a database than a spreadsheet, if so … hmmm sounds to me like its more a database than a spreadsheet, if so access would be much easier to store this information.If you had the information in access you could use queries to find your information much quicker, ie "Please enter Street" all houses in that street come up, or "Please enter House Number" followed by "Please enter Street" in order to get information on a specific house.



na mate, its excel I need as I'm doing calcuations too. Im basically trying to create a system that will recorded customers paper bills. Im working on the actually on the main payment sheet which is almost complete. I'll then replicate this sheet for 300 customers. but i dont wanna have to click say, tab 296 at the bttm of the worksheet. I thought a front screen would work? Maybe not?

Disco;3718007

na mate, its excel I need as I'm doing calcuations too. Im basically … na mate, its excel I need as I'm doing calcuations too. Im basically trying to create a system that will recorded customers paper bills. Im working on the actually on the main payment sheet which is almost complete. I'll then replicate this sheet for 300 customers. but i dont wanna have to click say, tab 296 at the bttm of the worksheet. I thought a front screen would work? Maybe not?



You'll be suprised by how many calculations you can use with access. Designed several access databases, some of them with very complex calculations involved on different forms. I havent used excel too much so not sure i can help you much with that, but depending on how complicated your calculations are id definately advise looking into using access. If its just total costs,adding VAT,discounts,labour costs etc... then this can be done easily in access and as i said the more complicated calculations can also be done. You can even import excel lists into access if you dont want to re-write all the customers details.

Original Poster

Cantona_7;3718135

You'll be suprised by how many calculations you can use with access. … You'll be suprised by how many calculations you can use with access. Designed several access databases, some of them with very complex calculations involved on different forms. I havent used excel too much so not sure i can help you much with that, but depending on how complicated your calculations are id definately advise looking into using access. If its just total costs,adding VAT,discounts,labour costs etc... then this can be done easily in access and as i said the more complicated calculations can also be done. You can even import excel lists into access if you dont want to re-write all the customers details.



im using VERY complex calcs such as IF, COUNTS etc

Disco;3718255

im using VERY complex calcs such as IF, COUNTS etc



I have used IF calculations in many of the access databases, and while i dont use COUNTS, it can be done. However i dont think im converting you, lol, so good luck with it,hope it goes well :thumbsup:

Maybe create a userform through VBA? I've recently done one where you input the data onto the form, click a button and that data will be added to a database, a new row for each entry Or maybe I am misunderstanding what exactly you are trying to do...:thinking:

Link here - contextures.com/xlU…tml

Original Poster

AberBargoed;3718440

Maybe create a userform through VBA? I've recently done one where you … Maybe create a userform through VBA? I've recently done one where you input the data onto the form, click a button and that data will be added to a database, a new row for each entry Or maybe I am misunderstanding what exactly you are trying to do...:thinking:Link here - http://www.contextures.com/xlUserForm01.html



I think you may be ....... its basically a project i'm doing which incorporates 300 individual worksheets (1 per customer) in a workbook (or 150 in 2). where I will record data each time a customer pays their weekly paper bill (its part is already set up using IFS, conditional formatting etc, so thats OK)

BUT, I dont wanna have to search 300 tabs on the bottom of a spreadsheet until I find the one i need, so thought a front screen would suffice. I know I could create 300 individual macros - 1 for each person and link this to a normal boring cell on the front screen, but thought a nice form would be more arractive.

Disco;3718572

I think you may be ....... its basically a project i'm doing which … I think you may be ....... its basically a project i'm doing which incorporates 300 individual worksheets (1 per customer) in a workbook (or 150 in 2). where I will record data each time a customer pays their weekly paper bill (its part is already set up using IFS, conditional formatting etc, so thats OK)BUT, I dont wanna have to search 300 tabs on the bottom of a spreadsheet until I find the one i need, so thought a front screen would suffice. I know I could create 300 individual macros - 1 for each person and link this to a normal boring cell on the front screen, but thought a nice form would be more arractive.



I can think of a navigating toolbar whereby you can select the appropriate tab via a drop down list? :idea:

Original Poster

AberBargoed;3719188

I can think of a navigating toolbar whereby you can select the … I can think of a navigating toolbar whereby you can select the appropriate tab via a drop down list? :idea:



thats really no good tbh mate. the person using the completed thing aint the brighest of people

Ok no probs - hopefully an Excel MVP can come up with something of use :thumbsup:

The toolbar is incredibly easy to use and just sits wherever you want on the screen - the bonus of this is that the tool can be used in all spreadsheets, not just the one you are currently workin on. I regularly work with spreadsheets with 30 or more tabs and it's most handy - even a novice can use it :-D

For anyone interested, this is about as good a tutorial as you can get & you can even download the file to get you started! contextures.com/xlT…tml
Post a comment
Avatar
@
    Text
    Top Discussions
    1. What should the future now hold for Bob, Grace, Bob Jr, Bona, Chatunga and …33
    2. Without saying how old you are, Name one thing from your childhood someone …66109
    3. Any PS4 News Here29314080
    4. 'Action had to be taken' yeah right. Lillith the lynx shot.22106

    See more discussions