Solved

How to use working buttons on excel

Posted on 2013-12-24
22
163 Views
Last Modified: 2014-01-08
I am wanting to create a 'Staff Details' spreadsheet on MS Excel.

I would like a name and next to the name buttons with different headings. When you click on the buttons it brings up informatino such as name, address, telephone number etc.

can someone help!?
0
Comment
Question by:benapplegarth
  • 12
  • 7
  • 3
22 Comments
 
LVL 14

Expert Comment

by:Zack Barresse
Comment Utility
Hi there,

Your question is a little vague. This can be done but we'd need to know all about your data structure, where things are located, etc. This can be done in a message box or a userform, among other options.

The following solution assumes you have buttons setup with the same macro assigned to them, your lookup data is on Sheet2 and in a table named 'tblData', you want to return all columns of information in a message box, the left-most column of lookup data is titled "NAME", and the cell directly left of the button contains the name to lookup.

Sub GetStaffDetails()

    Dim BTN                     As Variant
    Dim loGET                   As ListObject
    Dim LookupCell              As Range
    Dim iCol                    As Long
    Dim iRow                    As Long
    Dim LookupName              As String
    Dim Details                 As String

    On Error Resume Next
    BTN = Application.Caller
    On Error GoTo 0
    If BTN = vbNullString Then Exit Sub
    Set LookupCell = ActiveSheet.Shapes(BTN).TopLeftCell.Offset(0, -1)

    Set loGET = Sheets("Sheet2").ListObjects("tblData")    'set as desired
    Details = "No details found"
    If loGET.DataBodyRange Is Nothing Then GoTo FinishDetails
    LookupName = LookupCell.Value
    iRow = Evaluate("MATCH(" & Chr(34) & LookupName & Chr(34) & "," & loGET.Name & "[NAME],0)")
    If iRow = 0 Then GoTo FinishDetails

    Details = vbNullString
    For iCol = 1 To loGET.ListColumns.Count
        Details = Details & loGET.HeaderRowRange(1, iCol) & ": " & loGET.DataBodyRange(iRow, iCol) & vbNewLine
    Next iCol

FinishDetails:
    MsgBox Details, vbExclamation, "STAFF DETAILS"

End Sub

Open in new window


This is in the attached sample file. Whether you want something like this or not, specify the details and we can get something working for you.

HTH

Regards,
Zack Barresse
EE-Ex.xlsm
0
 

Author Comment

by:benapplegarth
Comment Utility
HI,

Thank you for your help. The example you posted is pretty much what we want but when you click the buttons like you have instead of the box coming up with the details would it be possible for just the normal excel cell boxes come up where you can edit easily? I'm not sure if i've explained that very well. I have attached a map for what ideally where the buttons go. If you click the buttons can something be unhidden but not upset the layout of the rest of the spreadsheet?

Thanks
Ben
EDB.pdf
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Well I have a comment here.

You post a very broad question with no specifics, and no file attached.
An Expert give you more than you can expect provided the broad question
You reply with a snapshot that has nothing to do (or at least not well explained in your original post)

My suggestion at this point:
1) Post the workbook you are referring to so whoever can give you a clear working solution direct to your needs.
2) Why do you have all these buttons ?? I would suggest an other approach (Imagine you have 2000 clients or names you would have 2000 buttons ??? this is an overkill !)
3) what you need can be approached differently but first:

Need you to upload your file and will take it from there.

PS I would recommend following buttons (1 for each):
Personal Details
Employment
Recruitment
Disciplinary / Grievance
Training
Misc

but all this can be seen later on
gowflow
0
 
LVL 14

Expert Comment

by:Zack Barresse
Comment Utility
Gowflow makes some very good points. I do not understand your last attachment and explanations. Another option, instead of all the buttons, would be to make use of the current UI. For example add a command button(s) to the right-click menu, or a button to the ribbon. We'd need to know what version of Excel you're using. I'm assuming 2007 from your partial screenshot?

Zack
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Any news ? did you have a chance to see the comments posted ?
gowflow
0
 

Author Comment

by:benapplegarth
Comment Utility
OK, I have an idea which will avoid all of the above problems people have raised.

However does anybody know if there is away to click on a cell which has a command to hide other cells.

for example:

Cell A:1 says: Personal Details

Cells b:1 and B:2
C:1 and c:2
D:1 and D:2 etc all have information in which is hidden.

If i click on A:1 (personal Details) the hidden cells mentioned above will be unhidden. Or would i have to do the normal highlight, right click, hide/unhide?

Thanks
0
 
LVL 14

Expert Comment

by:Zack Barresse
Comment Utility
Perhaps it would be best if you didn't think about coming up with the solution at all, but rather explained in detail your starting point, with a sample file. Then describe the end result functionality you're looking for. Let us connect the dots and make the solution.

The answer to your question is yes, of course you can do that, but that doesn't mean you should.

Zack
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok here it is let me know if this what your looking for.
For sure we can work around the options.

Make sure macros are activated and select from the first dropdown a name and see the results.

You main database would go in sheet Database and here depending on the combobox we can choose the appropriate fields.

Now it simply copy the whole line but this can be catered.
gowflow
ShowHide.xlsm
0
 

Author Comment

by:benapplegarth
Comment Utility
HI Guys,

Thanks for your ever patient help!

I've had a look at your ideas and others on the internet and had a play with a few ideas with my department to see what we need.

We have come up with the database we want and the only tech support we need now is to know how to hide cells when you click another cell,

For example:

on my attached spreadsheet.

Sheet 1 is how it should look normally (cells hidden) however when you click on the yellow boxes the information in the cells on sheet 2 should unhide and display.

