How do i create a popup window (a macro ?)

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:R9261C19"
ActiveChart.SeriesCollection(1).Values = _
"=TR6_BP3_BP5_122_125_20050408_00!R2C14:R9261C14"
ActiveChart.Location Where:=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 Where:=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:R9261C19"
> ActiveChart.SeriesCollection(1).Values = _
> "=TR6_BP3_BP5_122_125_20050408_00!R2C14:R9261C14"
> ActiveChart.Location Where:=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

Hi again

I have now created the userform and the module but when i start the macro
"Testme" I only get the popup box and then it stops. What am I doing wrong ?
Here is what I wrote in the userform and I made a combobox and a refeditbox.

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 my module (some of it)

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
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Rng1, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.ChartTitle.Characters.Text = "Capacity test 067L5640 #115" &
Chr(10) & " TR6 BP3"
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=36).Font
.Name = "Arial"
.FontStyle = "fed"
.Size = 11.5
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveChart
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Superheat
[°K]"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Capacity in
Tons refrigeration [R22]"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
Selection.Interior.ColorIndex = xlNone
ActiveChart.ChartArea.Select
and so on...


Susanne

"Toppers" wrote:

> 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 Where:=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:R9261C19"
> > ActiveChart.SeriesCollection(1).Values = _
> > "=TR6_BP3_BP5_122_125_20050408_00!R2C14:R9261C14"
> > ActiveChart.Location Where:=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

Hi

Here is a little more explanation. I need to select two different columns
ex. column N and column S, when I do that in the popup box it works ok but
when I then close the box nothing happens. What do I need to do? I appriciate
any help.

Susanne

"Susanne" wrote:

> Hi again
>
> I have now created the userform and the module but when i start the macro
> "Testme" I only get the popup box and then it stops. What am I doing wrong ?
> Here is what I wrote in the userform and I made a combobox and a refeditbox.
>
> 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 my module (some of it)
>
> 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
> Charts.Add
> ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
> ActiveChart.SetSourceData Source:=Rng1, PlotBy:=xlColumns
> ActiveChart.Location Where:=xlLocationAsNewSheet
> ActiveChart.ChartTitle.Characters.Text = "Capacity test 067L5640 #115" &
> Chr(10) & " TR6 BP3"
> Selection.AutoScaleFont = False
> With Selection.Characters(Start:=1, Length:=36).Font
> .Name = "Arial"
> .FontStyle = "fed"
> .Size = 11.5
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = xlAutomatic
> End With
> With ActiveChart
> .Axes(xlCategory, xlPrimary).HasTitle = True
> .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Superheat
> [°K]"
> .Axes(xlValue, xlPrimary).HasTitle = True
> .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Capacity in
> Tons refrigeration [R22]"
> End With
> With ActiveChart.Axes(xlCategory)
> .HasMajorGridlines = True
> .HasMinorGridlines = False
> End With
> With ActiveChart.Axes(xlValue)
> .HasMajorGridlines = True
> .HasMinorGridlines = False
> End With
> ActiveChart.HasLegend = False
> ActiveChart.PlotArea.Select
> With Selection.Border
> .ColorIndex = 16
> .Weight = xlThin
> .LineStyle = xlContinuous
> End With
> Selection.Interior.ColorIndex = xlNone
> ActiveChart.ChartArea.Select
> and so on...
>
>
> Susanne
>
> "Toppers" wrote:
>
> > 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 Where:=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:R9261C19"
> > > ActiveChart.SeriesCollection(1).Values = _
> > > "=TR6_BP3_BP5_122_125_20050408_00!R2C14:R9261C14"
> > > ActiveChart.Location Where:=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

Hi

Do I need to create 2 RefEdit's and i also need the
ActiveChart.SeriesCollection because I need to reverse the columns. How do i
get all this into what I already got ?

Susanne

"Susanne" wrote:

> Hi
>
> Here is a little more explanation. I need to select two different columns
> ex. column N and column S, when I do that in the popup box it works ok but
> when I then close the box nothing happens. What do I need to do? I appriciate
> any help.
>
> Susanne
>
> "Susanne" wrote:
>
> > Hi again
> >
> > I have now created the userform and the module but when i start the macro
> > "Testme" I only get the popup box and then it stops. What am I doing wrong ?
> > Here is what I wrote in the userform and I made a combobox and a refeditbox.
> >
> > 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 my module (some of it)
> >
> > 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
> > Charts.Add
> > ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
> > ActiveChart.SetSourceData Source:=Rng1, PlotBy:=xlColumns
> > ActiveChart.Location Where:=xlLocationAsNewSheet
> > ActiveChart.ChartTitle.Characters.Text = "Capacity test 067L5640 #115" &
> > Chr(10) & " TR6 BP3"
> > Selection.AutoScaleFont = False
> > With Selection.Characters(Start:=1, Length:=36).Font
> > .Name = "Arial"
> > .FontStyle = "fed"
> > .Size = 11.5
> > .Strikethrough = False
> > .Superscript = False
> > .Subscript = False
> > .OutlineFont = False
> > .Shadow = False
> > .Underline = xlUnderlineStyleNone
> > .ColorIndex = xlAutomatic
> > End With
> > With ActiveChart
> > .Axes(xlCategory, xlPrimary).HasTitle = True
> > .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Superheat
> > [°K]"
> > .Axes(xlValue, xlPrimary).HasTitle = True
> > .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Capacity in
> > Tons refrigeration [R22]"
> > End With
> > With ActiveChart.Axes(xlCategory)
> > .HasMajorGridlines = True
> > .HasMinorGridlines = False
> > End With
> > With ActiveChart.Axes(xlValue)
> > .HasMajorGridlines = True
> > .HasMinorGridlines = False
> > End With
> > ActiveChart.HasLegend = False
> > ActiveChart.PlotArea.Select
> > With Selection.Border
> > .ColorIndex = 16
> > .Weight = xlThin
> > .LineStyle = xlContinuous
> > End With
> > Selection.Interior.ColorIndex = xlNone
> > ActiveChart.ChartArea.Select
> > and so on...
> >
> >
> > Susanne
> >
> > "Toppers" wrote:
> >
> > > 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 Where:=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:R9261C19"
> > > > ActiveChart.SeriesCollection(1).Values = _
> > > > "=TR6_BP3_BP5_122_125_20050408_00!R2C14:R9261C14"
> > > > ActiveChart.Location Where:=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