Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Inputbox default (VBA 2000)

    I want the user to select a file from a list. In the past I have created a numbered list as part of the InputBox prompt and required the user to enter the correct number. It strikes me that this a little more hostile than selecting from a pull-down list; but despite googling around, I've not found a technique which will do this.
    Any ideas?
    Regards
    Don

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inputbox default (VBA 2000)

    I'm sorry, I don't quite understand what you're asking for. Do you want to do something with an inputbox, as the subject suggests, or do you want an alternative for an inputbox? Could you try to explain?

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inputbox default (VBA 2000)

    Hans
    Thank you for the speedy response.
    I would like an alternative to an Inputbox that will require the user to select from a pulldown or popup list, instead of typing information into the form.
    Apologies for the lack of clarity.
    Regards
    Don

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inputbox default (VBA 2000)

    You can create a userform with a combo box or a list box, and use code to populate it with the appropriate file names, using the Dir function and the AddItem method of the combo box/list box. You'll find a simple example in <post#=519,505>post 519,505</post: >.

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inputbox default (VBA 2000)

    Thank you Hans.
    Right on target.
    Regards
    Don

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inputbox default (VBA 2000)

    Hans
    I went a little further down the thread to <!post=this post,519625>this post<!/post>. Reworked it slightly to sort the files, and work on a directory in my machine. Now I'm as happy as a lark; But I need to pass a couple of arguments for my intended final use, and cannot seem to achieve a workable set of code. I currently have:

    In a standard module
    <pre>Option Explicit

    Public Sub showForm()


    Dim strPath As String
    Dim FromDir As Boolean
    '
    ' I need to pass these two variables as arguments
    strPath = "W:050712 QSDocs_Acronyms"
    FromDir = False
    '
    UserForm1.Show
    End Sub
    </pre>




    In the form <pre>Option Explicit
    '
    ' I need to replace this constant with a passed argument
    Const strPath = "W:050712 QSDocs_Acronyms"

    '
    Private Sub UserForm_Initialize()
    Dim i As Long
    Dim ArrayRedimmed As Boolean
    Dim myFiles() As String
    Dim strFile As String
    Dim oDoc As Document
    Dim FromDir As Boolean
    '
    ' I need to pass this
    FromDir = True
    '
    If FromDir Then
    strFile = Dir(strPath & "*.doc")
    Do While Not strFile = ""
    If ArrayRedimmed Then
    ReDim Preserve myFiles(UBound(myFiles) + 1)
    Else
    ReDim myFiles(0)
    ArrayRedimmed = True
    End If
    myFiles(UBound(myFiles)) = strFile
    strFile = Dir
    Loop
    Else
    For Each oDoc In Documents
    If UCase(Right(oDoc.Name, 4)) = ".DOC" Then
    If ArrayRedimmed Then
    ReDim Preserve myFiles(UBound(myFiles) + 1)
    Else
    ReDim myFiles(0)
    ArrayRedimmed = True
    End If
    myFiles(UBound(myFiles)) = oDoc.Name
    End If
    Next
    End If
    '
    Call BubbleSort.Normal(myFiles())
    '
    For i = LBound(myFiles) To UBound(myFiles)
    Me.ListBox1.AddItem myFiles(i)
    Next i
    '
    End Sub
    </pre>


    Oh and by the way; I will have to pass results of the user's selection back from the form to the standard module. I've not even attempted that as yet. Any guidance on this subject will be most appreciated.
    Regards
    Don

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inputbox default (VBA 2000)

    You can use global variables to pass information back and forth, i.e. variables declared as Public at the top of the standard module, before all Subs and Functions:

    Option Explicit

    Public strPath As String
    Public strFile As String
    Public FromDir As Boolean

    Public Sub showForm()
    strPath = "W:050712 QSDocs_Acronyms"
    ...

    And to pass the selected file in the On Click event of the OK button (I assume you have on on your form):

    strFile = Me.ListBox1

    Because strPath etc. are global variables, you can use and modify them in all modules in the workbook. You should NOT declare them again in the userform module.

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inputbox default (VBA 2000)

    Hans
    Thank you. You have the patience of Job.
    Regards
    Don

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inputbox default (VBA 2000)

    Hi Legare
    I tried Application.GetFilename in VBA for both Excel and Word (both in Office 2000). All that I got for my effort was the following messages, Any ideas?
    Regards
    Don

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox default (VBA 2000)

    Hi Don

    There are several way of passing parameters to/ from a Userform. Remember that a Userform is just an instance of just another class. Apart from global variables, as suggested by Hans, the methods I favour are:

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox default (VBA 2000)

    It is GetOpenFileName you're after:

    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Sub</font color=blue> GetOpenFileNameExample3()
    <font color=blue>Dim</font color=blue> lCount <font color=blue>As</font color=blue> <font color=blue>Long</font color=blue>
    <font color=blue>Dim</font color=blue> vFilename <font color=blue>As</font color=blue> <font color=blue>Variant</font color=blue>
    <font color=blue>Dim</font color=blue> sPath <font color=blue>As</font color=blue> <font color=blue>String</font color=blue>
    <font color=blue>Dim</font color=blue> lFilecount <font color=blue>As</font color=blue> <font color=blue>Long</font color=blue>
    sPath = "c:windowstemp"
    ChDrive sPath
    ChDir sPath
    vFilename = Application.GetOpenFilename("Microsoft Excel files (*.xls),*.xls", , "Please select the file(s) to import", , <font color=blue>True</font color=blue>)
    <font color=blue>If</font color=blue> TypeName(vFilename) = "Boolean" <font color=blue>Then</font color=blue> <font color=blue>Exit</font color=blue> <font color=blue>Sub</font color=blue>
    <font color=blue>For</font color=blue> lCount = 1 <font color=blue>To</font color=blue> <font color=blue>UBound</font color=blue>(vFilename)
    Workbooks.Open vFilename(lCount)
    <font color=blue>Next</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>Sub</font color=blue>
    </font color=black></code></div hiblock>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inputbox default (VBA 2000)

    Thanks Alan
    When the alligators finally back off a little, I'll experiment and gain some experience with this.
    Regards
    Don

  13. #13
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inputbox default (VBA 2000)

    Thank you Jan
    Regards
    Don

  14. #14
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputbox default (VBA 2000)

    Interesting. I assume the Excel version of GetOpenFilename returns a variant, which is False if the user cancels the dialog. I've only used the CommonDialog function, which returns zero if the user cancels. From memory, MS didn't franchise this function out to Word and other Office apps. Is this right?

    Alan

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inputbox default (VBA 2000)

    a) It's correct that GetOpenFilename returns a Variant that is False if the user cancels, otherwise a string. The same holds for GetSaveAsFilename.

    [img]/forums/images/smilies/cool.gif[/img] The CommonDialog control is not available in the standard and Professional versions of Office, only in the Developer Edition. You've seen the Windows API workaround in <post#=505,867>post 505,867</post: >.

    c) In Office XP (2002), Microsoft introduced a new FileDialog object, that can be used to let the user select one or more files, or to specify a file name, or to specify a folder.

Page 1 of 2 12 LastLast

Posting Permissions

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