Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Multi-button Input Form with on-the-fly userform

  1. #1
    Join Date
    23rd April 2007
    Posts
    3,452

    Multi-button Input Form with on-the-fly userform

    In another thread, I mentioned an inputbox with more than two buttons. Here it is. I call this thing an InputForm.
    It's really a userform created on the fly. It is done by creating a new class, thingInputForm.

    To call the form, a variable is declared as the new data class.

    VB:
    Dim userInput As New thingInputForm 
    
    

    userInput.InputForm(Prompt,[Title],[Buttons],[Default],[dataType],[passwordChar]) calls the InputForm and the user makes their entry.

    A thingInputForm hold the results of the last user interface in the properties .Value and .Pressed.

    .Value is what was entered into the box with either the keyboard or mouse.
    .Pressed ,with no index , returns the number of the button that was pressed at the last user entry.
    .Pressed(i) returns a boolean value. True if button #i was the last pressed.

    .InputForm displays a input box with up to three buttons. InputForm returns the number of the button pressed (same as .Pressed) or False if the user Cancels.

    The arguments Prompt, Title, and Default are strings.

    Buttons is an optional argument. Setting it to vbOKOnly, vbOKCancel, vbYesNoCancel determines how many buttons are shown.
    Adding vbDefault1, 2 or 3 to that value changes the default. Those are the only button styles InputForm accepts.
    vbOKCancel is the default.

    dataType is as in an inputbox. 1-number,2-text,4-logical,8-range,16-error

    passwordChar will mask the characters for password entry. If passwordChar = False characters entered are displayed normaly. If True, a bullet will mask each character entered. A custom masking character can be entered as a string.

    This password demo shows the general syntax. The other demos in the attachment show other features.
    VB:
    Sub passwordDemo() 
        Dim passCode As New thingInputForm 
        Dim inputButton As String 
         
        inputButton = passCode.InputForm(Prompt:="Type, friend, and enter.", _ 
        Title:="Password Check", _ 
        dataType:=2, _ 
        PasswordChar:=True) 
        If inputButton = "False" Then Exit Sub 
         
        If LCase(passCode.Value) = "friend" Then 
            MsgBox "Welcome, friend, you pass." 
        Else 
            MsgBox "Wrong password entered." 
        End If 
         
    End Sub 
    
    
    thingInputForm can easily be transmuted out of a class and into a function. The creeping feature creature prowls around a project like this. A fourth button and custom button caption arguments are a couple of features I rejected.

    The default button needs better bolding and the validation function typeOK is not too clean.

    I hope someone finds this useful.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by mikerickson; June 23rd, 2007 at 18:50.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,062

    Re: Multi-button Input Form with on-the-fly userform

    Thanks Mike. Well done.
    Regards,

    Wigi

    Excel MVP 2011-2014



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  3. #3
    Join Date
    23rd April 2007
    Posts
    3,452

    Re: Multi-button Input Form with on-the-fly userform

    Thank you Andy Pope for the info about the Default property of a CommandButton.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    Re: Multi-button Input Form with on-the-fly userform

    Hi mike,

    I’m getting numerous Compile Errors when clicking on the “demo” button. Error is always “Can’t find project or library”

    Example:

    this code is highlighted in yellow

    VB:
    Property Get InputForm(Prompt As String, _ 
        Optional Buttons As Variant, _ 
        Optional Title As String, _ 
        Optional Default As String, _ 
        Optional dataType As Long, _ 
        Optional PasswordChar As String) As Variant 
    
    

    and the word Chr$ is selected in this line of code

    VB:
    If CStr(PasswordChar) = "True" Then PasswordChar = Chr$(165) 
    
    
    I get them with simple code such as where Left$ is selected:

    VB:
    PasswordChar = Left$(PasswordChar, 1) 
    
    
    Removing the $ has no effect, same compile errors.

    I’m using xl2000 on the old dog windows98

    Any ideas?

    Bill

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Multi-button Input Form with on-the-fly userform

    I get compile errors as well, with Chr$() and LTrim(). Yet when I unload the worksheet, both functions work in the Immediate window. And also on
    VB:
    Dim Form As UserForm 
    
    
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  6. #6
    Join Date
    23rd April 2007
    Posts
    3,452

    Re: Multi-button Input Form with on-the-fly userform

    The RefEdit library needs to be open as does the Microsoft Visual Basic for Applications Extensibility library.

    I've found another problem. Every time a userform is created and destroyed, the file takes up a little more space. Excel doesn't complely clear all the memory when a userform is deleted. A temporary userform will make a file grow with each use. A version with permenant userform would be better.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    Re: Multi-button Input Form with on-the-fly userform

    mike,

    The RefEdit library needs to be open as does the Microsoft Visual Basic for Applications Extensibility library.
    I now have these libraries referenced and got past the initial compile errors. Now have a compile error “Expected user-defined type, not project.”



    VB:
    Private Sub makeRawForm(newComp As Object, _ 
        Prompt As String, _ 
        ByVal Buttons As Long, _ 
        Title As String, _ 
        Default As String, _ 
        PasswordChar As String) 
        Dim newForm As UserForm 
        Dim refEditBox As RefEdit  ‘ <<< This line gives the error 
    
    
    Bill

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    23rd April 2007
    Posts
    3,452

    Re: Multi-button Input Form with on-the-fly userform

    That RefEdit is causing a bunch of grief. Dimming it as Object should fix the situation.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Input Data Using An Input Form
    By mherber2 in forum Excel and/or Access Help
    Replies: 5
    Last Post: April 20th, 2007, 06:11
  2. Replies: 2
    Last Post: October 5th, 2006, 15:47
  3. Create A Multi Page Form With Tab Controls
    By billyj in forum Excel and/or Access Help
    Replies: 2
    Last Post: October 4th, 2006, 04:10
  4. multi form controls
    By globalStar in forum EXCEL HELP
    Replies: 7
    Last Post: May 25th, 2006, 21:55

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno