ActiveX Control Combo Box

[K]

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.

 

[Kenneth Hobson]

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

 

[JLGWhiz]

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]

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.

 

[Kenneth Hobson]

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

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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

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.

 

[Kenneth Hobson]

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

 

[K]

If you want to use Bernie's WMI method, here is an example.  Notice thelink
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.

 

[JLGWhiz]

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