Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimizing a user form
Hi,
I am creating a userform via run-time using the following code. How do I change the showmodal property to "false" in this case. I know to change the property if the userform is created in design mode. Option Explicit 'Passed back to the function from the UserForm Public GETOPTION_RET_VAL As Variant Function GetOption(OpArray, Default, Title) Dim TempForm 'As VBComponent Dim NewOptionButton As Msforms.OptionButton Dim NewCommandButton1 As Msforms.CommandButton Dim NewCommandButton2 As Msforms.CommandButton Dim TextLocation As Integer Dim X As Integer, i As Integer, TopPos As Integer Dim MaxWidth As Long Dim WasVisible As Boolean ' Hide VBE window to prevent screen flashing Application.VBE.MainWindow.Visible = False ' Create the UserForm Set TempForm = ThisWorkbook.VBProject.VBComponents.Add (3) TempForm.Properties("Width") = 800 ' Add the OptionButtons TopPos = 4 MaxWidth = 0 'Stores width of widest OptionButton For i = LBound(OpArray) To UBound(OpArray) Set NewOptionButton = TempForm.Designer.Controls.Add("forms.OptionButton .1") With NewOptionButton .Width = 800 .Caption = OpArray(i) .Height = 15 .Left = 8 .Top = TopPos .Tag = i .AutoSize = True If Default = i Then .Value = True If .Width MaxWidth Then MaxWidth = .Width End With TopPos = TopPos + 15 Next i ' Add the Cancel button Set NewCommandButton1 = TempForm.Designer.Controls.Add ("forms.CommandButton.1") With NewCommandButton1 .Caption = "Cancel" .Height = 18 .Width = 44 .Left = MaxWidth + 12 .Top = 6 End With ' Add the OK button Set NewCommandButton2 = TempForm.Designer.Controls.Add ("forms.CommandButton.1") With NewCommandButton2 .Caption = "OK" .Height = 18 .Width = 44 .Left = MaxWidth + 12 .Top = 28 End With ' Add event-hander subs for the CommandButtons With TempForm.CodeModule X = .CountOfLines .InsertLines X + 1, "Sub CommandButton1_Click()" .InsertLines X + 2, " GETOPTION_RET_VAL=False" .InsertLines X + 3, " Unload Me" .InsertLines X + 4, "End Sub" .InsertLines X + 5, "Sub CommandButton2_Click()" .InsertLines X + 6, " Dim ctl" .InsertLines X + 7, " GETOPTION_RET_VAL = False" .InsertLines X + 8, " For Each ctl In Me.Controls" .InsertLines X + 9, " If ctl.Tag < """" Then If ctl Then GETOPTION_RET_VAL = ctl.Tag" .InsertLines X + 10, " Next ctl" .InsertLines X + 11, " Unload Me" .InsertLines X + 12, "End Sub" End With ' Adjust the form With TempForm .Properties("Caption") = Title .Properties("Width") = NewCommandButton1.Left + NewCommandButton1.Width + 10 If .Properties("Width") < 160 Then .Properties("Width") = 160 NewCommandButton1.Left = 106 NewCommandButton2.Left = 106 End If .Properties("Height") = TopPos + 24 End With ' Show the form VBA.UserForms.Add(TempForm.Name).Show ' Delete the form ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm ' Pass the selected option back to the calling procedure GetOption = GETOPTION_RET_VAL End Function Thanks, Sudhakar |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimizing a user form
Hi Sudhakar
Usually like this: Userform1.Show vbModeless hope it adapts to your code, have not tested it in that context, sorry. -- HTH. Best wishes Harald Excel MVP Followup to newsgroup only please. "Sudhakar" wrote in message ... Hi, I am creating a userform via run-time using the following code. How do I change the showmodal property to "false" in this case. I know to change the property if the userform is created in design mode. Option Explicit 'Passed back to the function from the UserForm Public GETOPTION_RET_VAL As Variant Function GetOption(OpArray, Default, Title) Dim TempForm 'As VBComponent Dim NewOptionButton As Msforms.OptionButton Dim NewCommandButton1 As Msforms.CommandButton Dim NewCommandButton2 As Msforms.CommandButton Dim TextLocation As Integer Dim X As Integer, i As Integer, TopPos As Integer Dim MaxWidth As Long Dim WasVisible As Boolean ' Hide VBE window to prevent screen flashing Application.VBE.MainWindow.Visible = False ' Create the UserForm Set TempForm = ThisWorkbook.VBProject.VBComponents.Add (3) TempForm.Properties("Width") = 800 ' Add the OptionButtons TopPos = 4 MaxWidth = 0 'Stores width of widest OptionButton For i = LBound(OpArray) To UBound(OpArray) Set NewOptionButton = TempForm.Designer.Controls.Add("forms.OptionButton .1") With NewOptionButton .Width = 800 .Caption = OpArray(i) .Height = 15 .Left = 8 .Top = TopPos .Tag = i .AutoSize = True If Default = i Then .Value = True If .Width MaxWidth Then MaxWidth = .Width End With TopPos = TopPos + 15 Next i ' Add the Cancel button Set NewCommandButton1 = TempForm.Designer.Controls.Add ("forms.CommandButton.1") With NewCommandButton1 .Caption = "Cancel" .Height = 18 .Width = 44 .Left = MaxWidth + 12 .Top = 6 End With ' Add the OK button Set NewCommandButton2 = TempForm.Designer.Controls.Add ("forms.CommandButton.1") With NewCommandButton2 .Caption = "OK" .Height = 18 .Width = 44 .Left = MaxWidth + 12 .Top = 28 End With ' Add event-hander subs for the CommandButtons With TempForm.CodeModule X = .CountOfLines .InsertLines X + 1, "Sub CommandButton1_Click()" .InsertLines X + 2, " GETOPTION_RET_VAL=False" .InsertLines X + 3, " Unload Me" .InsertLines X + 4, "End Sub" .InsertLines X + 5, "Sub CommandButton2_Click()" .InsertLines X + 6, " Dim ctl" .InsertLines X + 7, " GETOPTION_RET_VAL = False" .InsertLines X + 8, " For Each ctl In Me.Controls" .InsertLines X + 9, " If ctl.Tag < """" Then If ctl Then GETOPTION_RET_VAL = ctl.Tag" .InsertLines X + 10, " Next ctl" .InsertLines X + 11, " Unload Me" .InsertLines X + 12, "End Sub" End With ' Adjust the form With TempForm .Properties("Caption") = Title .Properties("Width") = NewCommandButton1.Left + NewCommandButton1.Width + 10 If .Properties("Width") < 160 Then .Properties("Width") = 160 NewCommandButton1.Left = 106 NewCommandButton2.Left = 106 End If .Properties("Height") = TopPos + 24 End With ' Show the form VBA.UserForms.Add(TempForm.Name).Show ' Delete the form ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm ' Pass the selected option back to the calling procedure GetOption = GETOPTION_RET_VAL End Function Thanks, Sudhakar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Form | Excel Discussion (Misc queries) | |||
How do I fill a cell in a user form from a selection on same form? | Excel Discussion (Misc queries) | |||
User Form | Excel Discussion (Misc queries) | |||
User form | Excel Discussion (Misc queries) | |||
Minimizing a Form | Excel Programming |