Building Forms (like a command button) into a cell
 

Is there an easy way to create a series of command buttons programatically so
that they line up in a column of cells. Each button will need to be renamed,
each cell (that gets a button) will need to be sized properly. I would like
to know if there is an option that would do this... the only other way I can
think of that might work is to figure out how to calculate the necessary size
for each cell based on the top-left and bottom-right cords of the button.

I also will need to do the same kind of function for a picture-box and
assign a formula to this (using it as a view window to show a series of cells
from another part of the workbook)

Both of these will also include a block copy with a range of cells and their
formulae. (If there were a way to do the cell copy [which includes 1 picture
box & 1 button] and have it include the objects ... and be able to rename the
objects and set the new formula or macro, this would accomplish the task)

Any tips on how to approach this?

--
Regards,
John

Building Forms (like a command button) into a cell
 

Hi John,

Here's an example

Sub AddFormsButton()
Dim cell As Range
Dim oBtn

With ActiveSheet
For Each cell In Range("B5:B10")
Set oBtn = .Buttons.Add(cell.Left, cell.Top, cell.Width,
cell.Height)
oBtn.Caption = "Button " & cell.Row
oBtn.OnAction = "Macro1"
Next cell
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Keith" wrote in message
...
Is there an easy way to create a series of command buttons programatically
so
that they line up in a column of cells. Each button will need to be
renamed,
each cell (that gets a button) will need to be sized properly. I would
like
to know if there is an option that would do this... the only other way I
can
think of that might work is to figure out how to calculate the necessary
size
for each cell based on the top-left and bottom-right cords of the button.

I also will need to do the same kind of function for a picture-box and
assign a formula to this (using it as a view window to show a series of
cells
from another part of the workbook)

Both of these will also include a block copy with a range of cells and
their
formulae. (If there were a way to do the cell copy [which includes 1
picture
box & 1 button] and have it include the objects ... and be able to rename
the
objects and set the new formula or macro, this would accomplish the task)

Any tips on how to approach this?

--
Regards,
John

Building Forms (like a command button) into a cell
 

Thats exactly what I was looking for.

Where can I find a list of the available types that will work with the
".add". I need a picture box now.

How do I convert the following code to work inside the For each loop?
Range("A5:C5").Select 'is the range to show in side the picture
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Range("H5").Select ' is the cell to position the picture box in
ActiveSheet.Pictures.Paste.Select



--
Regards,
John


"Bob Phillips" wrote:

Hi John,

Here's an example

Sub AddFormsButton()
Dim cell As Range
Dim oBtn

With ActiveSheet
For Each cell In Range("B5:B10")
Set oBtn = .Buttons.Add(cell.Left, cell.Top, cell.Width,
cell.Height)
oBtn.Caption = "Button " & cell.Row
oBtn.OnAction = "Macro1"
Next cell
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Keith" wrote in message
...
Is there an easy way to create a series of command buttons programatically
so
that they line up in a column of cells. Each button will need to be
renamed,
each cell (that gets a button) will need to be sized properly. I would
like
to know if there is an option that would do this... the only other way I
can
think of that might work is to figure out how to calculate the necessary
size
for each cell based on the top-left and bottom-right cords of the button.

I also will need to do the same kind of function for a picture-box and
assign a formula to this (using it as a view window to show a series of
cells
from another part of the workbook)

Both of these will also include a block copy with a range of cells and
their
formulae. (If there were a way to do the cell copy [which includes 1
picture
box & 1 button] and have it include the objects ... and be able to rename
the
objects and set the new formula or macro, this would accomplish the task)

Any tips on how to approach this?

--
Regards,
John

Building Forms (like a command button) into a cell
 

Like this???

Option Explicit
Sub testme()
Dim iRow As Long
With ActiveSheet
For iRow = 5 To 10
.Cells(iRow, "A").Resize(1, 3).CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
.Cells(iRow, "H").Select
.Pictures.Paste
Next iRow
End With
End Sub



John Keith wrote:

Thats exactly what I was looking for.

Where can I find a list of the available types that will work with the
".add". I need a picture box now.

