Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do i insert a Address Bar kind of drop down list within MS Exc
Hi,
I wonderr if anyone can tell me if i am able to insert into an excel spreadsheet an address bar drop down list (like in windows explorer) to view and select the necessary drives, and make this particular cekk work like a pointer to changing the default path of where my macro searches for a specific file. Thanks |
#2
|
|||
|
|||
AT the end is some code that will browse and reset the folder, and use this
code to add a button to your standard toolbar Dim oCtl As Object With Application.CommandBars("Standard") Set oCtl = .Controls.Add(Type:=msoControlButton) oCtl.Caption = "SelectFolder" oCtl.Style = msoButtonCaption oCtl.OnAction = "GetFolder" End With It isn't an address toolbar initially, bvut will open up like such. Option Explicit Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _ Alias "SHGetPathFromIDListA" _ (ByVal pidl As Long, _ ByVal pszPath As String) As Long Private Declare Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" _ (lpBrowseInfo As BROWSEINFO) As Long Private Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type '------------------------------------------------------------- Function GetFolder(Optional ByVal Name As String = _ "Select a folder.") As String '------------------------------------------------------------- Dim bInfo As BROWSEINFO Dim path As String Dim oDialog As Long bInfo.pidlRoot = 0& 'Root folder = Desktop bInfo.lpszTitle = Name bInfo.ulFlags = &H1 'Type of directory to Return oDialog = SHBrowseForFolder(bInfo) 'display the dialog 'Parse the result path = Space$(512) GetFolder = "" If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then GetFolder = Left(path, InStr(path, Chr$(0)) - 1) End If ChDir GetFolder ChDrive GetFolder End Function -- HTH Bob Phillips "Swiftcode" wrote in message ... Hi, I wonderr if anyone can tell me if i am able to insert into an excel spreadsheet an address bar drop down list (like in windows explorer) to view and select the necessary drives, and make this particular cekk work like a pointer to changing the default path of where my macro searches for a specific file. Thanks |
#3
|
|||
|
|||
Hi Bob,
Thank you for helping, but i dont really understand where to place your codes, could you be more specific. I have placed all the declarations in a module, but it doesnt seem to work. You mentioned "the end is some code", i presume that would be the FUNCTION, but how do i link that to the newly created button on the tool bar? Thank you Ray "Bob Phillips" wrote: AT the end is some code that will browse and reset the folder, and use this code to add a button to your standard toolbar Dim oCtl As Object With Application.CommandBars("Standard") Set oCtl = .Controls.Add(Type:=msoControlButton) oCtl.Caption = "SelectFolder" oCtl.Style = msoButtonCaption oCtl.OnAction = "GetFolder" End With It isn't an address toolbar initially, bvut will open up like such. Option Explicit Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _ Alias "SHGetPathFromIDListA" _ (ByVal pidl As Long, _ ByVal pszPath As String) As Long Private Declare Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" _ (lpBrowseInfo As BROWSEINFO) As Long Private Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type '------------------------------------------------------------- Function GetFolder(Optional ByVal Name As String = _ "Select a folder.") As String '------------------------------------------------------------- Dim bInfo As BROWSEINFO Dim path As String Dim oDialog As Long bInfo.pidlRoot = 0& 'Root folder = Desktop bInfo.lpszTitle = Name bInfo.ulFlags = &H1 'Type of directory to Return oDialog = SHBrowseForFolder(bInfo) 'display the dialog 'Parse the result path = Space$(512) GetFolder = "" If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then GetFolder = Left(path, InStr(path, Chr$(0)) - 1) End If ChDir GetFolder ChDrive GetFolder End Function -- HTH Bob Phillips "Swiftcode" wrote in message ... Hi, I wonderr if anyone can tell me if i am able to insert into an excel spreadsheet an address bar drop down list (like in windows explorer) to view and select the necessary drives, and make this particular cekk work like a pointer to changing the default path of where my macro searches for a specific file. Thanks |
#4
|
|||
|
|||
Put all this code in a standard code module, then run SetupBars. You will
then have a neww toolbar button to do what you required. Option Explicit Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _ Alias "SHGetPathFromIDListA" _ (ByVal pidl As Long, _ ByVal pszPath As String) As Long Private Declare Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" _ (lpBrowseInfo As BROWSEINFO) As Long Private Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type '------------------------------------------------------------- Function GetFolder(Optional ByVal Name As String = _ "Select a folder.") As String '------------------------------------------------------------- Dim bInfo As BROWSEINFO Dim path As String Dim oDialog As Long bInfo.pidlRoot = 0& 'Root folder = Desktop bInfo.lpszTitle = Name bInfo.ulFlags = &H1 'Type of directory to Return oDialog = SHBrowseForFolder(bInfo) 'display the dialog 'Parse the result path = Space$(512) GetFolder = "" If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then GetFolder = Left(path, InStr(path, Chr$(0)) - 1) End If ChDir GetFolder ChDrive GetFolder End Function Sub SetupBars() Dim oCtl As Object With Application.CommandBars("Standard") Set oCtl = .Controls.Add(Type:=msoControlButton) oCtl.Caption = "SelectFolder" oCtl.Style = msoButtonCaption oCtl.OnAction = "GetFolder" End With End Sub. -- HTH Bob Phillips "swiftcode" wrote in message ... Hi Bob, Thank you for helping, but i dont really understand where to place your codes, could you be more specific. I have placed all the declarations in a module, but it doesnt seem to work. You mentioned "the end is some code", i presume that would be the FUNCTION, but how do i link that to the newly created button on the tool bar? Thank you Ray "Bob Phillips" wrote: AT the end is some code that will browse and reset the folder, and use this code to add a button to your standard toolbar Dim oCtl As Object With Application.CommandBars("Standard") Set oCtl = .Controls.Add(Type:=msoControlButton) oCtl.Caption = "SelectFolder" oCtl.Style = msoButtonCaption oCtl.OnAction = "GetFolder" End With It isn't an address toolbar initially, bvut will open up like such. Option Explicit Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _ Alias "SHGetPathFromIDListA" _ (ByVal pidl As Long, _ ByVal pszPath As String) As Long Private Declare Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" _ (lpBrowseInfo As BROWSEINFO) As Long Private Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type '------------------------------------------------------------- Function GetFolder(Optional ByVal Name As String = _ "Select a folder.") As String '------------------------------------------------------------- Dim bInfo As BROWSEINFO Dim path As String Dim oDialog As Long bInfo.pidlRoot = 0& 'Root folder = Desktop bInfo.lpszTitle = Name bInfo.ulFlags = &H1 'Type of directory to Return oDialog = SHBrowseForFolder(bInfo) 'display the dialog 'Parse the result path = Space$(512) GetFolder = "" If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then GetFolder = Left(path, InStr(path, Chr$(0)) - 1) End If ChDir GetFolder ChDrive GetFolder End Function -- HTH Bob Phillips "Swiftcode" wrote in message ... Hi, I wonderr if anyone can tell me if i am able to insert into an excel spreadsheet an address bar drop down list (like in windows explorer) to view and select the necessary drives, and make this particular cekk work like a pointer to changing the default path of where my macro searches for a specific file. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I insert a drop down list in a cell using | Excel Worksheet Functions | |||
How do I insert A drop down dialog box as a 'list to choose from'? | Excel Worksheet Functions | |||
how do I insert drop down menus into excel that are dependent on . | Excel Discussion (Misc queries) | |||
How do u insert a drop down menu in a Excel | Excel Worksheet Functions | |||
Challenging Charting | Charts and Charting in Excel |