Too many user forms

I have a way to do it and I'll do some of them for you and then get back to you. It would help if you uploaded your current workbook again that contains your and my changes,

Thanks - sounds like a good plan to me.
The most current updated with above changes as well as some forms I have remoove is attached. This is the one I will be working with.

My password for ALL protected sheets is "123memphis".

BTW, when I do a search from the "Menu" tab, select a sheet and close I get an error, but only when the worksheet is protected. I'll post that as a question later though. Just a heads up.
Excel-Formulas-and-Functions.xlsm

Thanks. Give me an hour or so.

Xmas starts here at 7 CST so no rush.
I made Santa's list - but no one has told me if I was naughty or nice. Good luck!

The only "vote" that counts is your wife's:)

I've run into into a problem. I need to add a new sheet and seeing as though you have a Sheet1 that apparently isn't being used I tried to rename it (3 times) and it crashes Excel. Once I recover I'll go the new sheet route.

Adding and renaming sheets does crash this workbook and I haven't figured out why. So I open a new workbook,  rename the sheet and copy it to the workbook. That seems to work.

Leaving the country....by choice I hope :)

You're a good man Marty,
Happy holidays

The wife and I are going on a Peru guided tour including Machu Picchu which with the Great Wall of China which I've seen are numbers 1 and 2 on my bucket list

I'm not at home right now so I can't check. Does my ShowSolution code have a line that actually sets the form"s caption?

In any case I'm glad I could help.

I just checked and I didn't include that needed line.

Public Sub ShowSolution(strCaption As String, strCol As String)
'new
Dim lngRow As Long

frmSolution.txtSolution.Text = ""

For lngRow = 1 To Range("Solution" & strCol).Rows.Count
    frmSolution.txtSolution.Text = frmSolution.txtSolution.Text & Range("Solution" & strCol).Rows(lngRow) & vbCrLf
Next

frmSolution.Caption = strCaption
frmSolution.Show

End Sub

Select allOpen in new window

Add line 11.

This will take me some time to absorb.....

If you have any questions, just ask.

I'm trying to figure out why the workbook crashes when renaming a sheet and in doing so I've run into a problem. I wrote a little macro to unprotect all the sheets and it pointed out that the password for two of the sheets is not 123memphis. Those two sheets are CondFormShadeProject and ORfunctIndicateDiff. Any idea what the password for those two sheets might be?

I found a way to remove those two passwords.

Great - remove those passwords. What were they, if you know? I've been looking for software to identify Excel passwords for xlms files with not much success.
I'm not in the process of seeing how you solution to so many forms works. I believe I understand but will want to try it myself.

OK, Marty...
Since frmSolution will be the "template" to use, many of my current text boxes are smaller or larger than "frmSolution". Is the size of the text box in frmSolution dynamic?
I follow what you've done though (guess it's a "special XMAS gift").

Great - remove those passwords. What were they, if you know?

The code I used to remove the passwords didn't report what they were.

I've been unable so far to determine why the workbook crashes. I use Excel 2010 most of the time but I have 2007 available and converting the workbook to 2007 gets rid of the problem but a bunch of other stuff is also removed so I don't think that's the way to go.

Since frmSolution will be the "template" to use, many of my current text boxes are smaller or larger than "frmSolution". Is the size of the text box in frmSolution dynamic?

No but I added a vertical scrollbar to the textbox. That means that there is a fixed, minimum, height which you can adjust in the IDE if you like but otherwise for long "solutions" the scrollbar will appear.

OK....scroll bars will work. The user has the option to "Print Solution" coded "me.printform". Will all the text be printed or just what is "visible" when the elect to "Print Solution"?

On this workbook crashing, I've reinstalled 2010 on my machine here at home (2007 is what is used at work) and I find this to be a mystery. I've search for possible solutions myself and have not found any solutions. The worksbook also crashes if you move too quickly on clicking tabs.

BTW, Merry Christmas!

OK....scroll bars will work. The user has the option to "Print Solution" coded "me.printform". Will all the text be printed or just what is "visible" when the elect to "Print Solution"?

