How to pass the value of a combobox to another function

[bigz60]

Hello,
I am new to VBA, so please bear with me.

I have an excel file with 13 different tabs. One for each month, and the last is a summary sheet. All of the monthly sheets are identical, except for the actual data in the cells.

I want the summary sheet to combine and sum the information from only one of the monthly sheets at a time.

I have created a combobox that lists all 12 months. When the user selects the appropriate month, the summary sheet should "grab" the data from the corresponding sheet.

I have created a named range on the monthly sheets, the range encompasses all cells on the sheet, and the range names are Jan, Feb, etc.

I want to create a custom function that has 3 arguments. The first argument will be the appropriate range (chosen from the combobox), and the other two will be offset integers.

I am not having much luck with finding a way to pass the range name from the combobox to the custom function.

Thanks in advance.

 

[SkipVought]

Hi,

"I have an excel file with 13 different tabs. One for each month..."

Why have you purposly made it hard to analyse your data? You have shot yourself in the foot, by chopping similar data up into different locations (tabs).

If your data were in ONE SHEET as ONE TABLE, this could simply and easily be accomplished, without code, using one of many of Excels built-in analysis and reporting features, like the PivotTable wizard, which could give you the answer you are looking for in mere seconds.

I'd strongly recommend a redesign of your workbook as the major effort. Then the REPORTING summarizations will be a breeze!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SoCalAccessPro]

Or better yet, store the data in a database.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[bigz60]

Skip and SoCal,

I agree with what the both of you are saying, but however, I do have a boss. He is the one that requested this layout plan.

I have tried to steer him in a different direction, but he is deadset on the layout stated above.

 

[SoCalAccessPro]

Assuming you have a user form.

Create a combo, cboData and two textboxes, txtOffset1 and txtoffset2.

Now, create a command button and drag it on the form.

The follwing code should give you an idea on how to access the values in the objects.

Private Sub cmdGetComboData_Click()
MsgBox Me.cboData.Value & Me.txtOffset1 & Me.txtOffset2
End Sub

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[SkipVought]

You can do it right and still make your boss happy.

1. Consolidate ALL your data into one table

2. Use the PivotTable Wizard or MS Query to put a report on 12 other sheets as required.

That's the correct way, that will enable the greatest flexibility and facility of Excel's plethora of analysis and reporting features.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]