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.Combo Box.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.Combo Box.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



"Leith Ross" wrote:

On Aug 19, 3:25 pm, wrote:
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

On Aug 19, 11:36 pm, OssieMac
wrote:
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.Combo Box.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.Combo Box.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



"Leith Ross" wrote:
On Aug 19, 3:25 wrote:
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

HelloSammy,

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- Hide quoted text -

- 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

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

" wrote:

On Aug 19, 11:36 pm, OssieMac
wrote:
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.Combo Box.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.Combo Box.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



"Leith Ross" wrote:
On Aug 19, 3:25 wrote:
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

HelloSammy,

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- Hide quoted text -

- 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

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

"OssieMac" wrote:

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

" wrote:

On Aug 19, 11:36 pm, OssieMac
wrote:
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.Combo Box.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.Combo Box.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



"Leith Ross" wrote:
On Aug 19, 3:25 wrote:
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

HelloSammy,

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- Hide quoted text -

- 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

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



"OssieMac" wrote:

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

"OssieMac" wrote:

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

" wrote:

On Aug 19, 11:36 pm, OssieMac
wrote:
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.Combo Box.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.Combo Box.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



"Leith Ross" wrote:
On Aug 19, 3:25 wrote:
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

HelloSammy,

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- Hide quoted text -

- 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

On Aug 20, 7:04 pm, OssieMac
wrote:
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



"OssieMac" wrote:
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

"OssieMac" wrote:

Hi againSammy,

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

" wrote:

On Aug 19, 11:36 pm, OssieMac
wrote:
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.Combo Box.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.Combo Box.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

"Leith Ross" wrote:
On Aug 19, 3:25 wrote:
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

HelloSammy,

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- Hide quoted text -

- 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- Hide quoted text -

- 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