use ComboBox value to select range and then use selected range tocreate a chart

[Jerry]

Hi,

Excel file used in this post:
https://docs.google.com/leaf?id=0B9...mMtOWU1MGM5M2I1MTEy&hl=en_US&authkey=CIXSoecG


I am trying to use VBA to achieve the following purpose:

1, a UserForm pops up once I open a workbook, and the value on the drop-down list of 2 ComboBoxes on the UserForm will be automatically populated. (I have designed the UserForm, open VBE to view it)

2, on the UserForm, after I choose "starting year month" and "ending year month", and then click "Select Duration" CommandButton, the VBA code will select a range on sheet "data" and use the range as the source data to createa chart on sheet "data". For example, if I select "2009 Jan" as "startingyear month" and "2009 Apr" as "ending year month", 4 rows will be selectedto be used as data range for the chart, see the area highlight in green. (the rudimentary VBA is also in the file mentioned at the top)

My problem is I don't know how to use the values obtained from ComboBox drop-down selection to set up the range. In my VBA code, there is a line to select a fixed range, see below

Set rngChtData = ActiveSheet.Range("c1:f49")

If I know how to replace c1 with the address of the cell which contains thevalue of "starting year month", and replace f49 with the address of the cell that's 3 columns to the right of the cell which contains the value of "ending year month", I will be getting very closer to my goal.

Any help is highly appreciated!

Thanks!
Jerry

 

[GS]

How about a slightly different approach?

On your userform:
Rename userform fPeriodDuration
Rename str_pt cboPeriodStart
Rename end_pt cboPeriodEnd
Rename CommandButton1 cmdSelectDuration
Rename CommandButton2 cmdCancel

With cboPeriodStart AND cboPeriodEnd:
Set ColumnCount to 2
Set BoundColumn to 2
Set ColumnWidths to 50,0

In the userform code module, replace all your code with the following:

 

[GS]

Sorry.., hit the wrong button!

Option Explicit


Private Sub cboPeriodEnd_Change()
Debug.Print Me.cboPeriodEnd.Value
End Sub

Private Sub cboPeriodStart_Change()
Debug.Print Me.cboPeriodStart.Value
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdSelectDuration_Click()
Dim sAddress As String
sAddress = Me.cboPeriodStart.Value & ":" & Me.cboPeriodEnd.Value
Application.Goto Worksheets("data").Range(sAddress).Resize(, 4)
End Sub

Private Sub UserForm_Initialize()
Dim rngSource As Range
Dim vListData() As String
Dim r As Long, lRows As Long, lCols As Long

Set rngSource = Worksheets("data").Range("yrmth")
With rngSource
lRows = .Rows.Count: lCols = .Columns.Count
End With

ReDim vListData(lRows, lCols)
For r = 1 To UBound(vListData)
vListData(r, 0) = rngSource.Cells(r).Value
vListData(r, 1) = rngSource.Cells(r).Address
Next 'r
Me.cboPeriodStart.List = vListData
Me.cboPeriodEnd.List = vListData
End Sub

 

[Jerry]

Gary,

Your code works fantastic, thank you so much for your awesome help, I
really appreciate it!

Don't mean to be greedy, I wonder how to integrate your code with the
code (if needed) in my original file to create the chart with 3 series
(H3351, H3335, S3521) in it. I know it may be very easy, but I'm not
sure how to do so, as I'm a total VBA newbie (you may have figured it
out right after you saw my original post).

Thank you again for your time!

Jerry