so how can i format a cell to say unhide the follwing cells... when clicked?

Thanks
Ben
example-database.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Before looking at it did you see my post above 39746612 ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Sorry as already commented out you tell us what you want and we suggest a solution. I am very sorry to tell you that the format you showed especially data in sheet1
Employee 1                                                 
Personal Details                                                 
Employment                                                 
Recruitment                                                 
Training                                                 
Employee 2                                                 
Personal Details                                                 
Employment                                                 
Recruitment                                     
...

Is in no way a database format !!!!
The database format should be row1 the fields all across and data from row2 onward

and then vba code could show you the data in the way you want !!!!

Pls advise if you saw my previous code
gowflow
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:benapplegarth
Comment Utility
I think everybody thinks this is more complicated than it needs it to be.

All i want to do is hide and show some writing easier than highlighting it and right clicking and pressing hide or unhide.

ignore what i have said about database's or whatever..
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Well if you get less aggressive and more receptive we can get you what you want. The problem is that you are putting yourself in the driver seat where you only need to be the co-pilot and leave the pilot drives the car.

This being said, I ask again the same question:
Have you seen the file I posted above ? If yes then here is my simple suggestion to you:

I can get you the layout that you want !!! YES can get you the layout same as sheet2 you posted above and this provided the data is layout same as in my file in sheet database will this be a problem for you ?

Based on your reply (I already started developing it) will then continue.
Rgds/gowflow
0
 

Author Comment

by:benapplegarth
Comment Utility
Hi,

I'm not being aggressive. I just don't get excel hence why i'm on this site! I only just learnt how to do COUNTIF

i am like a 2 year old child when it comes to excel.

Can you work out what i am trying to achieve by my spreadsheet?

If you do then you can send me something your developing and i will look at it and it might be more suitable.

But please understand i have a limited knowledge about excel.
Thank you
BEn
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok fine Excellent now we are talking.

I do not understand what do you mean by "I don't get Excel" ??? youo don't have Excel on your pc ?? r you don't know how to program Excel ???

If it is the second case then no problem will be glad to do this for you.

FIRST I need you to please open the file I am now attaching called ShowHide V01.xlsm and activate macros and see if the data in Sheet Database is ok for you to update and if ok then in sheet Main when you select an item in the dropdown it will display the data in sheet main exactly like you want posted in your file in Sheet2 will this be ok for you ???

gowflow
ShowHide-V01.xlsm
0
 

Author Comment

by:benapplegarth
Comment Utility
I have excel 2007 on my computer i'm just not very good at using it.

what you have would work for us! thanks!

ON the sheet 'Main' when you select an employee's name how will it be displayed? easy to read?
Thanks
Ben
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
ok I think you will like that.

Load this file and make sure macros are activated.
Select a name from the dropdown and see what it give you

then
tick on any of the options that you see beside the dropdown and see the results.

You may tick and untick any option and see how it evolves.

Happy New Year
I hope you will like that.

We can modify the layout if you like (better to be focus of a new question not to overcrowed this one)

Let me know your comments anyhow
gowflow
ShowHide-V01.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Tks for accepting the solution but would like to hear your comments anyway.
Rgds/gowflow
0
 

Author Comment

by:benapplegarth
Comment Utility
HI,

This is perfect. Just what we need!

Thank you!

I have re-attached the document and put on 'Sheet 1' the desired lay out and info.

and also just a couple of questions:

how do we input new information or change information? By information i mean the employees name, DOB etc - just off the database sheet?

How do we add new people?

How do we remove ex-employees? and where can we store them so we can easily look back on them?

I noticed on the main sheet when you select a name from the drop down, once you have looked at that person it does not let you look at them again?

Thanks
Ben
ShowHide-V01--4-.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Well glad you liked it.

As for all the other questions:

1) how do we input new information or change information? By information i mean the employees name, DOB etc - just off the database sheet?

>>> Basically yes from the sheet Database but for sure one can be fancy and create a FrontEnd where data is input thru the frontend and all the updates are done by VBA to Database sheet that is hidden. But here you are talking of an entire project and not just a simple question.

2) How do we add new people?

>>> In the database sheet simply add after the last row a new employee. Again if one wants to be fancy a FrontEnd can add help new items

3) How do we remove ex-employees? and where can we store them so we can easily look back on them?

>>> Here again simply delete the row in Database sheet. If one wants to be fancy, again a Front End can be programmed to have the removed items stored in an Archive file for future lookup and this can be in a whole project not a simple question.

4) I noticed on the main sheet when you select a name from the drop down, once you have looked at that person it does not let you look at them again?

>>> ok you are correct this has been corrected in this version.

Conclusion:
I see that you have a need for more than a simple question, but rather a whole project to manage Employees as a whole in their input, deletions, modification and Archive with multiple layouts.

Basically you have 2 avenues to choose from and I will be very frank and clear.
1) You can choose the avenue to stay asking questions here in EE (but here again you need to dissect your questions carefully and divide them in bits and pieces) so you get a reply from Experts as if your question contains too much work, then your are very likely not to receive answer or to go in circles like it was the case in the beginning of this question. Do not take my input as being a regular input from Experts I usually go out of my way sometimes and do more this is my nature.

2) the second avenue is to go and decide to treat this professionally and decide for who ever you like and Experts usually offer paid services and they may be contacted their credentials are posted on their profile. Here in this avenue you get dedicated catered work and ensure you get what you want. For sure this avenue is paying. and the first is free.

Your decision.
Regards
gowflow
ShowHide-V01--4-.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Did you have a chance to check out the above comment ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Any news on your development plans ?
gowflow
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now