Is it possible to create a class, lets call it CellAndPic, which unites
two objects: a range object and a picture object?

I want to be able to create a cell that inherently has a picture in it.
All properties of both the range object and the picture object should
be able to modified.

I haven't created classes before, so I need a little more then a yes/no
answer... if its possible.

Please ask any questions that will help me clarify my request.

Hello Abraham,

Technically a cell cannot contain a picture. Excel has a separate
drawing layer for pictures and shapes that are placed relative to a
cell's position. You can control the placement of a shape object or
picture on the worksheet using VBA code. There is really no need to
construct your own object class, which would be far more complex to do.


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=556275

Yeah, I knew the cell cannot contain a picture, but each picture would
be associated with a cell via position. I now agree with you and have
decided against constructing an object class... Im going about it a
different way. It would have been slick to be able to modify both the
picture and cell from one object though.

Thanks for the response,

-Abe

Leith Ross wrote:
Hello Abraham,

Technically a cell cannot contain a picture. Excel has a separate
drawing layer for pictures and shapes that are placed relative to a
cell's position. You can control the placement of a shape object or
picture on the worksheet using VBA code. There is really no need to
construct your own object class, which would be far more complex to do.


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=556275

Abe,
If you are planning on having many of these cell/pic relationships to
together, it would be pretty easy to create a class for this and a
collection of each on a WS to facilitate resyncing (??) or whatever. e.g.

<cCellPic
Private mCell As Range
Private mPic As Shape

Public Property Set ThisCell(vData As Range)
Set mCell = vData
End Property

Public Property Set ThisPic(vData As Shape)
Set mPic = vData
End Property

Public Property Let CellWidth(vData As Single)
mCell.ColumnWidth = vData
Call ReSync
End Property

Public Function ReSync()
With mCell
mPic.Top = .Top
mPic.Left = .Left
'Need to calculate the scaling correctly
mPic.ScaleWidth .ColumnWidth / mPic.Width, msoTrue, msoScaleFromTopLeft
mPic.ScaleHeight .RowHeight / mPic.Height, msoFalse, msoScaleFromTopLeft
End With
End Function
</cCellPic

<WS code
Dim CellPic As cCellPic

Private Sub CommandButton1_Click()
Set CellPic = New Class1

With CellPic
Set .ThisCell = Range("A1")
Set .ThisPic = ActiveSheet.Shapes("Picture 2")
.CellWidth = ActiveSheet.Range("D1").Value
End With

End Sub

NickHK


wrote in message
ups.com...
Yeah, I knew the cell cannot contain a picture, but each picture would
be associated with a cell via position. I now agree with you and have
decided against constructing an object class... Im going about it a
different way. It would have been slick to be able to modify both the
picture and cell from one object though.

Thanks for the response,

-Abe

Leith Ross wrote:
Hello Abraham,

Technically a cell cannot contain a picture. Excel has a separate
drawing layer for pictures and shapes that are placed relative to a
cell's position. You can control the placement of a shape object or
picture on the worksheet using VBA code. There is really no need to
construct your own object class, which would be far more complex to do.


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread:
http://www.excelforum.com/showthread...hreadid=556275

That is AWESOME! It will make the coding for my program a lot easier
(and more understandable). Thanks Nick, I am going to go for making the
class (as oppossed to my earlier posted decision). One question, would
..delete work on a cCellPic object, or do I need to define a function in
the class to do that?

-Abe


NickHK wrote:
Abe,
If you are planning on having many of these cell/pic relationships to
together, it would be pretty easy to create a class for this and a
collection of each on a WS to facilitate resyncing (??) or whatever. e.g.

<cCellPic
Private mCell As Range
Private mPic As Shape

Public Property Set ThisCell(vData As Range)
Set mCell = vData
End Property

Public Property Set ThisPic(vData As Shape)
Set mPic = vData
End Property

Public Property Let CellWidth(vData As Single)
mCell.ColumnWidth = vData
Call ReSync
End Property

Public Function ReSync()
With mCell
mPic.Top = .Top
mPic.Left = .Left
'Need to calculate the scaling correctly
mPic.ScaleWidth .ColumnWidth / mPic.Width, msoTrue, msoScaleFromTopLeft
mPic.ScaleHeight .RowHeight / mPic.Height, msoFalse, msoScaleFromTopLeft
End With
End Function
</cCellPic

