Hi

I would like to create a popup window in VBA in Excel that will let me
choose any excel sheet and then I need another popup window that will let me
choose a range from the sheet I haven chosen so i can ceate a chart.
Thanks for any help.

Susanne

you could use a userform onto which you drop a refedit box and a combobox
populate the combo with the sheets...here's an quick example
Add a userform ( called userform1)
onto userform1 add a combobox ( called combobox1) and below it, a refedit
control
Add this code to the form's code page:

Option Explicit
Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In Worksheets
ComboBox1.AddItem ws.Name
Next
End Sub
Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = -1 Then Exit Sub
Worksheets(ComboBox1.Value).Activate
End Sub

First, when you run the form, the combobox gets populated with all th
eworksheet names.

Selecting a sheetname in the combobox, fires the combobox's chnage event,
ant that sheet gets activated
Clicking the refedit control allows you to select a range on whatever is the
active sheet.





"Susanne" wrote:

Hi

I would like to create a popup window in VBA in Excel that will let me
choose any excel sheet and then I need another popup window that will let me
choose a range from the sheet I haven chosen so i can ceate a chart.
Thanks for any help.

Susanne

Hi again

I have the userform working but how do I get the range that I have selected
into a chart that I want my macro to make.
This is how it looks (part of my macro) right now with one particular sheet
selected. I now want the popup window to let me select a sheet and then a
range and then draw a chart.

Sheets("TR6_BP3_BP5_122_125_20050408_00").Select
Range("N:N,S:S").Select
Range("S2").Activate
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData
Source:=Sheets("TR6_BP3_BP5_122_125_20050408_00"). _
Range("N1:N9261,S1:S9261"), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = _
"=TR6_BP3_BP5_122_125_20050408_00!R2C19:R9261C 19"
ActiveChart.SeriesCollection(1).Values = _
"=TR6_BP3_BP5_122_125_20050408_00!R2C14:R9261C 14"
ActiveChart.Location Whe=xlLocationAsNewSheet
ActiveChart.ChartTitle.Select
Selection.Characters.Text = "Capacity test 067L5640
and so on...

Susanne

"Patrick Molloy" skrev:

you could use a userform onto which you drop a refedit box and a combobox
populate the combo with the sheets...here's an quick example
Add a userform ( called userform1)
onto userform1 add a combobox ( called combobox1) and below it, a refedit
control
Add this code to the form's code page:

Option Explicit
Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In Worksheets
ComboBox1.AddItem ws.Name
Next
End Sub
Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = -1 Then Exit Sub
Worksheets(ComboBox1.Value).Activate
End Sub

First, when you run the form, the combobox gets populated with all th
eworksheet names.

Selecting a sheetname in the combobox, fires the combobox's chnage event,
ant that sheet gets activated
Clicking the refedit control allows you to select a range on whatever is the
active sheet.





"Susanne" wrote:

Hi

I would like to create a popup window in VBA in Excel that will let me
choose any excel sheet and then I need another popup window that will let me
choose a range from the sheet I haven chosen so i can ceate a chart.
Thanks for any help.

Susanne

Susanne,
This works OK for me.


This is code in the Userform1

Private Sub RefEdit1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Set Rng1 = Range(RefEdit1.Value)
End Sub

Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In Worksheets
ComboBox1.AddItem ws.Name
Next
End Sub
Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = -1 Then Exit Sub
Worksheets(ComboBox1.Value).Activate
End Sub

This is in the main module:

Option Explicit
Public Rng1 As Range

Sub Testme()
UserForm1.Show
MsgBox Rng1.Address 'Range from RefEdIt
Call PlotChart
End Sub

Sub PlotChart()

Rng1.Select ' Range from RefEdit inserted in SetSourceData below
Range("S2").Activate
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Rng1, PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsNewSheet
ActiveChart.ChartTitle.Characters.Text = "Capacity test 067L5640
End Sub

I don't think you need the ActiveChart.SeriesCollection(1). statements (but
could be wrong!). It works OK without them!

HTH

"Susanne" wrote:

Hi again

I have the userform working but how do I get the range that I have selected
into a chart that I want my macro to make.
This is how it looks (part of my macro) right now with one particular sheet
selected. I now want the popup window to let me select a sheet and then a
range and then draw a chart.

Sheets("TR6_BP3_BP5_122_125_20050408_00").Select
Range("N:N,S:S").Select
Range("S2").Activate
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData
Source:=Sheets("TR6_BP3_BP5_122_125_20050408_00"). _
Range("N1:N9261,S1:S9261"), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).XValues = _
"=TR6_BP3_BP5_122_125_20050408_00!R2C19:R9261C 19"
ActiveChart.SeriesCollection(1).Values = _
"=TR6_BP3_BP5_122_125_20050408_00!R2C14:R9261C 14"
ActiveChart.Location Whe=xlLocationAsNewSheet
ActiveChart.ChartTitle.Select
Selection.Characters.Text = "Capacity test 067L5640
and so on...

Susanne

"Patrick Molloy" skrev:

you could use a userform onto which you drop a refedit box and a combobox
populate the combo with the sheets...here's an quick example
Add a userform ( called userform1)
onto userform1 add a combobox ( called combobox1) and below it, a refedit
control
Add this code to the form's code page:

Option Explicit
Private Sub UserForm_Initialize()
Dim ws As Worksheet
For Each ws In Worksheets
ComboBox1.AddItem ws.Name
Next
End Sub
Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = -1 Then Exit Sub
Worksheets(ComboBox1.Value).Activate
End Sub

First, when you run the form, the combobox gets populated with all th
eworksheet names.

Selecting a sheetname in the combobox, fires the combobox's chnage event,
ant that sheet gets activated
Clicking the refedit control allows you to select a range on whatever is the
active sheet.





"Susanne" wrote:

Hi

I would like to create a popup window in VBA in Excel that will let me
choose any excel sheet and then I need another popup window that will let me
choose a range from the sheet I haven chosen so i can ceate a chart.
Thanks for any help.

Susanne