• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

Working with an option button

In the attached workbook I have an option button labeled “Project Closed”. When the user selects this options they need to be ask “You have elected to ‘Close’ this project “xxxxxxx. Are you sure?” (Y/N).  If the user select No then the Project is not closed but changes made are saved. If they select Yes then then Project remains closed and changes made are saved
Consolidated-V2.xlsm
0
Frank Freese
Asked:
Frank Freese
  • 17
  • 15
1 Solution
 
Martin LissRetired ProgrammerCommented:
Frank, my understanding was that for now the option button wasn't going to have any actual functionality for now and that you wanted instead functionality behind the 'New Project' button. Please verify.
0
 
Martin LissRetired ProgrammerCommented:
I actually don't see a need for the option buttons. If the name of the 'New Project' button was changed to 'Close Project' and if after the user clicked that button the project was saved and then closed, would that do?

If you want that then I can have it for you in an hour or so,
0
 
Frank FreeseAuthor Commented:
The user "likes" the two option buttons. It's informational for them and I'm ok with that. I think leaving it as is would be the best course of action.
The "New Project" button would ask the user if they wanted to save the workbook they are in. Regardless of the answer,  the workbook they were in is closed. The user would then have to go to disk and locate the "Consolidated" file to load. If they elect to cancel, Exit Excel. It would function the same as when we append tabs to the Consolidated for Add Years.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Martin LissRetired ProgrammerCommented:
Give this a try. Note that the Grand Totals are incorrect but I'll fix that. Do you want it to say 'New Project' or 'Close Project'?
Consolidated-V3.xlsm
0
 
Frank FreeseAuthor Commented:
Several things
1) When I click on "New Project" it is asking for a Folder Name? I couldn't go any further.
2) We need to check to make sure that the user has assigned a Project a name before saving a Project or adding a New Project. No name - no save.
3. I noticed that when I had three years the third year formulas for January was 0 and February was January, etc.
4. Let's stay with "New Project"
0
 
Martin LissRetired ProgrammerCommented:
1) Why not? It defaults to the place where you have the Excel file, so just click OK.
2) Already being done.
3) May be dues to the formulas not including January which I've corrected, but I'll check tomorrow.
0
 
Frank FreeseAuthor Commented:
Wow! I'm going to have to chew on what you did - haven't seen it done that way before - ever.
Will wait for the corrected version tomorrow.
Welcome back!
0
 
Martin LissRetired ProgrammerCommented:
Do you have an Append file that contains non-zero data?
0
 
Frank FreeseAuthor Commented:
See attached
Append.xlsm
0
 
Martin LissRetired ProgrammerCommented:
I think you attached the wrong wb since there does not appear to be any non-zero data.
0
 
Frank FreeseAuthor Commented:
You did mean the append?
The attached file has zero values but only for the total for each month.
You're not thinking the Consolidated by any chance?
Append.xlsm
0
 
Martin LissRetired ProgrammerCommented:
Yes I do mean an Append file and what I was asking for was one that has non-zero data, so for eaxmple one that looks like the following so I could test the sub- and grand-total formulas.
non-zero dataAs you can see I have one now so no need to attach one again.

I've attached a new Consolidated wb that I believe fixes all known problems.
Consolidated-V3.xlsm
0
 
Frank FreeseAuthor Commented:
I look at it now - had some networking issues to resolve
0
 
Frank FreeseAuthor Commented:
The only problem I've seen is that when a w/b has been save then reopened to add another year when one goes to "New Project" the following occurs of the open project file.
ErrorI haven't tested adding data to years and checking subtotals / grand totals once I ran into the above problem.
What I've seen is awesome - I'd never thought that this could be done. So far a fantastic job!
0
 
Martin LissRetired ProgrammerCommented:
I can't reproduce the problem, probably because I didn't do exactly what you did. The following are the steps I took that did not result in an error. Please list your steps in the same fashion.
1.    Open Consolidated V3
2.    Click 'Add Year'
3.    Click 'New Project'
4.    Click 'Add Year'
5.    Click 'New Project'
0
 
Frank FreeseAuthor Commented:
Interesting - I can't recreate the problem either.
But I did notice something. The user opens Consolidate V3 and enters some Jan 14 data. When they go to create a "New Project" the current project is saved and a new project file is opened with the data from the existing one still? Also, enter some data in any month and then Exit the program. The file name to save is the same as the original. On New Project you have the file named "Consolidated xxxx", which is different then what happens when simply entering data and Exit the workbook. At least that's what I'm getting.
0
 
Martin LissRetired ProgrammerCommented:
Here is an updated workbook. The file saving process was fairly complicated to implement so test it thoroughly via the 'Exit' button, the 'X' workbook button and File|Save. If you find any problems please report them as I did in Post ID: 40237827. In other words
1 What you did first
2 What you did next
etc.