How do I convert the following code to work inside the For each loop?
Range("A5:C5").Select 'is the range to show in side the picture
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Range("H5").Select ' is the cell to position the picture box in
ActiveSheet.Pictures.Paste.Select

--
Regards,
John

"Bob Phillips" wrote:

Hi John,

Here's an example

Sub AddFormsButton()
Dim cell As Range
Dim oBtn

With ActiveSheet
For Each cell In Range("B5:B10")
Set oBtn = .Buttons.Add(cell.Left, cell.Top, cell.Width,
cell.Height)
oBtn.Caption = "Button " & cell.Row
oBtn.OnAction = "Macro1"
Next cell
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Keith" wrote in message
...
Is there an easy way to create a series of command buttons programatically
so
that they line up in a column of cells. Each button will need to be
renamed,
each cell (that gets a button) will need to be sized properly. I would
like
to know if there is an option that would do this... the only other way I
can
think of that might work is to figure out how to calculate the necessary
size
for each cell based on the top-left and bottom-right cords of the button.

I also will need to do the same kind of function for a picture-box and
assign a formula to this (using it as a view window to show a series of
cells
from another part of the workbook)

Both of these will also include a block copy with a range of cells and
their
formulae. (If there were a way to do the cell copy [which includes 1
picture
box & 1 button] and have it include the objects ... and be able to rename
the
objects and set the new formula or macro, this would accomplish the task)

Any tips on how to approach this?

--
Regards,
John




--

Dave Peterson

Building Forms (like a command button) into a cell
 

Yes, thanks Dave.

once more thing though...

I want to make the Picture box name something specific. The recorder shows
this code...
ActiveSheet.Shapes("Picture 39").Select
Selection.Name = "ViewBox1"

What is the generic way to select a just created shape when you do not know
the exact name excel has decided to choose?

I tried to use the Selection.name=.... right after .Cells(iRow, "H").Select
but that didnt work, no errors.. but the names were still
"picture##-picture##" The Cells in Col-H got the assigned names.

Also tried .Shapes.Select (just leaving out the explicit name) but that
caused an error.

Finally I tried:
Dim iRow As Long
Dim Count As Integer
Count = 0
With ActiveSheet

For iRow = 5 To 10
Count = Count + 1
.Cells(iRow, "A").Resize(1, 3).CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
.Cells(iRow, "H").Select
.Pictures.Paste
.Pictures.Select
Selection.Name = "ViewBox" & Count
Next iRow
End With

