How do i insert a Address Bar kind of drop down list within MS Exc

Swiftcode · 06-13-2005, 07:05 AM

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

Bob Phillips · 06-13-2005, 08:05 AM

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" <Swiftcode@discussions.microsoft.com> wrote in message
news:A165DE90-C3F6-4421-8084-0C00610499CE@microsoft.com...
> 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

swiftcode · 06-16-2005, 11:05 AM

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" <Swiftcode@discussions.microsoft.com> wrote in message
> news:A165DE90-C3F6-4421-8084-0C00610499CE@microsoft.com...
> > 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
>
>
>

Bob Phillips · 06-16-2005, 08:05 PM

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" <swiftcode@discussions.microsoft.com> wrote in message
news:2F5088C6-1AE7-45D2-B4B1-5FBC35F5238B@microsoft.com...
> 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" <Swiftcode@discussions.microsoft.com> wrote in message
> > news:A165DE90-C3F6-4421-8084-0C00610499CE@microsoft.com...
> > > 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
> >
> >
> >