Custom UserForm Question

[suburbanpsyco]

Hello again all!

I am currently working on designing a custom userform for my database. I found the following code and am working on converting it to work properly in Access. It is current aimed to work in Excel.

Option Compare Database
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 Access.OptionButton
    Dim NewCommandButton1 As Access.CommandButton
    Dim NewCommandButton2 As Access.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

Im made some of the modifications already (or at least i think i have). Any pointers are always appreciated, but i would really like to know the Access equivalent of this line of code:

ThisWorkbook.VBProject.VBComponents.Add(3)

Thanks in Advance!

 

[VilaRestal]

Access Forms are very different to Windows Forms and I'd be very surprised if you can add Access controls to a Windows Form made at runtime in Access VBA (if indeed you can create windows Forms in Access)

I expect, if it as at all possible to create a windows form in Access VBA then you would be using an unusual reference and/or set of private declare functions and you wouldn't mix it with Access objects at all.

I think this may be impossible in Access. Excel is a very different program and its VBA is equally different to Access's

 

[VilaRestal]

Having said all that if you do get it to work then let us know. It would be very nice to be able to make custom dialogs like that in Access

 

[suburbanpsyco]

Noted. Will post with the results of my experiment. Thanks!

 

[boblarson]

You can add a form via code in Access. But adding it and adding controls on it will require you to open it in design view (it can be hidden though while in design view) and then you can create the controls on it and all and then assign code to the events. But the real drawback from doing this is that it will bloat the database and it could bloat it considerably. So you would have to be mindful of that and either copy a new frontend over each time (we do that where I currently work - we copy a new frontend file over for the user when they click the shortcut and that way they always have the latest file and also we don't have to worry about it bloating or if there is corruption).

 

[boblarson]

But I did forget to mention that you will NEVER be able to compile the database as an MDE/ACCDE file if you use this code then. Also, users who use the Runtime version of Access would not be able to use your database as that would also not be able to create those things and would generate errors since the runtime version can't create new objects.

 

[suburbanpsyco]

http://spreadsheetpage.com/index.php/site/tip/creating_a_userform_programmatically/

I know this is an excel solution, but ill dump it here anyways, since it diretly relates to my question, and i do appologise in advance if i break any rules . I find it bizzare that excel supports something like this, but access from the same year does not. I am still seeing what progress i can make on this in access, imposiible though it may be. Once again, all your help is greatly appreciated.

 

[VilaRestal]

The difficulty is Access has its own versions of windows components that Excel does not.

So, Excel can reference things because they won't conflict with its own classes: It doesn't have its own Form class.

If Access tries to reference anything that has a Form class (like VB6 for example) it will immediately conflict with Access's own Form class, which is very different to a normal windows form.

It's a pity and one of the most tragic facts of Access is how forms can't be dealt with like forms in any other language: declare a new one, construct it in code and show it. Even if it achieved that having another type of form that behaved like windows forms and could be declared and classes made that extend it. But because it doesn't and it uses the name 'Form' for its forms you can't reference anything that has a form class in its package.

That's my understanding of it