Excel Capabilities w/ Macros

[Nighthawk]

Hello. I am in the process of designing an Excel 2016 workbook where I'm mainly focusing on the UI design and interface, with the intention of hiring an experienced VBA programmer to make it functional once I've laid it all out. I have a few questions about some specific capabilities within Excel and if it's possible to do, which will in turn dictate how I layout the worksheets.

1) I'd like to be able to select a row from a Table 1, for example "Item A", and when this row is highlighted it will pull up additional information in a right hand column linked to "Item A" in Table 2 where I can also input data in a few fields. Likewise when "Item B, C, etc" is selected, the additional info is displayed in Table 2 for those line items.

2) Is it possible to design and layout a calendar in Excel and turn this into a dynamic working calendar?

3) Can you design your own drop downs, spin buttons, etc with macros and shapes? Or even popup forms other than the standard Windows 98 style Ui?

4) I see the option to insert and delete rows for specified columns. Can you also hide and show rows for specified columns or only the entire row?

I guess I'll stop there for now. I appreciate any information. Thanks!

 

[ajetrumpet]

1) sounds to me like the VLOOKUP() function would be good for this stuff.
2) yes. not sure what you mean by:

dynamic working calendar

. are you referring to the same kind of functionality you can find in one of my examples, or perhaps even this one? (both built in ms access).
3) excel has all sorts of interface objects, controls and a lot of other things. depending on the version of excel you have, you should explore the ribbon/menu options at the top and check out the different availabilities. for instance, I have version 2016 and a lot of these things for me are found on the INSERT and DEVELOPER tabs:

4) excel's structure is built on the matrix of rows and columns, so no, you can't do this. you can hide *entire* rows or *entire* columns. unless I'm wrong, as I'm not excel expert.

 

[Nighthawk]

I appreciate the reply! I am using Excel 2016 as well. Unfortunately I can't open mdb files.

2) I'm not sure if I know exactly what I mean either haha. I guess, take the following screenshot as an example. I basically laid out the calendar in Excel, and I want it to display all transactions from various tables, and in this particular is example it would be pulling data from other tables and listing the last 5 transactions.

3) Also in the screenshot I guess you can see what I mean by a custom drop down for example. In this example it's not functional, I just threw a shape up there, but wanted to illustrate what I would want to achieve. I did take a browse through the Insert and Developer tabs and I don't think those can be customized graphically? But perhaps I just don't know how?

4) Ok, yes I did discover a macro to hide and unhide entire rows which works great but was hoping it was possible to hide and unhide partial rows since you can insert and delete them. But I can work around that.

Also on a somewhat different path, once I get the workbook developed and working how I would like, what would it take for a programmer to take this workbook and have them develop it into a standalone desktop application? Would any of the Excel programming translate/crossover to another appropriate language or would you basically be starting from scratch?

For example, using a simple Ui similar as below to navigate worksheets and open tables into the workspace similar to Quickbooks.

 

[ajetrumpet]

well you've got some pretty good questions, bud. here are your answers....

Unfortunately I can't open mdb files.

I assume that's because you purchased the 365 version that most people do, which does not include access? I think that's every package except for one.

I guess, take the following screenshot as an example. I basically laid out the calendar in Excel, and I want it to display all transactions from various tables, and in this particular is example it would be pulling data from other tables and listing the last 5 transactions.

this is quite complicated, and it looks like you've set it up quite well as it is. I would say you should run with it from here and then post on this forum when you get stuck on an issue. calendars can be done in so many ways, to say that any one way is better than another is just an ignorant/uninformed statement. so I won't talk to that.

3) Also in the screenshot I guess you can see what I mean by a custom drop down for example. I did take a browse through the Insert and Developer tabs and I don't think those can be customized graphically? But perhaps I just don't know how?

i didn't see any dropdown of any kind on the screen shot you posted of the calendar. as far as manipulating the ribbon items that are built-in to excel, that's a pretty impractical exercise because excel was created specifically for 2 purposes:

=> allow business people to use it to manage business data efficiently and effectively.
=> allow developers to use the interface objects and VBA code to create custom tools.

in terms of point #2 above, I would seriously doubt that most developers have any desire to modify the objects and tools excel already provides. if you actually want to do that, you'll have to get down low into the windows directories where all of the extensible libraries for excel are found (type libraries, dll's. com's, etc...). and at that point you would basically be getting to a point where you're almost re-inventing the wheel. so to me, that seems like a waste of time.

was hoping it was possible to hide and unhide partial rows since you can insert and delete them.

I don't think so.

what would it take for a programmer to take this workbook and have them develop it into a standalone desktop application? Would any of the Excel programming translate/crossover to another appropriate language or would you basically be starting from scratch?

excel's structure is very common in a lot of other programming languages. you have to remember that the spreadsheet layout, in general, is in existence almost everywhere and on every platform....which is why excel is so compatible with a lot of other applications and platforms. a great example of how excel integrates with other players is the Python language. I believe you can connect to an excel instance and the spreadsheet object in the general by writing simple statements in Python which call resources from its standard library. so in short, turning an excel app into a desktop app is essentially happening as soon as you start modifying an excel file to look like what you wish. a desktop app from there is only a few short steps away, really. (depending on how sophisticated you are)

For example, using a simple Ui similar as below to navigate worksheets and open tables into the workspace similar to Quickbooks.

QB is very compatible with Excel. But whether you can write some kind of "universal" languages the communicates withe the 2 of them, I have no idea. You would have to explore that. I've worked with both, but I never did any intermixing of their resources.[/QUOTE]