Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I get error 91 when trying to run Bullen's FormFun
Hello
Thanks to Stephen Bullen for FormFun I am having problems setting it up I inserted CFormChanger Class Module in my project I get run time error 91: Object variable...not set when I run UserForm_Activate Private Sub UserForm_Activate() Dim clsFormChanger As CFormChanger clsFormChanger.ShowMinimizeBtn = True 'Error 91 here clsFormChanger.Sizeable = True clsFormChanger.ShowIcon = False Appreciate any suggestions K |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
I get error 91 when trying to run Bullen's FormFun
You need to Set the clsFormChanger to a new instance of the
CFormChanger class. E.g., Set clsFormChanger = New CFormChanger. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "kurb" wrote in message ... Hello Thanks to Stephen Bullen for FormFun I am having problems setting it up I inserted CFormChanger Class Module in my project I get run time error 91: Object variable...not set when I run UserForm_Activate Private Sub UserForm_Activate() Dim clsFormChanger As CFormChanger clsFormChanger.ShowMinimizeBtn = True 'Error 91 here clsFormChanger.Sizeable = True clsFormChanger.ShowIcon = False Appreciate any suggestions K |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
I get error 91 when trying to run Bullen's FormFun
Thanks for the help
I am still struggling to make this work. All I want is to add a Minimize and Maximize button to my Userform. I have succeeded in doing this, however I have not been able to figure out what changes to make to Sub Userform-Resize. When I click on Minimize I want the size to gp to zero, when I click on Maximize I want it to returns to the original size. Is there an easy way to do this. Thanks K In the example provided by Bullen (see below) I dont need any of the items related to controls not on my Userform:lblMessage, tbMessage, fraStyle.. so I have eliminated all of that.. Private Sub UserForm_Resize() Dim dFrameCols As Double, dFrameRows As Double, dFrameHeight As Double Dim i As Integer, j As Integer 'Standard control gap of 6pts Const dGAP As Integer = 6 'Exit the sub if we've been minimized If Me.InsideWidth = 0 Then Exit Sub 'Set controls that don't move/size With lblMessage 'The position of the "Message" label .Top = dGAP .Left = dGAP End With With tbMessage 'The position of the message box (the size changes, not the position) .Top = dGAP + lblMessage.Height + dGAP .Left = dGAP End With fraStyle.Left = dGAP 'Don't let the form get less than a certain height - must have at least the message and button If Me.InsideHeight < lblMessage.Height + btnOK.Height + fraStyle.Height + dGAP * 5 Then 'Reset the height, allowing for the form's border (Height - InsideHeight) Me.Height = lblMessage.Height + btnOK.Height + fraStyle.Height + dGAP * 5 + Me.Height - Me.InsideHeight End If 'Don't let the form get less than a certain width - must be as wide as the biggest check box, plus the standard gap If Me.InsideWidth < cbMaximize.Width + fraStyle.Width - fraStyle.InsideWidth + dGAP * 4 Then 'Reset the width, allowing for the form's border (Width - InsideWidth) Me.Width = cbMaximize.Width + fraStyle.Width - fraStyle.InsideWidth + dGAP * 4 End If 'Work out the new dimensions of the frame (as the check boxes move within the frame) With fraStyle dFrameCols = Application.Max(1, (Me.InsideWidth - dGAP * 3 - (.Width - .InsideWidth)) \ (cbMaximize.Width + dGAP)) dFrameRows = .Controls.Count / dFrameCols If dFrameRows < Int(dFrameRows) Then dFrameRows = Int(dFrameRows) + 1 dFrameHeight = dFrameRows * cbMaximize.Height + dGAP + .Height - .InsideHeight End With 'Don't allow the form width to decrease so that there's no room for the checkboxes 'i.e. decreasing the width causes the check boxes to require an extra row, which doesn't fit. If Me.InsideHeight <= btnOK.Height + lblMessage.Height + dFrameHeight + dGAP * 5 Then 'Reset the width, allowing for the form's border (Width - InsideWidth) Me.Width = fraStyle.Width + dGAP * 2 + Me.Width - Me.InsideWidth 'Recalculate the frame's dimensions with the changed form's width With fraStyle dFrameCols = Application.Max(1, (Me.InsideWidth - dGAP * 3 - (.Width - .InsideWidth)) \ (cbMaximize.Width + dGAP)) dFrameRows = .Controls.Count / dFrameCols If dFrameRows < Int(dFrameRows) Then dFrameRows = Int(dFrameRows) + 1 dFrameHeight = dFrameRows * cbMaximize.Height + dGAP + ..Height - .InsideHeight End With End If 'Set the OK button to be in the middle at the bottom With btnOK .Left = (Me.InsideWidth - btnOK.Width) / 2 .Top = Me.InsideHeight - btnOK.Height - dGAP End With 'Sometimes the OK button leaves white lines from its edges, so use a label to clear them With lblBlank .Width = Me.InsideWidth .Top = btnOK.Top - 0.75 End With 'Set the frame to be as wide as the box and move the check boxes in it to fit With fraStyle .Width = Me.InsideWidth - dGAP * 2 .Height = dFrameHeight 'Reposition the controls in the frame, according to their tab order For i = 0 To .Controls.Count - 1 For j = 0 To .Controls.Count - 1 With .Controls(j) If .TabIndex = i Then .Left = (i Mod dFrameCols) * (cbMaximize.Width + dGAP) + dGAP .Top = Int(i / dFrameCols) * cbMaximize.Height + dGAP End If End With Next Next .Top = btnOK.Top - dGAP - .Height End With 'Userform is big enough, so set the message box's height and width to fill it With tbMessage .Width = Me.InsideWidth - dGAP * 2 'Don't allow the height to go negative .Height = Application.Max(0, fraStyle.Top - .Top - dGAP) End With End Sub Chip Pearson wrote: You need to Set the clsFormChanger to a new instance of the CFormChanger class. E.g., Set clsFormChanger = New CFormChanger. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
I get error 91 when trying to run Bullen's FormFun
Hi Kurb,
When I click on Minimize I want the size to gp to zero, when I click on Maximize I want it to returns to the original size. Is there an easy way to do this. Thanks You probably don't want a Maximize button at all, then. When Minimized, the Minimize changes into a 'Restore'. In that case, you won't need any code in the Userform_Resize event either. Regards Stephen Bullen Microsoft MVP - Excel www.oaltd.co.uk |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
I get error 91 when trying to run Bullen's FormFun THANK YOU
Hi Stephen
Thanks so much, I've been wanting this for a long time. K (Now, if I could find a template for mutlipage-Userform, with multi select features, and disappearing/appearing frames ....just dreaming) Stephen Bullen wrote: Hi Kurb, When I click on Minimize I want the size to gp to zero, when I click on Maximize I want it to returns to the original size. Is there an easy way to do this. Thanks You probably don't want a Maximize button at all, then. When Minimized, the Minimize changes into a 'Restore'. In that case, you won't need any code in the Userform_Resize event either. Regards Stephen Bullen Microsoft MVP - Excel www.oaltd.co.uk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! Bullen's Scroll/Zoom chart | Charts and Charting in Excel | |||
How do I get Bullen's FilterCriteria() to work in Excel 2007? | Excel Discussion (Misc queries) | |||
Stephen Bullen FormFun | Excel Programming | |||
Copying a named range / Bullen's PastePicture | Excel Programming | |||
Steve Bullen's form resizer | Excel Programming |