ComboBox doesn't select value on Click

[wist]

Hey guys,

I have a form with a couple of ComboBoxes and one of them is being populated based on files in directories. It populates just fine but I am unable to select any value in R_Rec by clicking on it, only using arrow keys. I have found out that the line of code that's preventing me to click is highlighted in red. When I remove it I can select the values by clicking but the list just keeps populating and stacking the same values below so it seems it's needed.

[COLOR=#0000cd]Private Sub UserForm_Initialize()[/COLOR]

     R_Country.List = Array("One", "Two", "Three")

[COLOR=#0000cd]End Sub[/COLOR]


[COLOR=#0000cd]Private Sub R_Rec_DropButton Click()[/COLOR]


Dim Rfolder, Rec As String


[B][COLOR=#ff0000]R_Rec.Clear[/COLOR][/B]


    If R_Country.Value = "One" Then Rfolder = "C:\One\"
    If R_Country.Value = "Two" Then Rfolder = "C:\Two\"
    If R_Country.Value = "Three" Then Rfolder = "C:\Three\"
   
    Rec = Dir(Rfolder & "*")
         Do While Rec <> ""
              R_Rec.AddItem Rec
              Rec = Dir
         Loop

[COLOR=#0000cd]End Sub[/COLOR]

Anu suggestions? Thanks

 

[gallen]

Hello, you need to move the clear. Not sure why you need to repopulate every time the dropdown is clicked though

Private Sub R_Rec_DropButton_Click()

Dim Rfolder, Rec As String

    If R_Country.Value = "One" Then Rfolder = "C:\One\"
    If R_Country.Value = "Two" Then Rfolder = "C:\Two\"
    If R_Country.Value = "Three" Then Rfolder = "C:\Three\"
    
[COLOR=#ff0000]    R_Rec.Clear[/COLOR]
   
    Rec = Dir(Rfolder & "*")
         Do While Rec <> ""
              R_Rec.AddItem Rec
              Rec = Dir
         Loop

End Sub

 

[wist]

Hi gallen, moving it didn't work ...I need to repopulate every time R_Country is changed because they are different folder -> different files ....When R_Country is not changed R_Rec doesn't repopulate when clicked because form is not registering any changes.

 

[wist]

any ideas on this guys please ?

 

[Fluff]

Shouldn't that code be in an R_Country_Click event rather than the R_Rec

 

[wist]

there are other comboboxes in the code so I need to trigger the event when clicking on the R_Rec

 

[Fluff]

I tend not to use the dropButton event, so not sure what is happening.
But if you put your code in a click event for the R_country combo then it will do what you want

Private Sub R_Country_Click()
Dim Rfolder, Rec As String


R_Rec.Clear


    If R_Country.Value = "One" Then Rfolder = "C:\One\"
    If R_Country.Value = "Two" Then Rfolder = "C:\Two\"
    If R_Country.Value = "Three" Then Rfolder = "C:\Three\"
   
    Rec = Dir(Rfolder & "*")
         Do While Rec <> ""
              R_Rec.AddItem Rec
              Rec = Dir
         Loop
End Sub

 

[mikerickson]

there are other comboboxes in the code so I need to trigger the event when clicking on the R_Rec

Other combo boxes in what code?

The time to make a userform consistent (i.e. so all controls point to the same situation, folder in this case) is as soon as possible. If R_Country is the control which determines what folder, all the controls should be adjusted to that folder as soon as R_Country is changed. That would be in either the R_Country_Click event or the R_Country_Change event.

 

[wist]

Other combo boxes in what code?

The time to make a userform consistent (i.e. so all controls point to the same situation, folder in this case) is as soon as possible. If R_Country is the control which determines what folder, all the controls should be adjusted to that folder as soon as R_Country is changed. That would be in either the R_Country_Click event or the R_Country_Change event.

there are 3 comboboxes and each determines the locations (i didn't mention them in here to make the code more simple) so anytime any one of them is changed I need to refresh the data in R_Rec. That is why I used the 'R_Rec_DropButton_Click()'

So the code looks more like this:

[COLOR=#0000cd]Private Sub UserForm_Initialize()[/COLOR]

     R_Country.List = Array("One", "Two", "Three")
     R_PeriodBox.List = Array("april", "May","June")
     R_FYbox.List = Array("2017", "2018","2019") 

[COLOR=#0000cd]End Sub[/COLOR]


[COLOR=#0000cd]Private Sub R_Rec_DropButton Click()[/COLOR]
Dim Rfolder, Rec As String

[B][COLOR=#ff0000]R_Rec.Clear[/COLOR][/B]

    If R_Country.Value = "One" Then Rfolder = "C:\One\" & R_FYbox.Value & "\" & R_PeriodBox.Value & "\"
    If R_Country.Value = "Two" Then Rfolder = "C:\Two\" & R_FYbox.Value & "\" & R_PeriodBox.Value & "\"
    If R_Country.Value = "Three" Then Rfolder = "C:\Three\" & R_FYbox.Value & "\" & R_PeriodBox.Value & "\"
   
    Rec = Dir(Rfolder & "*")
         Do While Rec <> ""
              R_Rec.AddItem Rec
              Rec = Dir
         Loop

[COLOR=#0000cd]End Sub
[/COLOR]

I tend not to use the dropButton event, so not sure what is happening.
But if you put your code in a click event for the R_country combo then it will do what you want

Private Sub R_Country_Click()
Dim Rfolder, Rec As String


R_Rec.Clear


    If R_Country.Value = "One" Then Rfolder = "C:\One\"
    If R_Country.Value = "Two" Then Rfolder = "C:\Two\"
    If R_Country.Value = "Three" Then Rfolder = "C:\Three\"
   
    Rec = Dir(Rfolder & "*")
         Do While Rec <> ""
              R_Rec.AddItem Rec
              Rec = Dir
         Loop
End Sub

Fluff, thanks for now I used R_Country_Click() as you suggested and for the other 2 comboboxes I applied following:

Private Sub R_FYbox_Click()

    Call R_Country_Click


End Sub


Private Sub R_PeriodBox_Click()
    
    Call R_Country_Click


End Sub

This fixes my problem but it's not really elegant in my opinion so if anyone has an idea how to fix it in the 'Private Sub R_Rec_DropButton Click()' event as I inteded in the beggining or has something better, I'm all eyes

 

[Fluff]

If you will always be selecting a value from all three combos then you can put this code

Dim Rfolder, Rec As String

[B][COLOR=#ff0000]R_Rec.Clear[/COLOR][/B]

    If R_Country.Value = "One" Then Rfolder = "C:\One\" & R_FYbox.Value & "\" & R_PeriodBox.Value & "\"
    If R_Country.Value = "Two" Then Rfolder = "C:\Two\" & R_FYbox.Value & "\" & R_PeriodBox.Value & "\"
    If R_Country.Value = "Three" Then Rfolder = "C:\Three\" & R_FYbox.Value & "\" & R_PeriodBox.Value & "\"
   
    Rec = Dir(Rfolder & "*")
         Do While Rec <> ""
              R_Rec.AddItem Rec
              Rec = Dir
         Loop

into the R_PeriodBox click event & leave the others blank.