Also please tell me
What happened
What should happen
Consolidated-V3.xlsm
0
 
Frank FreeseAuthor Commented:
Would you mind as I go through this identify each problem as I find them?
For example, when I opened Consolidated-V-3.xlsm and enter data for Jan 14 Billable hours the formula is missing. Or do you want them all at once?
0
 
Martin LissRetired ProgrammerCommented:
I'd prefer more than one at a time unless there's a major problem.

Anyhow, while I was making the recent changes I was thinking about the fact that if you manually enter something in the Consolidated sheet that that would not be reflected in the details sheet and I was going to ask you if we should protect the Consolidated sheet. The problem you just reported makes that pretty much a requirement because if you enter something in a Consolidated cell that has a formula, that formula is replaced by the value, so should be protect that sheet?
0
 
Frank FreeseAuthor Commented:
I'm ahead of you on this. Here's what I have seen so far:
1.      For January 14 no billable hours formula – just 0. I believe you've identified the why - good point!
2.      When clicking on “Exit” and there is no Project Name the Excels “Save, Don’t Save, Cancel” dialog box appears. If there is a Project name the same dialog box appears. The problem here is that “Consolidated-V3” is overwritten regardless. Protecting a workbook with a password really doesn't solve the problem since it is so easy to find the password.
3.      I truly, honestly, would like the "Consolidated" and "Append" workbooks to be template files and let them place those files in a folder where all the Project w/b's are kept. As you mentioned there needs protection on these two files. This may sound weird but after 2003 I haven't figured out where 2010 and above template files are kept. I've never had the need to know until now. I'll do some research on that.
0
 
Frank FreeseAuthor Commented:
Finally found where the template files are located. If you didn't know you have to open Word - File - Option - Advanced then scroll to the bottom to see file locations. I would have though Excel would tell you the same thing. Weird
0
 
Martin LissRetired ProgrammerCommented:
2. If you first rename the file as "Consolidated" it will work differently, and that's the way you should test it because it's what your users will experience.

3. I have no experience with templates so if you really want to use them then someone else will need to do it. Why do you need templates anyhow?
0
 
Frank FreeseAuthor Commented:
3. Templates is not the final solution - the only part of the worksheet not protected would be the where the Project Name goes. All formulas have to be protected. I could handle templates now that I know where they're stored.
2. Understood and I'll test it that way and with using a template -  but will do it tomorrow.
I'll be so glad when this is project is done because I need some help on the Statistics workbook.
I'll need to work through this first part - thanks.
Appreciate you!
0
 
Martin LissRetired ProgrammerCommented:
We can do both, no?
0
 
Frank FreeseAuthor Commented:
Yes we can. I've already created the templates for "Consolidated" and "Append"
Question here?
You've gone far and above the initial objective and I think we've beat this dog to death. I would like to test and submit a questions for each new problem. I suspect we're "very" close to bringing this home. Please advise.
0
 
Martin LissRetired ProgrammerCommented:
Sure.
0
 
Frank FreeseAuthor Commented:
Thanks...new question being developed
0
 
Frank FreeseAuthor Commented:
thanks - great job
0
 
Martin LissRetired ProgrammerCommented:
I feel a little like a drug dealer enticing you to take some more, but in the attached workbook I've locked the data cells (sheet password = ml), but click on any data cell (other than the month and year) and see what happens.

BTW there's a bug. If you manually type in the Project Name it's not propagated to the other sheets.
Consolidated.xlsm
0
 
Frank FreeseAuthor Commented:
I knew of the "bug" and mentioned it to them that they only way to populate the project name across workbooks is using the Project Name button.

In regards to protection - I took care of that before I sent them the final file. Not only did I have to protect all formulas but unprotect the location of the project name on all Consolidated sheets (also corrected the Append w/b also). They didn't like they fact that they can't make their own changes - whatever that means. So, to make them happy I told them to make a copy of either file saving it to the same location. Make whatever changes they make to the copy version they can over write the original file. It is understood that no additional support will be provided. What they did like was that it is only about 3 - 4 steps and that was nice.

I bet you feel like you've been on a roller coaster. Maybe you can get back to some simpler questions? Almost, for I'll be posting one on the Statistics w/b - but I tried to do it myself first. Just to let you know what type of guy I am I'll wait a couple of days (I need it myself).
0
 
Martin LissRetired ProgrammerCommented:
OK but make sure that the workbook that you passed over to them doesn't have "xxx" as the project name on the detail sheets.
0
 
Frank FreeseAuthor Commented:
whoops - but that'll be overwritten when they put in a project name.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 17
  • 15
Tackle projects and never again get stuck behind a technical roadblock.
Join Now