The first time through the loop, the name "ViewBox1" does get assigned
properly, however the 2nd time through the loop a runtime error 438 (object
doesn't support the property or method) occurs on the "Selection.Name =
"ViewBox" & Count" statement. This confuses me, why would the assignment
work once but not again???

--
Regards,
John


"Dave Peterson" wrote:

Like this???

Option Explicit
Sub testme()
Dim iRow As Long
With ActiveSheet
For iRow = 5 To 10
.Cells(iRow, "A").Resize(1, 3).CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
.Cells(iRow, "H").Select
.Pictures.Paste
Next iRow
End With
End Sub



John Keith wrote:

Thats exactly what I was looking for.

Where can I find a list of the available types that will work with the
".add". I need a picture box now.

How do I convert the following code to work inside the For each loop?
Range("A5:C5").Select 'is the range to show in side the picture
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Range("H5").Select ' is the cell to position the picture box in
ActiveSheet.Pictures.Paste.Select

--
Regards,
John

"Bob Phillips" wrote:

Hi John,

Here's an example

Sub AddFormsButton()
Dim cell As Range
Dim oBtn

With ActiveSheet
For Each cell In Range("B5:B10")
Set oBtn = .Buttons.Add(cell.Left, cell.Top, cell.Width,
cell.Height)
oBtn.Caption = "Button " & cell.Row
oBtn.OnAction = "Macro1"
Next cell
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Keith" wrote in message
...
Is there an easy way to create a series of command buttons programatically
so
that they line up in a column of cells. Each button will need to be
renamed,
each cell (that gets a button) will need to be sized properly. I would
like
to know if there is an option that would do this... the only other way I
can
think of that might work is to figure out how to calculate the necessary
size
for each cell based on the top-left and bottom-right cords of the button.

I also will need to do the same kind of function for a picture-box and
assign a formula to this (using it as a view window to show a series of
cells
from another part of the workbook)

Both of these will also include a block copy with a range of cells and
their
formulae. (If there were a way to do the cell copy [which includes 1
picture
box & 1 button] and have it include the objects ... and be able to rename
the
objects and set the new formula or macro, this would accomplish the task)

Any tips on how to approach this?

--
Regards,
John




--

Dave Peterson

Building Forms (like a command button) into a cell
 

Sub AA()
Dim iRow As Long
Dim Count As Integer
Dim pic as Picture
Count = 0
With ActiveSheet

For iRow = 5 To 10
Count = Count + 1
.Cells(iRow, "A").Resize(1, 3).CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
.Cells(iRow, "H").Select
.Pictures.Paste
For Each pic In .Pictures
If pic.TopLeftCell.Row = iRow Then
pic.Name = "ViewBox" & Count
End If
Next
Next iRow
End With

End Sub

--
regards,
Tom Ogilvy


"John Keith" wrote in message
...
Yes, thanks Dave.

once more thing though...

I want to make the Picture box name something specific. The recorder
shows
this code...
ActiveSheet.Shapes("Picture 39").Select
Selection.Name = "ViewBox1"

What is the generic way to select a just created shape when you do not
know
the exact name excel has decided to choose?

I tried to use the Selection.name=.... right after .Cells(iRow,
"H").Select
but that didnt work, no errors.. but the names were still
"picture##-picture##" The Cells in Col-H got the assigned names.

Also tried .Shapes.Select (just leaving out the explicit name) but that
caused an error.

Finally I tried:
Dim iRow As Long
Dim Count As Integer
Count = 0
With ActiveSheet

For iRow = 5 To 10
Count = Count + 1
.Cells(iRow, "A").Resize(1, 3).CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
.Cells(iRow, "H").Select
.Pictures.Paste
.Pictures.Select
Selection.Name = "ViewBox" & Count
Next iRow
End With

The first time through the loop, the name "ViewBox1" does get assigned
properly, however the 2nd time through the loop a runtime error 438
(object
doesn't support the property or method) occurs on the "Selection.Name =
"ViewBox" & Count" statement. This confuses me, why would the assignment
work once but not again???

--
Regards,
John


"Dave Peterson" wrote:

Like this???

Option Explicit
Sub testme()
Dim iRow As Long
With ActiveSheet
For iRow = 5 To 10
.Cells(iRow, "A").Resize(1, 3).CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
.Cells(iRow, "H").Select
.Pictures.Paste
Next iRow
End With
End Sub



John Keith wrote:

Thats exactly what I was looking for.

Where can I find a list of the available types that will work with the
".add". I need a picture box now.

How do I convert the following code to work inside the For each loop?
Range("A5:C5").Select 'is the range to show in side the picture
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Range("H5").Select ' is the cell to position the picture box
in
ActiveSheet.Pictures.Paste.Select

--
Regards,
John

"Bob Phillips" wrote:

Hi John,

Here's an example

Sub AddFormsButton()
Dim cell As Range
Dim oBtn

With ActiveSheet
For Each cell In Range("B5:B10")
Set oBtn = .Buttons.Add(cell.Left, cell.Top, cell.Width,
cell.Height)
oBtn.Caption = "Button " & cell.Row
oBtn.OnAction = "Macro1"
Next cell
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Keith" wrote in message
...
Is there an easy way to create a series of command buttons
programatically
so
that they line up in a column of cells. Each button will need to
be
renamed,
each cell (that gets a button) will need to be sized properly. I
would
like
to know if there is an option that would do this... the only other
way I
can
think of that might work is to figure out how to calculate the
necessary
size
for each cell based on the top-left and bottom-right cords of the
button.

I also will need to do the same kind of function for a picture-box
and
assign a formula to this (using it as a view window to show a
series of
cells
from another part of the workbook)

Both of these will also include a block copy with a range of cells
and
their
formulae. (If there were a way to do the cell copy [which
includes 1
picture
box & 1 button] and have it include the objects ... and be able to
rename
the
objects and set the new formula or macro, this would accomplish
the task)

Any tips on how to approach this?

--
Regards,
John




--

Dave Peterson

Building Forms (like a command button) into a cell
 

Another way if these are the only pictures on the sheet.

Option Explicit
Sub testme01()
Dim iRow As Long
Dim pic As Picture
With ActiveSheet
For iRow = 5 To 10
.Cells(iRow, "A").Resize(1, 3).CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
.Cells(iRow, "H").Select
.Pictures.Paste
Set pic = .Pictures(.Pictures.Count)
pic.Name = "ViewBox" & .Pictures.Count
Next iRow
End With
End Sub

It uses the fact that the picture you just pasted is the last picture on the
worksheet.


John Keith wrote:

Yes, thanks Dave.

once more thing though...

I want to make the Picture box name something specific. The recorder shows
this code...
ActiveSheet.Shapes("Picture 39").Select
Selection.Name = "ViewBox1"

What is the generic way to select a just created shape when you do not know
the exact name excel has decided to choose?

I tried to use the Selection.name=.... right after .Cells(iRow, "H").Select
but that didnt work, no errors.. but the names were still
"picture##-picture##" The Cells in Col-H got the assigned names.

Also tried .Shapes.Select (just leaving out the explicit name) but that
caused an error.

Finally I tried:
Dim iRow As Long
Dim Count As Integer
Count = 0
With ActiveSheet

For iRow = 5 To 10
Count = Count + 1
.Cells(iRow, "A").Resize(1, 3).CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
.Cells(iRow, "H").Select
.Pictures.Paste
.Pictures.Select
Selection.Name = "ViewBox" & Count
Next iRow
End With

The first time through the loop, the name "ViewBox1" does get assigned
properly, however the 2nd time through the loop a runtime error 438 (object
doesn't support the property or method) occurs on the "Selection.Name =
"ViewBox" & Count" statement. This confuses me, why would the assignment
work once but not again???

--
Regards,
John

"Dave Peterson" wrote:

Like this???

Option Explicit
Sub testme()
Dim iRow As Long
With ActiveSheet
For iRow = 5 To 10
.Cells(iRow, "A").Resize(1, 3).CopyPicture _
Appearance:=xlScreen, Format:=xlPicture
.Cells(iRow, "H").Select
.Pictures.Paste
Next iRow
End With
End Sub



John Keith wrote:

Thats exactly what I was looking for.

Where can I find a list of the available types that will work with the
".add". I need a picture box now.

How do I convert the following code to work inside the For each loop?
Range("A5:C5").Select 'is the range to show in side the picture
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Range("H5").Select ' is the cell to position the picture box in
ActiveSheet.Pictures.Paste.Select

--
Regards,
John

"Bob Phillips" wrote:

Hi John,

Here's an example

Sub AddFormsButton()
Dim cell As Range
Dim oBtn

With ActiveSheet
For Each cell In Range("B5:B10")
Set oBtn = .Buttons.Add(cell.Left, cell.Top, cell.Width,
cell.Height)
oBtn.Caption = "Button " & cell.Row
oBtn.OnAction = "Macro1"
Next cell
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Keith" wrote in message
...
Is there an easy way to create a series of command buttons programatically
so
that they line up in a column of cells. Each button will need to be
renamed,
each cell (that gets a button) will need to be sized properly. I would
like
to know if there is an option that would do this... the only other way I
can
think of that might work is to figure out how to calculate the necessary
size
for each cell based on the top-left and bottom-right cords of the button.

I also will need to do the same kind of function for a picture-box and
assign a formula to this (using it as a view window to show a series of
cells
from another part of the workbook)

Both of these will also include a block copy with a range of cells and
their
formulae. (If there were a way to do the cell copy [which includes 1
picture
box & 1 button] and have it include the objects ... and be able to rename
the
objects and set the new formula or macro, this would accomplish the task)

Any tips on how to approach this?

--
Regards,
John




--

Dave Peterson


--

Dave Peterson