Dynamic range based on vba selection

[Annpan79]

Hi,

I hope i can explain this as well as it sounds in my head.

I have a worksheet called 'requests', column A in requests. The requests column has values starting Q***** or C*****.

I have created a form which contains a combo box and two radio button, one called change and one called requests.

What i'd like, is for the combo box to display values from column A based on which radio button is selected. I would like only those values beginning with C to be displayed when the changes radio is selected and when requests is selected i would like the combo box to disappear (i've been able to do this by doing visible = false). I'm really struggling with the dynamic range though.
If anybody could help, i would be most grateful

thanks

 

[Annpan79]

Is anyone able to help me with this at all?

 

[Weaver]

Is column A sorted, or are the Q codes and C codes mixed up?

With the combobox being in a form, you might be better off using .additem to populate it, rather than setting its rowsource

 

[Weaver]

Something like this perhaps:

Private Sub change_Click()
    With Me.ComboBox1
        .Clear
        For Each f In Range("A2", Cells(Rows.Count, "A").End(xlUp))
            If Left(f, 1) = "C" Then
                .AddItem f
            End If
        Next
    End With
End Sub

 

[Annpan79]

Something like this perhaps:

Private Sub change_Click()
    With Me.ComboBox1
        .Clear
        For Each f In Range("A2", Cells(Rows.Count, "A").End(xlUp))
            If Left(f, 1) = "C" Then
                .AddItem f
            End If
        Next
    End With
End Sub

Hi,

Thank you for your help. Column A is not sorted, totally random.

\in the code above, what does the f stand for? Sorry if i'm being a bit thick. Also, where it state range "a2", can i replace this with the range name?

Thanks,

Ann

 

[Weaver]

Don't worry about what the f stands for, it's just a variable representing in turn each value in the range.

The reason why I used the range as I did was to avoid the need to create the named range in the first place as I thought this would be more useful, but if you still want to use it, replace

Range("A2", Cells(Rows.Count, "A").End(xlUp))

with

Range("myNamedRange")

or whatever it's called.

 

[Annpan79]

Don't worry about what the f stands for, it's just a variable representing in turn each value in the range.

The reason why I used the range as I did was to avoid the need to create the named range in the first place as I thought this would be more useful, but if you still want to use it, replace

Range("A2", Cells(Rows.Count, "A").End(xlUp))

with

Range("myNamedRange")

or whatever it's called.

Hello, apologies for teh delay in getting back - I've only just returned to the office.

I've put in a range name for now, as I have a number of worksheets so its just for my sanity really.

This is the code that I have put in;

Private Sub OptChgReq_Click()
CmbChg.Visible = True
With Me.CmbChg
.Clear
For Each f In Range("reqno", Cells(Rows.Count, "B").End(x1up))
If Left(CmbChg, 1) = "C" Then
.AddItem CmbChg
End If
Next
End With

End Sub

The problem is, that it is failing with 'Run time error '1004': Application-defined or object-defined error.

Any ideas on what it could be?

 

[Annpan79]

Hello, apologies for teh delay in getting back - I've only just returned to the office.

I've put in a range name for now, as I have a number of worksheets so its just for my sanity really.

This is the code that I have put in;

Private Sub OptChgReq_Click()
CmbChg.Visible = True
With Me.CmbChg
.Clear
For Each f In Range("reqno", Cells(Rows.Count, "B").End(x1up))
If Left(CmbChg, 1) = "C" Then
.AddItem CmbChg
End If
Next
End With

End Sub

The problem is, that it is failing with 'Run time error '1004': Application-defined or object-defined error.

Any ideas on what it could be?

Ignore the above post - I was being stupid! After re-entering it properly (as you originally put it) - it now works!