dynamic file path in VBA

[kelly mort]

Hello,
I have this file path to access my pictures folder. I am trying to do something a bit weird here: if the first item in a combobox named cmb1 is selected, I wanna use the folder "Picture1", if the second item is selected, then I wanna select the folder "Picture2 " etc.

fPath = ThisWorkbook.Path & "\" & "Picture1"

How do I get this done from the line above or anything cool?
Regards
Kelly

 

[MickG]

Perhaps something like this:-

[COLOR="Navy"]Sub[/COLOR] MG20Sep18
[COLOR="Navy"]Dim[/COLOR] Fld [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] fPath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] ComboBox1.Value
    [COLOR="Navy"]Case[/COLOR] "pic1", "pic2", "pic3", "pic4", "pic5": Fld = "Picture1"
    [COLOR="Navy"]Case[/COLOR] "pic6", "pic7", "Pic8", "pic9", "pic10": Fld = "Picture2"
    [COLOR="Navy"]Case[/COLOR] "pic11", "pic12", "Pic13", "pic14", "pic15": Fld = "Picture3"
[COLOR="Navy"]End[/COLOR] Select
fPath = ThisWorkbook.Path & "\" & Fld
MsgBox fPath
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Zubair Muhammad]

Hi Kelly,

If your combobox is a userform control or an activeX control then

fPath = ThisWorkbook.Path & "\" & "Picture" & cmb1.listindex + 1

 

[Zubair Muhammad]

If it is a form control and suppose A1 is the linked cell then

fPath = ThisWorkbook.Path & "\" & "Picture" & A1

 

[kelly mort]

Perhaps something like this:-

[COLOR="Navy"]Sub[/COLOR] MG20Sep18
[COLOR="Navy"]Dim[/COLOR] Fld [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] fPath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] ComboBox1.Value
    [COLOR="Navy"]Case[/COLOR] "pic1", "pic2", "pic3", "pic4", "pic5": Fld = "Picture1"
    [COLOR="Navy"]Case[/COLOR] "pic6", "pic7", "Pic8", "pic9", "pic10": Fld = "Picture2"
    [COLOR="Navy"]Case[/COLOR] "pic11", "pic12", "Pic13", "pic14", "pic15": Fld = "Picture3"
[COLOR="Navy"]End[/COLOR] Select
fPath = ThisWorkbook.Path & "\" & Fld
MsgBox fPath
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Hello MickG,
I will test this when I get home. Buy the Pic1 etc, what are they?
Thanks.

 

[kelly mort]

Hi Kelly,

If your combobox is a userform control or an activeX control then

fPath = ThisWorkbook.Path & "\" & "Picture" & cmb1.listindex + 1

Hello Zubair,
I think I understand your code and will do what I wanna achieve. Will get you posted when I get home and test it. Thanks
Kelly

PS.
If there is an error that's where cmb1 is blank, which error handler should I use? Maybe

Exit Sub

 

[MickG]

Your thread specifies :- "I wanna use the folder "Picture1", if the second item is selected, then I wanna select the folder "Picture2 " etc. " ", I assumed that these are the folder Names and not the name of the pictures within some Folder.

Assuming that is the case, the actual Picture files would be names in your combobox i.e. "Pic1,Pic2,Pic3 etc and held in the various Folders "Picture1, "Picture2" etc.
Then the Complete Path should be something like below:-

fPath = ThisWorkbook.Path & "\" & Fld & "\" & ComboBox1.Value & ".jpg"

 

[kelly mort]

Hi Kelly,

If your combobox is a userform control or an activeX control then

fPath = ThisWorkbook.Path & "\" & "Picture" & cmb1.listindex + 1

Okay. I have tested it and it has worked just fine. I don't think I even need that error handler. I already have others taking care of the errors that may arise. Thanks again
Kelly

 

[kelly mort]

Your thread specifies :- "I wanna use the folder "Picture1", if the second item is selected, then I wanna select the folder "Picture2 " etc. " ", I assumed that these are the folder Names and not the name of the pictures within some Folder.

Assuming that is the case, the actual Picture files would be names in your combobox i.e. "Pic1,Pic2,Pic3 etc and held in the various Folders "Picture1, "Picture2" etc.
Then the Complete Path should be something like below:-

fPath = ThisWorkbook.Path & "\" & Fld & "\" & ComboBox1.Value & ".jpg"

Hello MickG,
The combobox does not house a name of the pictures. It has kofi relation. The only logic behind my request which I termed "weird" is to reference a particular folder based on the first item 8n the combobox. I think my first explanation was not that clear. Haha. Sometimes I find it very tough to communicate the exact thing on my mind. Still working on it though.

Zubair's code solved it for me.
Thanks

 

[MickG]

No problem!!
Glad you now have it fixed !!