Oops, only the visible part will appear so before you go any further let me see what I can do.

I like the concept - really will make to worksbook smaller.

Here's a demo project which dynamically calculates the height of the textbox so that all the text is visible without scrolling. It's not perfect but it does a reasonable job. After trying the demo, copy frmSolution into your workbook replacing what's there and do the same for the ShowSolution sub.
Solution.xlsm

Marty,
This looks great!. I looked around the web yesteday also but missed this. I was thinking that if a soltuion was not possible then an alternative would be to simply print out the column needed from the "data tab" of the list.
Enjoy your trip and may you and your family have a safe New Year's eve and safe trip. I truly am thankful for all you have done for me.
Frank

Martin,
Everthing is looking great - will start the transtion today - a lot of work but a much smaller file. Thanks

Thanks, the best to you and your family as well.

Try completely converting one or two of the forms and let me know if you have any questions or problems.

Here's an update to my demo with what are IMO improvements to the printing of the form. I've added code that prints the userform's caption and hides the buttons.
Solution.xlsm

I've followed your instructions on using a single form frmSolution. I started with the first tab labeled Production Per Hour. The form I was using to show the solution is frmSolutionPerHour. I open the Immediate Window and type frmSoultionPerHour.Show and pressed enter.
From there I opened frmSolutionPerHour and copied the contents in the list box and pasted it to Column B.
A new range was added called ProductionPerHour selecting B1:B4, the contents of the list box from the tab labeled SolutionsDisplay.
I closed the range dialog box then changed the code in the commnad button to read ShowSolution "Solution to Calculate Production Per Hour","B".
When I went to check the results I got only the first line, B1, not B1:B4.
I've done this several times so I still must be doing something wrong?

Open my demo project and go to Formulas|Name Manger. Does your 'SolutionB' named range look like mine, particularly the 'Refers to' formula? Do you have a 'SolutionB'?

No, because the range I am using is from B1:B4, where you're using B1. However, your Solution C (C1:C5) looks my Solution B (B1:B4), but I'm only seeing B1.
My solution B1 is labled "ProductionPerHour".
BTW, I haven't looked at you new Solution.xlsm file yet.

Here's my B1, SolutionDisplays

1. Select cells P4:P9.
2. Enter the following formula:
=O4/(N4*24)
3. Press <Ctrl+Enter>

Select allOpen in new window

This is what I see:

The new print routine look really good. I copied the code and placed it in a module. The only change I made to it was adding Unload Me at the very end.

The Name of the Named range should be 'SolutionB' rather than 'ProductionPerHour' because line 10 in the ShowSolution sub shown here takes the letter that you pass (in this case "B") and appends it to "Solution" to get the name of the range which in this case is "SolutionB".

Public Sub ShowSolution(strCaption As String, strCol As String)
Dim lngRow As Long

Const MIN_HEIGHT = 103.5

With frmSolution

    .txtSolution.Text = ""
   
    For lngRow = 1 To Range("Solution" & strCol).Rows.Count
        .txtSolution.Text = .txtSolution.Text & Range("Solution" & strCol).Rows(lngRow) & vbCrLf
    Next
    ' This formula that I found on the web had a multipliere of 18 and
    ' that did a reasonable job of calculating the textbox's height based
    ' on the length of the text, but 16.5 seems better
    .txtSolution.Height = (Int(Len(.txtSolution.Text) / Int(.txtSolution.Width / 4.7)) + 1) * 16.5 '18

    If .txtSolution.Height > MIN_HEIGHT Then
        .cmdClose.Top = .cmdClose.Top + .txtSolution.Height - MIN_HEIGHT
        .cmdPrintAlterSol.Top = .cmdClose.Top
        .Height = .Height + .txtSolution.Height - MIN_HEIGHT
    End If
    .Caption = strCaption
    DoEvents

    .Show
    
End With

End Sub

Select allOpen in new window

It makes sense. I didn't look at all the code and took liberties to give the Name Manager a unique name. No problem. I can have a table that cross references.
BTW. I'm trying to incorporate the new print rountine as a module with no success. Do you mind looking at your code and how I need to do that?

