Excel combobox Function 1

[bigz60]

I have an excel sheet that has 12 ranges, one for each month. All of the ranges are on the same sheet. All ranges are identical, except for the data.

I have a combobox that has the 12 months as the options.

I am trying to write a function that populates cells on a "recap" sheet based on the month chosen in the combobox.

This function is basically an offset function, but I want the reference to the range be the same as the combobox choice.

Public Function roff(x As Integer, y As Integer)


Select Case ComboBox1.Text

Dim txt1 As String

Case "January"
    txt1 = "January"
Case "February"
    txt1 = "February"
.......

roff = Range(txt1).Range("A1").Offset(x, y).Value
End Select

End Function

I have tested the function without the case and it works fine when I manually set txt1 to "January", so I am thinking it is a problem with the case.

 

[PHV]

Why not simply this ?

Public Function roff(x As Integer, y As Integer)
roff = Range(ComboBox1.Text).Offset(x, y).Value
End Function

Be sure that ComboBox1 returns the expected value:
MsgBox "ComboBox1.Text=" & ComboBox1.Text

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Anthony047]

I think you need neither the combobox nor the Udf to perform what you explain.
-you create the list of months and assign to this range a name, ex MList
-assign the names of the monthly data ranges same as the names in Mlist (ex Mlist=Jan, Feb, Mar etc; one range named Jan, one named Feb, etc)
-on the recap sheet, you select a cell for typing the month, ex B5, and Validate it by Mlist (Select B5, Menu /Data /Validation; choose "List" and type =Mlist as source)
-at this point you can collect data from the various cells of the various ranges with a formula like

=Offset(Indirect(B5),x,y,1,1)

Play with x & y to extract the several cells, as you are prepared to do in ROFF.

Hth, bye.


Anthony047 (GMT+1)

 

[Anthony047]

..and you can even sum several cells, for example this will sum 5 adjacents cells from offset(0,0) of the selected month.

=Sum(Offset(Indirect(B5),0,0,1,5))

Bye.



Anthony047 (GMT+1)

 

[bigz60]

Thank you Anthony. That worked great.