Hi all, I have ActiveX Control Combo Box on my sheet. Is there any
way or macro that when I click that Combo Box drop down button then it
show me all the drives list on computer in Combo Box List index Like
( "C:\" , "D:\" , "E:\" etc) or is there any other control in which i
can achive this. Please note i dont need dialog box popping up to see
the list of drives i just want this list to appear (preferable Comb
Box) in control on sheet. Please can any friend can help.

Add the reference as commented.
Private Sub ComboBox1_GotFocus()
ComboBox1.List = DriveList
End Sub

Function DriveList() As Variant
'add, Tools References... Microsoft Scipting Runtime
Dim fso As New FileSystemObject
Dim dic As New Scripting.Dictionary
Dim d As Object
For Each d In fso.Drives
dic.Add d.driveletter, vbNullString
Next d
DriveList = dic.Keys
End Function

Put this in your UserForm code module:

Private Sub UserForm_Initialize()
Dim fs, d, dc
Set fs = CreateObject("Scripting.FileSystemObject")
Set dc = fs.Drives
For Each d In dc
UserForm1.ComboBox1.AddItem d & " \ "
Next
End Sub


"K" wrote:

Hi all, I have ActiveX Control Combo Box on my sheet. Is there any
way or macro that when I click that Combo Box drop down button then it
show me all the drives list on computer in Combo Box List index Like
( "C:\" , "D:\" , "E:\" etc) or is there any other control in which i
can achive this. Please note i dont need dialog box popping up to see
the list of drives i just want this list to appear (preferable Comb
Box) in control on sheet. Please can any friend can help.

On Feb 16, 3:52*pm, Kenneth Hobson
wrote:
Add the reference as commented.
Private Sub ComboBox1_GotFocus()
* ComboBox1.List = DriveList
End Sub

Function DriveList() As Variant
'add, Tools References... Microsoft Scipting Runtime
* Dim fso As New FileSystemObject
* Dim dic As New Scripting.Dictionary
* Dim d As Object
* For Each d In fso.Drives
* * dic.Add d.driveletter, vbNullString
* Next d
* DriveList = dic.Keys
End Function

Thanks for replying kenneth, your code is perfect but just small
question that your code is giving just the drives letters but if i
want letters and their names or description like ("F:\ [Home drive]")
etc then how can i get that.

Not sure where the [Home Drive] would come from. We can use the properties
of the fso drives collections to get some parts. Some API methods might be a
bit better if I know which part you needed. FSO usually makes the API
methods unneeded.

To review the methods and properties of fso, get the help file.
http://tinyurl.com/5ts6r8

This doesn't do anything for mine but it might work for you.
Function DriveList() As Variant
'add, Tools References... Microsoft Scipting Runtime
Dim fso As New FileSystemObject
Dim dic As New Scripting.Dictionary
Dim d As Object
Dim s As String, n As String
For Each d In fso.Drives
n = ""
If d.DriveType = 3 Then
n = d.ShareName
ElseIf d.IsReady Then
n = d.VolumeName
End If
dic.Add d.driveletter & " - " & n, vbNullString
Next d
DriveList = dic.Keys
End Function

The code below requires a reference to the MS WMI Scripting Library

HTH,
Bernie
MS Excel MVP


Sub IdentifyDrives()
Dim colDisks As SWbemObjectSet
Dim objDisk As SWbemObject
Dim myDisks() As String
Dim i As Integer

Set colDisks = GetObject( _
"Winmgmts:").ExecQuery("Select * from Win32_LogicalDisk")
i = 0
ReDim myDisks(1 To colDisks.Count)
For Each objDisk In colDisks
i = i + 1
myDisks(i) = objDisk.DeviceID
Next objDisk
For i = 1 To UBound(myDisks)
MsgBox "Drive #" & i & " is " & myDisks(i)
Next i

End Sub
"K" wrote in message
...
Hi all, I have ActiveX Control Combo Box on my sheet. Is there any
way or macro that when I click that Combo Box drop down button then it
show me all the drives list on computer in Combo Box List index Like
( "C:\" , "D:\" , "E:\" etc) or is there any other control in which i
can achive this. Please note i dont need dialog box popping up to see
the list of drives i just want this list to appear (preferable Comb
Box) in control on sheet. Please can any friend can help.

Again, this code requires a reference to the MS WMI Scripting Library.

Bernie


Sub IdentifyDriveLetterAndTypes()
Dim colDisks As SWbemObjectSet
Dim objDisk As SWbemObject

Set colDisks = GetObject( _
"Winmgmts:").ExecQuery("Select * from Win32_LogicalDisk")
For Each objDisk In colDisks
UserForm1.ComboBox1.AddItem _
objDisk.DeviceID & " - " & DriveMessage(objDisk.driveType)
Next objDisk

Load UserForm1
UserForm1.Show
End Sub

Function DriveMessage(myType) As String

Select Case myType
Case 1
DriveMessage = "Drive type could not be determined."
Case 2
DriveMessage = "Removable drive"
Case 3
DriveMessage = "Local hard disk"
Case 4
DriveMessage = "Network disk"
Case 5
DriveMessage = "Compact disk"
Case 6
DriveMessage = "RAM disk"
Case Else
DriveMessage = "Drive type could not be determined."
End Select

End Function

"K" wrote in message
...
On Feb 16, 3:52 pm, Kenneth Hobson
wrote:
Add the reference as commented.
Private Sub ComboBox1_GotFocus()
ComboBox1.List = DriveList
End Sub

Function DriveList() As Variant
'add, Tools References... Microsoft Scipting Runtime
Dim fso As New FileSystemObject
Dim dic As New Scripting.Dictionary
Dim d As Object
For Each d In fso.Drives
dic.Add d.driveletter, vbNullString
Next d
DriveList = dic.Keys
End Function

Thanks for replying kenneth, your code is perfect but just small
question that your code is giving just the drives letters but if i
want letters and their names or description like ("F:\ [Home drive]")
etc then how can i get that.

If you want to use Bernie's WMI method, here is an example. Notice the link
to a site that shows the properties for the object. I also commented some
other ways to tack on parts that you might want.

Private Sub ComboBox1_GotFocus()
'ComboBox1.List = DriveList
ComboBox1.List = GetDrivesByWMI
End Sub

Function GetDrivesByWMI() As Variant
'add, Tools References... Microsoft WMI Scripting Library
'http://pageofwords.com/blog/content/binary/TechEdTranscript.txt
Dim colDisks As SWbemObjectSet
Dim objDisk As SWbemObject
Dim myDisks
Dim i As Integer

Set colDisks = GetObject( _
"Winmgmts:").ExecQuery("Select * from Win32_LogicalDisk")

i = 0
ReDim myDisks(1 To colDisks.Count)
For Each objDisk In colDisks
i = i + 1
myDisks(i) = objDisk.deviceid
'myDisks(i) = objDisk.DeviceID & " - " & objDisk.Description
'myDisks(i) = objDisk.deviceid & " - " & objDisk.DriveType
'myDisks(i) = objDisk.deviceid & " - " & objDisk.VolumeName
Next objDisk
GetDrivesByWMI = myDisks
End Function

On 16 Feb, 18:40, Kenneth Hobson
wrote:
If you want to use Bernie's WMI method, here is an example. *Notice the link
to a site that shows the properties for the object. *I also commented some
other ways to tack on parts that you might want.

Private Sub ComboBox1_GotFocus()
* 'ComboBox1.List = DriveList
* ComboBox1.List = GetDrivesByWMI
End Sub

Function GetDrivesByWMI() As Variant
'add, Tools References... Microsoft WMI Scripting Library
'http://pageofwords.com/blog/content/binary/TechEdTranscript.txt
* Dim colDisks As SWbemObjectSet
* Dim objDisk As SWbemObject
* Dim myDisks
* Dim i As Integer

* Set colDisks = GetObject( _
* * * "Winmgmts:").ExecQuery("Select * from Win32_LogicalDisk")

* i = 0
* ReDim myDisks(1 To colDisks.Count)
* For Each objDisk In colDisks
* * * i = i + 1
* * * myDisks(i) = objDisk.deviceid
* * * 'myDisks(i) = objDisk.DeviceID & " - " & objDisk.Description
* * * 'myDisks(i) = objDisk.deviceid & " - " & objDisk.DriveType
* * * 'myDisks(i) = objDisk.deviceid & " - " & objDisk.VolumeName
* Next objDisk
* GetDrivesByWMI = myDisks
End Function

Thanks guys your codes been very helpful.

This code is modified to use a ComboBox on a sheet from the Control Toolbox
and will identify the drive type.

Sub listDrv() 'Identifies drives and lists them in ComboBox
Dim fs, d, dc
Set fs = CreateObject("Scripting.FileSystemObject")
Set dc = fs.Drives
For Each d In dc
Select Case d.DriveType
Case 0: t = "Unknown"
Case 1: t = "Removable"
Case 2: t = "Fixed"
Case 3: t = "Network"
Case 4: t = "CD-ROM"
Case 5: t = "RAM Disk"
End Select
Sheets(1).ComboBox1.AddItem d & " \ " & "- " & t
Next
End Sub

"K" wrote:

On 16 Feb, 18:40, Kenneth Hobson
wrote:
If you want to use Bernie's WMI method, here is an example. Notice the link
to a site that shows the properties for the object. I also commented some
other ways to tack on parts that you might want.

Private Sub ComboBox1_GotFocus()
'ComboBox1.List = DriveList
ComboBox1.List = GetDrivesByWMI
End Sub

Function GetDrivesByWMI() As Variant
'add, Tools References... Microsoft WMI Scripting Library
'http://pageofwords.com/blog/content/binary/TechEdTranscript.txt
Dim colDisks As SWbemObjectSet
Dim objDisk As SWbemObject
Dim myDisks
Dim i As Integer

Set colDisks = GetObject( _
"Winmgmts:").ExecQuery("Select * from Win32_LogicalDisk")

i = 0
ReDim myDisks(1 To colDisks.Count)
For Each objDisk In colDisks
i = i + 1
myDisks(i) = objDisk.deviceid
'myDisks(i) = objDisk.DeviceID & " - " & objDisk.Description
'myDisks(i) = objDisk.deviceid & " - " & objDisk.DriveType
'myDisks(i) = objDisk.deviceid & " - " & objDisk.VolumeName
Next objDisk
GetDrivesByWMI = myDisks
End Function

Thanks guys your codes been very helpful.