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

Hi,

Excel file used in this post:
https://docs.google.com/leaf?id=0B9F...thkey=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 create a chart on sheet "data". For example, if I select "2009 Jan" as "starting year month" and "2009 Apr" as "ending year month", 4 rows will be selected to 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 the value 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

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

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:

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

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

On Jun 21, 7:16*pm, GS wrote:
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

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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

Jerry formulated the question :
Your code works fantastic, thank you so much for your awesome help, I
really appreciate it!

You're welcome! ..always glad to help!


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).

I did not go there because I rarely use charts. You can turn on the
macro recorder and see what generates while you do it manually, then
clean the resulting code up for reuse. I'll help you with that if you
post the code generated by the macro recorder.

Good luck...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc