Solved

Too many user forms

Posted on 2013-12-24
39
246 Views
Last Modified: 2013-12-26
Folks,
I have a workbook with many worksheets that need a user form. Many of these forms are identical except the text. I would like to be able to reduce the number of "custom" forms and fill the text with information specific to each worksheet.
0
Comment
Question by:Frank Freese
  • 20
  • 19
39 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39738649
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,
0
 

Author Comment

by:Frank Freese
ID: 39738660
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
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39738668
Thanks. Give me an hour or so.
0
 

Author Comment

by:Frank Freese
ID: 39738670
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!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39738672
The only "vote" that counts is your wife's:)
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39738683
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.
0
 

Author Comment

by:Frank Freese
ID: 39738688
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.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 39738754
Here's a partial solution and an explanation of what I did and what you should do to complete it.

I added new sheet called SolutionDisplays
I selected all the cells on the sheet and formated them as Text
Starting with the first userform (frmAlternativeSolution) type the following in the Immediate Window and press return
frmAlternativeSolution.show
Select and copy the displayed text and paste it into cell A1 on the new sheet. That will result in each of the first cells in column A having a line of text from the userform. Each line will most likely be wider then its cell so you can expand the column if you like, but remember that even though it may not be apparent, the text is contained SOLELY in column A.
Do the same for the rest of the userforms, putting their text in B1, C1, etc. I’ve done frmAverFuelConsumption (in B1) and frmBonusGasConsumption (C1).
It will look messy but remember again that each form’s text is in its own column
Add a named range called SolutionA, SolutionB, etc )I’ve created SolutionA, SolutionB and SolutionC) by selecting from the 1st row to the last row that has text in each column, adding the new named range
I renamed and slightly mofified frmAlternativeSolution to frmSolution and used it to display all the text.
Find the Show commands for each of the userforms and change them like I changed cmdAlterExplan_Click in Sheet246. In the ShowSolution "Alternative Solution", "A" line the first parameter is the form's caption and the second is the column where the data resides.
I added the ShowSolution sub to modVisible but you can move it to any module you feel is more appropriate.
Wen you are satisfied with the results, delete all the old forms like frmBonusGasConsumption that are no longer needed.

I'm going out for the evening. I'm also leaving the country on 1/2 but hopefully we can wrap this up before then
Q-28325315.xlsb
0
 

Author Comment

by:Frank Freese
ID: 39738763
Leaving the country....by choice I hope :)
0
 

Author Closing Comment

by:Frank Freese
ID: 39738765
You're a good man Marty,
Happy holidays
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39738798
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39738858
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

Open in new window


Add line 11.
0
 

Author Comment

by:Frank Freese
ID: 39739157
This will take me some time to absorb.....
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39739186
If you have any questions, just ask.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39739220
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?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39739238
I found a way to remove those two passwords.
0
 

Author Comment

by:Frank Freese
ID: 39739253
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.
0
 

Author Comment

by:Frank Freese
ID: 39739270
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").
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39739310
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Frank Freese
ID: 39739314
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!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39739325
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.
0
 

Author Comment

by:Frank Freese
ID: 39739336
I like the concept - really will make to worksbook smaller.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39739582
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
0
 

Author Comment

by:Frank Freese
ID: 39739946
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
0
 

Author Comment

by:Frank Freese
ID: 39740074
Martin,
Everthing is looking great - will start the transtion today - a lot of work but a much smaller file. Thanks
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39740267
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39740322
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
0
 

Author Comment

by:Frank Freese
ID: 39740328
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?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39740343
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'?
0
 

Author Comment

by:Frank Freese
ID: 39740417
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>

Open in new window

RangeEditRange
This is what I see:Solution
0
 

Author Comment

by:Frank Freese
ID: 39740462
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.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39740484
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

Open in new window

0
 

Author Comment

by:Frank Freese
ID: 39740526
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?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39740550
Why do you want to put the print routine in a module? It should be in the code for frmSolution as in my demo.
0
 

Author Comment

by:Frank Freese
ID: 39740596
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!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39740757
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.
0
 

Author Comment

by:Frank Freese
ID: 39740851
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
0
 

Author Comment

by:Frank Freese
ID: 39741067
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
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39741122
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

Open 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

Open in new window

0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

746 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

9 Experts available now in Live!

Get 1:1 Help Now