location of combobox and chart
 

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

location of combobox and chart
 

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

location of combobox and chart
 

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

location of combobox and chart
 

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

location of combobox and chart
 

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

location of combobox and chart
 

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

location of combobox and chart
 

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


" wrote:

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