Why do you want to put the print routine in a module? It should be in the code for frmSolution as in my demo.

Because I'm not thinking...you sure are patient with me. I see it now.
I'm having problems still with this (my form did not resize "B"  but I need to work through this and will only ask for help before I kick the cat!

I didn't look at all the code and took liberties to give the Name Manager a unique name. No problem. I can have a table that cross references.

Are you doing that? There's no need but unless you follow my SolutionA, SolutionB, SolutionC scheme the code will need to be changed.

Well, yes I do look at presented code. It's not just you but others and I learn by what has been done for me. Every once in a while I even see a mistake or two. I read comments also.
I just got home and will work on implementing what you have done. The work computer just is such a pain.
I'll keep you posted .
Thanks

Martin,
I've tried...I've tried...and I've tried but no luck.
I started from the beginning and here's what I did.
I copied your "frmSolution" and your module that I named "modSolution" to my working workbook. I then closed your workbook.
I created and formatted a blank worksheet as text, named a tab "SolutionDisplays" then copied it to my workbook.
In the Immediate Window when I entered in frmSolutionPerHour and pressed entered but I could not copy the text. So I opened the frmSolutionPerHour, set the text property Locked to False then copied the text and pasted it in Column A of the worksheet "SolutionDisplays".
From there I went to the Name Manager and created a new name "SolutionA" and selected the range A1:A4 from "SolutionDisplays".
After I unprotected the worksheet "Production Per Hour" I edited the command button to show the Step-By-Step Solution and replaced my code with - -  ShowSolution "Production Per Hour", "A"
I closed the worksheet, re-opened it, clicked on Display Data - Step-By-Step Solution and when my form opened only the last line appeared, a.k.a. A4 from the tab SolutionDisplays.

I love the concept...I, me and myself, the three of us just can't get it to work like you do. I've attached my work so you can see what I've done and what I see.
Excel-Formulas-and-Functions-Rev.xlsm

It doesn't look like it's working but it actually is. When you run the program and frmSolution shows up, place the cursor in the textbox and press the up arrow several times and you'll see that all the text is there. There is also a second problem and that is that the textbox is too short. To fix both, replace the ShowSolution sub with the following code.

Public Sub ShowSolution(strCaption As String, strCol As String)
Dim lngRow As Long
Dim lngHeight As Long

Const MIN_HEIGHT = 103.5

With frmSolution.txtSolution

    .Text = ""
   
    For lngRow = 1 To Range("Solution" & strCol).Rows.Count
        .Text = .Text & Range("Solution" & strCol).Rows(lngRow) & vbCrLf
    Next
    
    ' The following manipulation of AutoSize results in a textbox that's the right height
    ' but narrower than it's original width of 241.5
    .AutoSize = False
    .MultiLine = True
    .Font.Size = 10
    .AutoSize = True
    
    ' To fix that we save the height, turn autosize off, reset the height to
    ' to the saved value, set the width to what it should be and voila!
    lngHeight = .Height
    .AutoSize = False
    .Height = lngHeight
    .Width = 241.5
End With

With frmSolution
    If .Height > MIN_HEIGHT Then
        .cmdClose.Top = .cmdClose.Top + .txtSolution.Height - MIN_HEIGHT
        .cmdPrintAlterSol.Top = .cmdClose.Top
        .Height = .Height + .txtSolution.Height - MIN_HEIGHT
    End If
    
    .txtSolution.SelStart = 1
    
    .Caption = strCaption
    DoEvents

    .Show
    
End With

End Sub

Select allOpen in new window

Lines 15 to 27 are code that I developed that uses AutoSize temporarily to determine how tall the textbox should be and line 37 is the solution to the first row not showing up at the top. It results in the cursor being in the textbox but if you add the following to frmSolution then cmdPrintAfterSol will have the focus when you see the form.

Private Sub UserForm_Activate()
'new
cmdPrintAlterSol.SetFocus
End Sub

Select allOpen in new window