How do you make browse buttons in Microsoft Excel because i am making something for someone who isn't good with typing file addresses?
Printable View
How do you make browse buttons in Microsoft Excel because i am making something for someone who isn't good with typing file addresses?
Hi ,
U jus type in the required data in any unused part of your worksheet and goto the cell where u want the data
use alt+DL (DATA->Validation)one the first box drop down any select list
on the second one select the red botton use arrow bottons to move to the place where u have entered the data eariler
press shift and select the list
hit enter and u have the validation done.
Hope this makes sense???
Do you mean a CommonDialog browse box?Like when you do a
Save As... etc.
Place in a standard module:
VB Code:
Option Explicit Public Const LF_FACESIZE = 32 Public Const MAX_PATH = 260 'ShowOpen/ShowSave flags: Public Const OFN_ALLOWMULTISELECT = &H200 Public Const OFN_CREATEPROMPT = &H2000 Public Const OFN_ENABLEHOOK = &H20 Public Const OFN_ENABLETEMPLATE = &H40 Public Const OFN_ENABLETEMPLATEHANDLE = &H80 Public Const OFN_EXPLORER = &H80000 Public Const OFN_EXTENSIONDIFFERENT = &H400 Public Const OFN_FILEMUSTEXIST = &H1000 Public Const OFN_HIDEREADONLY = &H4 Public Const OFN_LONGNAMES = &H200000 Public Const OFN_NOCHANGEDIR = &H8 Public Const OFN_NODEREFERENCELINKS = &H100000 Public Const OFN_NOLONGNAMES = &H40000 Public Const OFN_NONETWORKBUTTON = &H20000 Public Const OFN_NOREADONLYRETURN = &H8000 Public Const OFN_NOTESTFILECREATE = &H10000 Public Const OFN_NOVALIDATE = &H100 Public Const OFN_OVERWRITEPROMPT = &H2 Public Const OFN_PATHMUSTEXIST = &H800 Public Const OFN_READONLY = &H1 Public Const OFN_SHAREAWARE = &H4000 Public Const OFN_SHAREFALLTHROUGH = 2 Public Const OFN_SHARENOWARN = 1 Public Const OFN_SHAREWARN = 0 Public Const OFN_SHOWHELP = &H10 Public Const OFS_MAXPATHNAME = 128 'BrowseForFolder flags: Public Const BIF_RETURNONLYFSDIRS = &H1 ' For finding a folder to start document searching Public Const BIF_DONTGOBELOWDOMAIN = &H2 ' For starting the Find Computer Public Const BIF_STATUSTEXT = &H4 Public Const BIF_RETURNFSANCESTORS = &H8 Public Const BIF_BROWSEFORCOMPUTER = &H1000 ' Browsing for Computers. Public Const BIF_BROWSEFORPRINTER = &H2000 ' Browsing for Printers Public Const BIF_BROWSEINCLUDEFILES = &H4000 ' Browsing for Everything 'Error constants Public Const CDERR_DIALOGFAILURE = &HFFFF Public Const CDERR_FINDRESFAILURE = &H6 Public Const CDERR_GENERALCODES = &H0 Public Const CDERR_INITIALIZATION = &H2 Public Const CDERR_LOADRESFAILURE = &H7 Public Const CDERR_LOADSTRFAILURE = &H5 Public Const CDERR_LOCKRESFAILURE = &H8 Public Const CDERR_MEMALLOCFAILURE = &H9 Public Const CDERR_MEMLOCKFAILURE = &HA Public Const CDERR_NOHINSTANCE = &H4 Public Const CDERR_NOHOOK = &HB Public Const CDERR_REGISTERMSGFAIL = &HC Public Const CDERR_NOTEMPLATE = &H3 Public Const CDERR_STRUCTSIZE = &H1 'Types Type POINTAPI x As Long y As Long End Type Type RECT Left As Long Top As Long Right As Long Bottom As Long End Type Type OPENFILENAME lStructSize As Long hwndOwner As Long hInstance As Long lpstrFilter As String lpstrCustomFilter As String nMaxCustFilter As Long nFilterIndex As Long lpstrFile As String nMaxFile As Long lpstrFileTitle As String nMaxFileTitle As Long lpstrInitialDir As String lpstrTitle As String flags As Long nFileOffset As Integer nFileExtension As Integer lpstrDefExt As String lCustData As Long lpfnHook As Long lpTemplateName As String End Type Type BROWSEINFO hwndOwner As Long pIDLRoot As Long pszDisplayName As Long lpszTitle As Long ulFlags As Long lpfnCallback As Long lParam As Long iImage As Long End Type Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long) Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" (ByVal _ lpString1 As String, ByVal lpString2 As String) As Long Declare Function SHBrowseForFolder Lib "shell32" (lpbi As _ BROWSEINFO) As Long Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList _ As Long, ByVal lpBuffer As String) As Long Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" _ (pOpenfilename As OPENFILENAME) As Long Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" _ (pOpenfilename As OPENFILENAME) As Long Declare Function GetFileTitleAPI Lib "comdlg32.dll" Alias "GetFileTitleA" _ (ByVal lpszFile As String, ByVal lpszTitle As String, ByVal cbBuf As Integer) As Integer Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long Global OFName As OPENFILENAME Global BInfo As BROWSEINFO Public Function ShowOpen(hwndOwner As Long, sFilter As String, sTitle As String, Optional nFlags As Long = OFN_EXPLORER) As String OFName.lStructSize = Len(OFName) OFName.hwndOwner = hwndOwner OFName.hInstance = App.hInstance OFName.lpstrFilter = sFilter OFName.lpstrFile = String(254, vbNullChar) OFName.nMaxFile = 255 OFName.lpstrFileTitle = String(254, vbNullChar) OFName.nMaxFileTitle = 255 OFName.lpstrTitle = sTitle OFName.flags = nFlags If GetOpenFileName(OFName) Then ShowOpen = StripTerminator(OFName.lpstrFile) Else ShowOpen = "" End If End Function Public Function ShowSave(hwndOwner As Long, sFilter As String, sTitle As String, Optional nFlags As Long = OFN_EXPLORER) As String OFName.lStructSize = Len(OFName) OFName.hwndOwner = hwndOwner OFName.hInstance = App.hInstance OFName.lpstrFilter = sFilter OFName.lpstrFile = String(254, vbNullChar) OFName.nMaxFile = 255 OFName.lpstrFileTitle = String(254, vbNullChar) OFName.nMaxFileTitle = 255 OFName.lpstrTitle = sTitle OFName.flags = nFlags If GetSaveFileName(OFName) Then ShowSave = StripTerminator(OFName.lpstrFile) Else ShowSave = "" End If End Function Public Function BrowseForFolder(hwndOwner As Long, sTitle As String) As String Dim iNull As Integer Dim lpIDList As Long Dim lResult As Long With BInfo .hwndOwner = hwndOwner .lpszTitle = lstrcat(sTitle, "") .ulFlags = BIF_RETURNONLYFSDIRS End With lpIDList = SHBrowseForFolder(BInfo) If lpIDList Then BrowseForFolder = String$(MAX_PATH, 0) SHGetPathFromIDList lpIDList, BrowseForFolder CoTaskMemFree lpIDList BrowseForFolder = StripTerminator(BrowseForFolder) End If End Function Public Function GetFileTitle(sFile As String) As String GetFileTitle = String(255, vbNullChar) GetFileTitleAPI sFile, GetFileTitle, 255 GetFileTitle = StripTerminator(GetFileTitle) End Function Public Function GetErrorString() As String Select Case CommDlgExtendedError Case CDERR_DIALOGFAILURE GetErrorString = "The dialog box could not be created." Case CDERR_FINDRESFAILURE GetErrorString = "The common dialog box function failed to find a specified resource." Case CDERR_INITIALIZATION GetErrorString = "The common dialog box function failed during initialization." Case CDERR_LOADRESFAILURE GetErrorString = "The common dialog box function failed to load a specified resource." Case CDERR_LOADSTRFAILURE GetErrorString = "The common dialog box function failed to load a specified string." Case CDERR_LOCKRESFAILURE GetErrorString = "The common dialog box function failed to lock a specified resource." Case CDERR_MEMALLOCFAILURE GetErrorString = "The common dialog box function was unable to allocate memory for internal structures." Case CDERR_MEMLOCKFAILURE GetErrorString = "The common dialog box function was unable to lock the memory associated with a handle." Case CDERR_NOHINSTANCE GetErrorString = "The ENABLETEMPLATE flag was set in the Flags member of the initialization structure for the corresponding common dialog box, but you failed to provide a corresponding instance handle." Case CDERR_NOHOOK GetErrorString = "The ENABLEHOOK flag was set in the Flags member of the initialization structure for the corresponding common dialog box, but you failed to provide a pointer to a corresponding hook procedure." Case CDERR_REGISTERMSGFAIL GetErrorString = "The RegisterWindowMessage function returned an error code when it was called by the common dialog box function." Case CDERR_NOTEMPLATE GetErrorString = "The ENABLETEMPLATE flag was set in the Flags member of the initialization structure for the corresponding common dialog box, but you failed to provide a corresponding template." Case CDERR_STRUCTSIZE GetErrorString = "The lStructSize member of the initialization structure for the corresponding common dialog box is invalid." Case Else GetErrorString = "Undefined error ..." End Select End Function Private Function StripTerminator(sInput As String) As String Dim ZeroPos As Integer ZeroPos = InStr(1, sInput, vbNullChar) If ZeroPos > 0 Then StripTerminator = Left$(sInput, ZeroPos - 1) Else StripTerminator = sInput End If End Function