This commandbutton1 (caption as "...") allow a user to choose one file (picture or spreadsheet or etc) from any folder of My Computer.

For example if I click this commandbutton1, I can go to 'C:\Program Files\Adobe' and choose 'abc.pdf.' This 'abc.pdf' appear in the Label1 beside commandbutton1 as ''C:\Program Files\Adobe\abc.pdf'

What coding is suitable for this situation? The function is similar like when we need to attach a resume into online application.

Regard,
Mona

Mona:
It is hard to tell from your issue, but I would assign a macro to the command button that uses the Windows API GetOpenFileName (I have found this safer and more flexible that the Excel Application.GetOpenFilename).

Here is the function definition and related Type def, followed by a code sample. Please note: I did not include all the variable definitions in the code sample. Basicially the Windows API call lets the user navigate anywhere to select a file and if they do (and click OK) the filename is returned. You can read about the API call on the Microsoft site:

http://msdn.microsoft.com/en-us/library/ms646927.aspx


Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
      "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Type OPENFILENAME
    lngGOFStructSize As Long
    lngGOFOwner As Long
    lngGOFInstance As Long
    strGOFFilter As String
    strGOFCustomFilter As String
    lngGOFMaxCustFilter As Long
    lngGOFFilterIndex As Long
    strGOFFile As String
    lngGOFMaxFile As Long
    strGOFFileTitle As String
    lngGOFMaxFileTitle As Long
    strGOFInitialDir As String
    strGOFTitle As String
    lngGOFFlags As Long
    intGOFFileOffset As Integer
    intGOFFileExtension As Integer
    strGOFDefExt As String
    lngGOFCustData As Long
    lngGOFHook As Long
    strGOFTemplateName As String
End Type

Sample code (without variable declarations):
'***
'* . get name (and location) of Test Data Workbook
'***
    OpenFile.lngGOFStructSize = Len(OpenFile)
    OpenFile.strGOFFilter = "(Test_Data*.XLS*)" & Chr(0) & _
        "Test_Data*.XLS*" & Chr(0)
    OpenFile.lngGOFFilterIndex = 1
    OpenFile.strGOFFile = String(257, 0)
    OpenFile.lngGOFMaxFile = Len(OpenFile.strGOFFile) - 1
    OpenFile.lngGOFMaxFileTitle = OpenFile.lngGOFMaxFile
    OpenFile.strGOFInitialDir = strDataPath
    OpenFile.strGOFTitle = "Open Test Data Workbook"
    OpenFile.lngGOFFlags = 0

    Application.ScreenUpdating = True
    lngReturn = GetOpenFileName(OpenFile)
    Application.ScreenUpdating = False

    If lngReturn = 0 Then
        strMsg = "Open CMA Data Workbook CANCELLED..."
        GoTo Test_OpenExit
    End If

    varFileName = OpenFile.strGOFFile

Hope this helps.

Lenora

Hello Lenora,

I found easier and simpler coding for me to understand:

Sub CommandButton1_Click()
fn = Application.GetOpenFileName("Excel-files,*.xls",1, "Select One File To Open", , False)
If TypeName(fn) = "Boolean" Then Exit Sub
Me.TextBox1.Text = fn
Exit Sub

So, as soon as a user select which spreadsheet he/she wants, the details will printed into TextBox1.

Anyway, thanks Lenora. I tried to understand the coding you gave here, but its too complicated for me as a beginner in VBA.

Regards,
Mona