Creat beautiful Excel Dialogs (preferably using javascript)

[ahmed470]

Is it possible to create beautiful dialogs in Excel not the traditional ones?
I want to achieve the following look. Only the look, I can functionalities myself.
See the screenshot below from excel. It is a built in dialog. Can this be made in excel? (without making a seperate Addin)

e.g.

 

[Yongle]

The code below is a bit more than "snippets" - I got carried away

The code..
1. asks user for a name for the userform
2. auto-creates a similar userform to above "on the fly"
3. inserts few lines of userform code into the new userform's code module
4. three subs are inserted (UserForm_Initialize, lbl_Cancel_Button_Click, lbl_OK_Button_Click )
5. option button "Basic" is auto-checked upon form initialisation
6. the 2 "fake" command buttons have something behind them ("OK" triggers a message,"Cancel" unloads the form) and so can be clicked on immediately

Comment
The code is long because it deliberately repeats things to make it easy to follow and so that you can copy/paste sections into separate procedures if you wish.
Additionally some object variables have been commented out to illustrate how to create them - they differ from each other

If you are regularly creating thse things it would make sense build a toolbox of procedures creating the various objects which could then be called
- Add_pretty_UserForm
- Add_pretty_TextBox
- Add_pretty_Label
- Add_Fake_CommandButton
etc

Credit for the code creating the user form and its objects is due to IConfused who posted his efforts on this page

VBA
Paste code into a standard module and run it

Sub CreateNewUserForm()
Dim myForm As Object, FormName As String, msg As String
Dim NewTextBox As MSForms.TextBox
Dim NewLabel As MSForms.Label
Dim NewOptionButton As MSForms.OptionButton
'Dim NewFrame As MSForms.Frame
'Dim NewButton As MSForms.CommandButton
'Dim NewComboBox As MSForms.ComboBox
'Dim NewListBox As MSForms.ListBox
'Dim NewCheckBox As MSForms.CheckBox
Dim X As Integer
Dim Line As Integer
'fonts
Dim Font1 As String:        Font1 = "Tahoma"
Dim Font2 As String:        Font2 = "Arial Narrow"
'colour palette
Dim clrWhite As Long:       clrWhite = 16777214
Dim clrFontHdr As Long:     clrFontHdr = 3506772
Dim clrFontText:            clrFontText = 5855577
Dim clrFontUser As Long:    clrFontUser = 65793
Dim clrBorder As Long:      clrBorder = 9359529

'stop screen flashing while creating form
Application.VBE.MainWindow.Visible = False

'Create the User Form
FormName = InputBox("You are about to create a new user form" & vbCr & "Name your form", "NEW FORM")
FormName = "UF_" & Replace(FormName, " ", "_")

'form properties
On Error Resume Next
    Set myForm = ThisWorkbook.VBProject.VBComponents.Add(3)
        myForm.Properties("Name") = FormName
    If Err > 0 Then
        msg = "Name Rejected" & vbCr & "Already exists\ illegal characters in name"
        msg = msg & vbCr & "Rename " & myForm.Properties("Name") & " in VBA editor"
        MsgBox msg, vbExclamation, "OOPS!"
    End If
On Error GoTo 0

With myForm
    .Properties("Caption") = ""
    .Properties("Width") = 280
    .Properties("Height") = 150
    .Properties("foreColor") = clrWhite
    .Properties("BackColor") = clrWhite
End With

'Create Label
Set NewLabel = myForm.designer.Controls.Add("Forms.label.1")
With NewLabel
    .Name = "lbl_From_Web"
    .Top = 1
    .Left = 10
    .Width = 75
    .Height = 20
    .Font.Size = 15
    .Font.Name = Font1
    .ForeColor = clrFontHdr
    .BackColor = clrWhite
    .BorderColor = clrWhite
    .Caption = "From Web"
End With

'Create Option Buttons
Set NewOptionButton = myForm.designer.Controls.Add("Forms.optionbutton.1")
With NewOptionButton
    .Name = "optn_Basic"
    .Top = 30
    .Left = 10
    .Width = 50
    .Height = 20
    .Font.Size = 10
    .Font.Name = Font1
    .ForeColor = clrFontText
    .BackColor = clrWhite
    .Caption = "Basic"
End With

Set NewOptionButton = myForm.designer.Controls.Add("Forms.optionbutton.1")
With NewOptionButton
    .Name = "optn_Adv"
    .Top = 30
    .Left = 60
    .Width = 70
    .Height = 20
    .Font.Size = 10
    .Font.Name = Font1
    .ForeColor = clrFontText
    .BackColor = clrWhite
    .Caption = "Advanced"
