Dears,
I am looking for vba codes in switching two dynamic ranges in one combobox, i set one range under column A (e.g. 1,2,3,4,5) and another range under column D(a,b,c,d,e). And there are two command button ("letter" and "number") and one combobox. When i click the "letter" button, the range of the combobox will be letter; when i click the "number" button, the range of the combobox will be number. My codes are below:
Private Sub Cmd_letter_Click()
Dim x As String
x = "d1"
numberorletter.Clear
Call numberorletterprocedure
End Sub
Private Sub Cmd_number_Click()
Dim x As String
x = "a1"
numberorletter.Clear
Call numberorletterprocedure
End Sub
Private Sub numberorletterprocedure()
Dim x As String
Dim numberorletter As Range
Dim ws As Worksheet
Set ws = sheet1
Set numberorletter.List = sheet1.Range(x, sheet1.Range(x).End(xlDown)).Value
End Sub
However, it don't work, could you let me know which part i made the mistakes? And how it will look like if it is done by create a function, not procedure? Thanks!
Try this
Private Sub Cmd_letter_Click()
Dim x As String
x = "d1"
Call numberorletterprocedure(x)
End Sub
Private Sub Cmd_number_Click()
Dim x As String
x = "a1"
Call numberorletterprocedure(x)
End Sub
Private Sub numberorletterprocedure(x As String)
Dim ws As Worksheet
Set ws = Sheet1
'* assuming your combobox name is numberorletter
'* if not change it to the correct name
numberorletter.List = ws.Range(x, ws.Range(x).End(xlDown)).Value
End Sub