<WS code
Dim CellPic As cCellPic

Private Sub CommandButton1_Click()
Set CellPic = New Class1

With CellPic
Set .ThisCell = Range("A1")
Set .ThisPic = ActiveSheet.Shapes("Picture 2")
.CellWidth = ActiveSheet.Range("D1").Value
End With

End Sub

NickHK


wrote in message
ups.com...
Yeah, I knew the cell cannot contain a picture, but each picture would
be associated with a cell via position. I now agree with you and have
decided against constructing an object class... Im going about it a
different way. It would have been slick to be able to modify both the
picture and cell from one object though.

Thanks for the response,

-Abe

Leith Ross wrote:
Hello Abraham,

Technically a cell cannot contain a picture. Excel has a separate
drawing layer for pictures and shapes that are placed relative to a
cell's position. You can control the placement of a shape object or
picture on the worksheet using VBA code. There is really no need to
construct your own object class, which would be far more complex to do.


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread:
http://www.excelforum.com/showthread...hreadid=556275

Abe,
If you write a .Delete method in your class.
e.g.
Public Enum DeleteObj
delPic
delCell
delBoth
End Enum

Public Function Delete (DeleteWhat As DeleteObj)
Select Case DeleteWhat
case delPic
mPic.Delete
case delCell
mCell.Delete xlShiftToLeft
case delBoth
mPic.Delete
mCell.Delete xlShiftToLeft
end select
End Function

And then call it from the WS code
cellpic.Delete delPic
'Release the reference
Set cellpic=nothing

It all depends what you want to achieve.

NickHK

wrote in message
oups.com...
That is AWESOME! It will make the coding for my program a lot easier
(and more understandable). Thanks Nick, I am going to go for making the
class (as oppossed to my earlier posted decision). One question, would
.delete work on a cCellPic object, or do I need to define a function in
the class to do that?

-Abe


NickHK wrote:
Abe,
If you are planning on having many of these cell/pic relationships to
together, it would be pretty easy to create a class for this and a
collection of each on a WS to facilitate resyncing (??) or whatever.
e.g.

<cCellPic
Private mCell As Range
Private mPic As Shape

Public Property Set ThisCell(vData As Range)
Set mCell = vData
End Property

Public Property Set ThisPic(vData As Shape)
Set mPic = vData
End Property

Public Property Let CellWidth(vData As Single)
mCell.ColumnWidth = vData
Call ReSync
End Property

Public Function ReSync()
With mCell
mPic.Top = .Top
mPic.Left = .Left
'Need to calculate the scaling correctly
mPic.ScaleWidth .ColumnWidth / mPic.Width, msoTrue,
msoScaleFromTopLeft
mPic.ScaleHeight .RowHeight / mPic.Height, msoFalse,
msoScaleFromTopLeft
End With
End Function
</cCellPic

<WS code
Dim CellPic As cCellPic

Private Sub CommandButton1_Click()
Set CellPic = New Class1

With CellPic
Set .ThisCell = Range("A1")
Set .ThisPic = ActiveSheet.Shapes("Picture 2")
.CellWidth = ActiveSheet.Range("D1").Value
End With

End Sub

NickHK


wrote in message
ups.com...
Yeah, I knew the cell cannot contain a picture, but each picture would
be associated with a cell via position. I now agree with you and have
decided against constructing an object class... Im going about it a
different way. It would have been slick to be able to modify both the
picture and cell from one object though.

Thanks for the response,

-Abe

Leith Ross wrote:
Hello Abraham,

Technically a cell cannot contain a picture. Excel has a separate
drawing layer for pictures and shapes that are placed relative to a
cell's position. You can control the placement of a shape object or
picture on the worksheet using VBA code. There is really no need to
construct your own object class, which would be far more complex to
do.


Sincerely,
Leith Ross


--
Leith Ross

------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread:
http://www.excelforum.com/showthread...hreadid=556275

Also, should it really be
..CellWidth = ActiveSheet.Range("D1").Value
for the worksheet code?
Wouldn't ActiveSheet.Range("D1").ColumnWidth be the desired value?

Thanks again Nick,

-Abe

NickHK wrote:
Abe,
If you are planning on having many of these cell/pic relationships to
together, it would be pretty easy to create a class for this and a
collection of each on a WS to facilitate resyncing (??) or whatever. e.g.