End With

'Create URL Label
Set NewLabel = myForm.designer.Controls.Add("Forms.label.1")
With NewLabel
    .Name = "lbl_URL"
    .Top = 50
    .Left = 10
    .Width = 20
    .Height = 20
    .Font.Size = 10
    .Font.Name = Font1
    .ForeColor = clrFontText
    .BackColor = clrWhite
    .BorderColor = clrWhite
    .Caption = "URL"
End With

'Create TextBox
Set NewTextBox = myForm.designer.Controls.Add("Forms.textbox.1")
With NewTextBox
    .Name = "txt_URL"
    .Top = 70
    .Left = 10
    .Width = 150
    .Height = 16
    .Font.Size = 12
    .Font.Name = Font2
    .ForeColor = clrFontUser
    .BackColor = clrWhite
End With

'Create "fake" OK button
Set NewLabel = myForm.designer.Controls.Add("Forms.label.1")
With NewLabel
    .Name = "lbl_Ok_Button"
    .Top = 100
    .Left = 170
    .Width = 40
    .Height = 12
    .Font.Size = 10
    .Font.Name = Font1
    .ForeColor = clrFontText
    .BackColor = clrWhite
    .BorderColor = clrBorder
    .BorderStyle = fmBorderStyleSingle
    .TextAlign = fmTextAlignCenter
    .Caption = "OK"
End With

'Create "fake" Cancel button
Set NewLabel = myForm.designer.Controls.Add("Forms.label.1")
With NewLabel
    .Name = "lbl_Cancel_Button"
    .Top = 100
    .Left = 220
    .Width = 40
    .Height = 12
    .Font.Size = 10
    .Font.Name = Font1
    .ForeColor = clrFontText
    .BackColor = clrWhite
    .BorderColor = clrBorder
    .BorderStyle = fmBorderStyleSingle
    .TextAlign = fmTextAlignCenter
    .Caption = "Cancel"
End With

'to clear any existing lines
On Error Resume Next
    For X = 1 To myForm.CodeModule.CountOfLines
        myForm.CodeModule.DeleteLines X
    Next X
On Error GoTo 0

With myForm.CodeModule
'add code for UserForm
    .InsertLines 1, "Private Sub UserForm_Initialize()"
    .InsertLines 2, "Me.optn_Basic.Value = ""True"" "
    .InsertLines 3, "'etc ..."
    .InsertLines 4, "'etc..."
    .InsertLines 5, "End Sub"

'add code for Cancel Button
    .InsertLines 6, "Private Sub lbl_Cancel_Button_Click()"
    .InsertLines 7, "Unload Me"
    .InsertLines 8, "End Sub"

'add code for OK Button
    .InsertLines 9, "Private Sub lbl_OK_Button_Click()"
    .InsertLines 10, "MsgBox ""Life is OK"" "
    .InsertLines 11, "End Sub"
    End With

VBA.UserForms.Add(myForm.Name).Show

End Sub

 

[Yongle]

In previous post I should have told you add a reference:

VBA \ Tools \ References \ Scroll down to Microsoft Visual Basic for Applications Extensibility 5.3 \ check the box \ OK

The code will not run unless you add that reference

 

[ahmed470]

Thanks a lot but
I am unable to run it.
First I get this error:
User defined Type Not defined on these lines

Dim NewTextBox As MSForms.TextBox
Dim NewLabel As MSForms.Label
Dim NewOptionButton As MSForms.OptionButton

If I remove MSForms from above lines then the code runs.
Then on this line it gives this error

Application.VBE.MainWindow.Visible = False

Method VBE of _application failed
If I commend above line.

It shows a dialog box to create new User form

Whatever Name I enter in it, it gives error
Name rejected
Already Exists\illegal characters in name

So far no luck with running it.

 

[Yongle]

see post 12
- even if your name is illegal it should still create the form

 

[Yongle]

Ah

A reference is also required to Microsoft Forms 2.0 Object Library

If it is not visible in the list, click on the Browse button

I am on Windows10 and Office365 and I browsed to
C:\Windows\SysWOW64
and selected this file
FM20.DLL

 

[ahmed470]

Still giving the same errors.
I am not windows 10 and using office 365.
I found Microsoft Forms 2.0 library at
C:\Program Files\Microsoft Office\root\VFS\System
but still the same error.

 

[Yongle]

I cannot do anything for you until you fix this issue.
It is pretty standard stuff so should be fixable.
Please confirm that when you click on References that the MS Forms Object Library is there and that the box is checked.


Thanks