Selection.width / .height / .left / .top from cell refor userform

[JB2010]

Hi there

I have a macro that makes a chart. I have included in it a userform to try
to control the size & layout of the chart's plot area & legend area. i cant
seem to find a way to make the numbers that come out of the userform
sucessfully change the chart layout, however.

in developing this macro,i have tried several different to set these
parameters. firstly, i tried it with simply the numbers & it works fine;

eg:

----------------------

ActiveChart.Legend.Select
Selection.Height = 34
Selection.Width = 40
Selection.Left = 684
Selection.Top = 196

ActiveChart.PlotArea.Select
Selection.Height = 416
Selection.Width = 673
Selection.Left = 1
Selection.Top = 25

---------------------

fair enough, but i want it to be more dynamic, so i tried this with a cell
ref where each cell equals the numbers above & i get nothing!

eg;

-----------------------------------

ActiveChart.Legend.Select
Selection.Height = Range("A1")
Selection.Width = Range("B1")
Selection.Left = Range("C1")
Selection.Top = Range("D1")

ActiveChart.PlotArea.Select
Selection.Height = Range("E1")
Selection.Width = Range("F1")
Selection.Left = Range("G1")
Selection.Top = Range("H1")

---------------

so then i tried something else, if i name a cell or the spinbutton value or
textbox value from the userform as a range & then quote that it still doesnt
work, though

eg;

--------------------

ActiveChart.Legend.Select
Selection.Height = LegH
Selection.Width = LegW
Selection.Left = LegL
Selection.Top = LegT

ActiveChart.PlotArea.Select
Selection.Height = PloH
Selection.Width = PloW
Selection.Left = PloL
Selection.Top = PloT

-----------------------

this is driving me up the wall!

i cant believe that it will only work if i enter a number rather than
anything else that draws it to the number.

any help gratefully recieved, let me know if you need more info

cheers

jb

 

[merjet]

Is the chart on a worksheet or a sheet of its own?

Turn on the macro recorder, change some of its properties manually,
turn off the recorder, and look at the variable names generated.

Hth,
Merjet

 

[merjet]

I tried manipulating a chart on a worksheet and one on a sheet of its
own using cell values. Both worked fine, but I had to specify the
sheet for the latter. Of course, if you use a value from a Textbox,
you should convert it from text to number.

Merjet

 

[JB2010]

Hi Merjet


Thanks once again for your assistance.

The chart is in a sheet of its own, when i make recorded changes the code
all begins "ActiveChart.". Could you please explain how i would convert the
textbox value from text to number?

Many thanks


jb

 

[merjet]

Given that the chart is on a sheet of its own and your code assumes it
is the ActiveSheet, using Range("A1") to adjust a chart property won't
work (gives me a run-time error 1004). You need to qualify it, e.g.
Sheets("Sheet1").Range("A1").

Could you please explain how i would convert the
textbox value from text to number?

Assign it to a variable declared as a number (Integer, Long, Single,
Double) or use a convert function CInt(), CLng(), CSng(), CDbl(),
Val().

Merjet

 

[JB2010]

Hi Merjet

....of course, specify the sheet, its so simple when you look it, isnt it! i
apologise for my dim-wittedness.

that now works fine coming from the cell refs, so it is basically done.
thank you.


i am still curious, however, as to how i would successfully get the number
from the textbox of the spinbutton into an integer for the macro to read for
the plot & legend scales. I have tried the CInt() thing & its not happening.


Many thanks again

jb

 

[merjet]

1. Dim iNum as Integer
iNum = Textbox1.Value

2. Textbox1.Value = "77"
CInt(Textbox1.Value) produces an Integer

SpinButton1.Value is a number (type Long), so no conversion is needed.

By the way, you can get the data type of any variable X with
Typename(X).

Hth,
Merjet