Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

get path file in label based on selected combobox



 I  try  getting path for  specific  file is  selected by combobox2  into label1  and  hyperlink  to  open  the  file . I  added  some lines  , but  it  gives error  object required in this  line 

For Each obfile In folderspec.Files

this  I  added  in  ComboBox1_Change()

For Each obfile In folderspec.Files
    Label1.Caption = obfile.path

and  add this  to  hyperlink 

Private Sub Label1_Click()
On Error Resume Next
ThisWorkbook.FollowHyperlink Address = Me.Label1.Caption
End Sub




Selected Answer


To get the file selected in ComboBox 2 into Label1, you can use this:

Private Sub ComboBox2_Change()

    Dim folderspec As String

    folderspec = "D:\All website photo" & "\" & ComboBox1.Value

    UserForm1.Label1.Caption = folderspec & "\" & ComboBox2.Value

End Sub
but I've removed the second loop in the ComboBox1 code and provided code to expand the ComboBox2 list (once Combobox1 has a folder selected):
Private Sub ComboBox1_Change()

    Dim fs, f, f1, fc, s
    Dim folderspec

    folderspec = "D:\All website photo" & "\" & ComboBox1.Value

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)

    Set fc = f.Files


    For Each f1 In fc

        ComboBox2.AddItem f1.Name

    Next f1

    ComboBox2.SetFocus 'get into CB2...
    'Application.SendKeys "^{F4}"
    Application.SendKeys "%{down}" ' and expand list

End Sub
It's a bit unusual to open a file from a label (I'd normally use a CommandButton to do that) but if you do, the FollowHyperlink method will fail if there are spaces in the full file path. This code replaces them with %20 so the file will open:
Private Sub Label1_Click()
On Error Resume Next
'swap spaces in file path/name and open in new window
ThisWorkbook.FollowHyperlink (Replace(UserForm1.Label1, " ", "%20"))

End Sub
Not sure what your SpinButtons are for but hopefully this answered your question. The attached file includes the modified code above.


p.s. I suggest you change your file names as you alter the code- it helps you track progress (and recover if you have a system crash)
John_Ru (rep: 2867) Nov 17, '21 at 4:55 am
Not sure what your SpinButtons are for
to  show  the  next  or  previous the   path  in label1  and  file  in combobox2
hopefully this answered your question
yes you really did it  based on my OP.
thanks  very  much !
Kalil (rep: 22) Nov 17, '21 at 5:14 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login