location of combobox and chart

[sammy.peters]

i was wondering if its possible to select a cell on a sheet where to
put a combobox or a chart rather than by pixel.
i am planning on making one sheet called graphs which will have 25
graphs each with their own combobox. when you select an item from the
combo box list it is going to graph that column from another worksheet
on that page but I dont know how to spread them out automatically.

I wanted to use a macro to make it for me.


thanks
sammy

 

[Guest]

I don't know of any way of using a cell to position the controls.

However, if your columns widths are all the same, you can record a macro to
insert the controls at a couple of specific positions and you can then
calculate how many points wide for one column and simply multiply them for
the number of columns.

You can use actual row height to calculate vertical positions but again only
if you are using row heights which are all the same. Unfortunately column
widths are not measured in points so that will not work for the width.

Regards,

OssieMac

 

[Leith Ross]

i was wondering if its possible to select a cell on a sheet where to
put a combobox or a chart rather than by pixel.
i am planning on making one sheet called graphs which will have 25
graphs each with their own combobox. when you select an item from the
combo box list it is going to graph that column from another worksheet
on that page but I dont know how to spread them out automatically.

I wanted to use a macro to make it for me.

thanks
sammy

Hello Sammy,

You can reposition an object by setting its Left and Top properties to
same as the selected cell. This macro will move the chart named "Chart
1" on the Active sheet so the upper left corner of the chart is in the
upper left corner of cell D10. This same method will work for the
ComboBox as well.

Sub MoveChart()

With ActiveSheet
.ChartObjects("Chart 1").Left = .Range("D10").Left
.ChartObjects("Chart 1").Top = .Range("D10").Top
End With

End Sub

Sincerely,
Leith Ross

 

[Guest]

Marvelous solution Leith,

I just tested using your your idea to modify recorded code of creating a
combo box and it works perfectly.

Recorded code to create combo box:-

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=96, Top:=24.75, Width:=144.75,
Height:=27 _
).Select

Modified to position combo box based on cell position:-

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=Range("D10").Left,
Top:=Range("D10").Top, Width:=144.75, Height:=27 _
).Select

Hope it works well for you Sammy.

Regards,

OssieMac

 

[sammy.peters]

Marvelous solution Leith,

I just tested using your your idea to modify recorded code of creating a
combo box and it works perfectly.

Recorded code to create combo box:-

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=96, Top:=24.75, Width:=144.75,
Height:=27 _
).Select

Modified to position combo box based on cell position:-

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=Range("D10").Left,
Top:=Range("D10").Top, Width:=144.75, Height:=27 _
).Select

Hope it works well for youSammy.

Regards,

OssieMac









- Show quoted text -

my code now is ActiveSheet.ScrollBars.Add(pixcol, pixrow, 283.5,
12.75).Select
With Selection
.Value = 0
.Min = 0
.Max = index
.SmallChange = 1
.LargeChange = 10
.LinkedCell = "b" & i + 1
.Display3DShading = True
pixrow = pixrow + 15
End With

where do i put that in this?
i am not familiar with oleobjects

 

[Guest]

Hi again Sammy,

Try this:-

ActiveSheet.ScrollBars.Add(Range("D10").Left, Range("D10").Top, 283.5,
12.75).Select

With Selection
.Value = 0
.Min = 0
.Max = Index
.SmallChange = 1
.LargeChange = 10
.LinkedCell = "b" & i + 1
.Display3DShading = True
pixrow = pixrow + 15
End With

Regards,

OssieMac

 

[Guest]

I need to research what your code in the With/End With does. I think that it
needs some adjustment also but I am not sure what needs to be changed.

However, it appears that you want to increment the row position so the
following might be a better method of inserting cell addresses so that you
can increment rows and columns easily with a variable:-

Note: It is Cells(row,column). Back to front to the usual D4 type address
where is is column first.

ActiveSheet.ScrollBars.Add _
(Cells(10, 4).Left, Cells(10, 4).Top, 283.5, 12.75).Select

Regards,

OssieMac

 

[Guest]

I have had another look at your code in the With/End With section. What threw
me initially was your use of the reserved word Index. Now that I have worked
out what I believe you intended then the only change you realy need is to
replace Index with a variable which is not a reserved word.

The test that I use for a reserved word is PRIOR to dimensioning it, is to
simply type it on a line on it's own and hit Enter. If it is a reserved word
then VBA Intellisence recognises it and converts the first (and sometimes
intermediate) characters to upper case. I don't know if this works in all
cases but it is a fairly good indication.

Regards,

OssieMac

 

[sammy.peters]

I have had another look at your code in the With/End With section. What threw
me initially was your use of the reserved word Index. Now that I have worked
out what I believe you intended then the only change you realy need is to
replace Index with a variable which is not a reserved word.

The test that I use for a reserved word is PRIOR to dimensioning it, is to
simply type it on a line on it's own and hit Enter. If it is a reserved word
then VBA Intellisence recognises it and converts the first (and sometimes
intermediate) characters to upper case. I don't know if this works in all
cases but it is a fairly good indication.

Regards,

OssieMac








- Show quoted text -

heres what i did actually

ActiveSheet.ScrollBars.Add(zoomcol, zoomrow, 283.5, 12.75).Select
With Selection
.Left = Cells(zoomrow, zoomcol).Left
.Top = Cells(zoomrow, zoomcol).Top
.Value = 0
.Min = 0
.Max = index
.SmallChange = 1
.LargeChange = 10
.LinkedCell = scrollgraph
.Display3DShading = True
End With

sam i am

 

[Guest]

Hi there again Sam,

The code you are using is actually placing the scrollbar by the pixel
location and then moving it using the cell location. You can test it by
placing the following code into a blank workbook and running it. I have
inserted a stop so that you can then change windows to the worksheet and view
where it is placed. Continue the macro and then view the new location.
(Corrected code is further down).

I have also got rid of that reserved word 'Index' and dimensioned a new
variable to replace it. If you dimension the variables with a an uppercase
character as I have done then you can type them in all lower case and VBA
intellisence will change them to include the uppercase character and you then
know that you have not typed an error.

Sub test3()

Dim zoomCol As Single
Dim zoomRow As Single
Dim scrollMax As Single

zoomCol = 4
zoomRow = 10
scrollMax = 100

ActiveSheet.ScrollBars.Add(zoomCol, zoomRow, 283.5, 12.75).Select
Stop
With Selection
.Left = Cells(zoomRow, zoomCol).Left
.Top = Cells(zoomRow, zoomCol).Top
.Value = 0
.Min = 0
.Max = scrollMax
.SmallChange = 1
.LargeChange = 10
.LinkedCell = scrollGraph
.Display3DShading = True
End With

End Sub

The following code places the scrollbar in the correct cell location when it
is created.

Sub test4()

Dim zoomCol As Single
Dim zoomRow As Single
Dim scrollMax As Single
Dim scrollGraph As String

zoomCol = 4
zoomRow = 10
scrollMax = 100
scrollGraph = "B1"

ActiveSheet.ScrollBars.Add(Cells(zoomRow, zoomCol).Left, _
Cells(zoomRow, zoomCol).Top, 283.5, 12.75).Select
Stop
With Selection
.Value = 0
.Min = 0
.Max = scrollMax
.SmallChange = 1
.LargeChange = 10
.LinkedCell = scrollGraph
.Display3DShading = True
End With

End Sub

Regards,

OssieMac