Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.width / .height / .left / .top from cell refor userform
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.width / .height / .left / .top from cell refor userform
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.width / .height / .left / .top from cell refor userform
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.width / .height / .left / .top from cell refor userf
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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.width / .height / .left / .top from cell refor userf
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.width / .height / .left / .top from cell refor userf
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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selection.width / .height / .left / .top from cell refor userf
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cell 's width and height | Excel Programming | |||
UserForm TextBox with fixed Width and dynamic Height | Excel Programming | |||
cell width and height | New Users to Excel | |||
What does the 8.43 and 12.75 represent in height and width of cell | Excel Discussion (Misc queries) | |||
.AddShape(Type, Left, Top, Width, Height) | Excel Programming |