Adding Buttons and/or Front Screen Forms in Excel - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HUKD, you accept our cookie and privacy policy.
Get the HUKD app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

Adding Buttons and/or Front Screen Forms in Excel

Disco Avatar
8y, 6h agoPosted 8 years, 6 hours ago
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
Tags:
Disco Avatar
8y, 6h agoPosted 8 years, 6 hours ago
Options

All Comments

(17) Jump to unreadPost a comment
Comments/page:
#1
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#2
Have you turned on your control toolbox?

Click on the end of a toolbar...add or remove buttons....customise
#3
buttons are on the toolbar called "forms"
#4
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
#5
the 6th one across, it will draw like a text box.
1 Like #6
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
#7
Cantona_7
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?
#8
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.
#9
Cantona_7
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?
#10
Disco
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.
#11
Cantona_7
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
#12
Disco
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:
#13
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
#14
AberBargoed
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.
#15
Disco
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:
#16
AberBargoed
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
1 Like #17
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! http://contextures.com/xlToolbar01.html

Post a Comment

You don't need an account to leave a comment. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!