<cCellPic
Private mCell As Range
Private mPic As Shape

Public Property Set ThisCell(vData As Range)
Set mCell = vData
End Property

Public Property Set ThisPic(vData As Shape)
Set mPic = vData
End Property

Public Property Let CellWidth(vData As Single)
mCell.ColumnWidth = vData
Call ReSync
End Property

Public Function ReSync()
With mCell
mPic.Top = .Top
mPic.Left = .Left
'Need to calculate the scaling correctly
mPic.ScaleWidth .ColumnWidth / mPic.Width, msoTrue, msoScaleFromTopLeft
mPic.ScaleHeight .RowHeight / mPic.Height, msoFalse, msoScaleFromTopLeft
End With
End Function
</cCellPic

<WS code
Dim CellPic As cCellPic

Private Sub CommandButton1_Click()
Set CellPic = New Class1

With CellPic
Set .ThisCell = Range("A1")
Set .ThisPic = ActiveSheet.Shapes("Picture 2")
.CellWidth = ActiveSheet.Range("D1").Value
End With

End Sub

NickHK


wrote in message
ups.com...
Yeah, I knew the cell cannot contain a picture, but each picture would
be associated with a cell via position. I now agree with you and have
decided against constructing an object class... Im going about it a
different way. It would have been slick to be able to modify both the
picture and cell from one object though.

Thanks for the response,

-Abe

Leith Ross wrote:
Hello Abraham,

Technically a cell cannot contain a picture. Excel has a separate
drawing layer for pictures and shapes that are placed relative to a
cell's position. You can control the placement of a shape object or
picture on the worksheet using VBA code. There is really no need to
construct your own object class, which would be far more complex to do.


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread:
http://www.excelforum.com/showthread...hreadid=556275

Abe,
The point of that statement was to show that you could take a value from a
WS (or anywhere for that matter), change a property within the class,
..CellWidth, and not only is the cell's width changed, but the picture is
fitted to the new size.
It would depend on what you need to achieve as to which Properties/method
your class requires, but as you writing it yourself, you have complete
freedom.

NickHK

wrote in message
ups.com...
Also, should it really be
.CellWidth = ActiveSheet.Range("D1").Value
for the worksheet code?
Wouldn't ActiveSheet.Range("D1").ColumnWidth be the desired value?

Thanks again Nick,

-Abe

NickHK wrote:
Abe,
If you are planning on having many of these cell/pic relationships to
together, it would be pretty easy to create a class for this and a
collection of each on a WS to facilitate resyncing (??) or whatever.
e.g.

<cCellPic
Private mCell As Range
Private mPic As Shape

Public Property Set ThisCell(vData As Range)
Set mCell = vData
End Property

Public Property Set ThisPic(vData As Shape)
Set mPic = vData
End Property

Public Property Let CellWidth(vData As Single)
mCell.ColumnWidth = vData
Call ReSync
End Property

Public Function ReSync()
With mCell
mPic.Top = .Top
mPic.Left = .Left
'Need to calculate the scaling correctly
mPic.ScaleWidth .ColumnWidth / mPic.Width, msoTrue,
msoScaleFromTopLeft
mPic.ScaleHeight .RowHeight / mPic.Height, msoFalse,
msoScaleFromTopLeft
End With
End Function
</cCellPic

<WS code
Dim CellPic As cCellPic

Private Sub CommandButton1_Click()
Set CellPic = New Class1

With CellPic
Set .ThisCell = Range("A1")
Set .ThisPic = ActiveSheet.Shapes("Picture 2")
.CellWidth = ActiveSheet.Range("D1").Value
End With

End Sub

NickHK


wrote in message
ups.com...
Yeah, I knew the cell cannot contain a picture, but each picture would
be associated with a cell via position. I now agree with you and have
decided against constructing an object class... Im going about it a
different way. It would have been slick to be able to modify both the
picture and cell from one object though.

Thanks for the response,

-Abe

Leith Ross wrote:
Hello Abraham,

Technically a cell cannot contain a picture. Excel has a separate
drawing layer for pictures and shapes that are placed relative to a
cell's position. You can control the placement of a shape object or
picture on the worksheet using VBA code. There is really no need to
construct your own object class, which would be far more complex to
do.


Sincerely,
Leith Ross


--
Leith Ross

------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread:
http://www.excelforum.com/showthread...hreadid=556275