How to save entire Cell in a variable and put it back later?

[Joe HM]

Hello -

I wrote a little deleteCell() function that deletes a cell and shifts
the cells below up. I had to do that because most of the sheet is
locked and I don't want the user to fool with it. So I wanted to give
the option of a undelete as well. I store the .Value and
..Interior.ColorIndex in a string and integer respectively.

I was wondering if I could store the entire cell in an object or
something like that? I want to keep all the formatting including
character font etc. ...

I tried ...

' Global
dim gCell as object

Set gCell = lSheet.Cell(1,1)

This works ... just not when I try to assign it back later ...

lSheet.Cell(1,1) = gCell

I cannot just copy it to the clipboard because the user might put other
stuff there in-between the calls.

How can I make this work to easily store and restore the format of the
cell. Would the CellFormat type work instead of the Object type?

Thanks,
Joe

 

[Jim Cone]

Joe,

You could hide a column and store a copy of the cell there.

Jim Cone
San Francisco, USA

 

[Guest]

I would store the cell and format with contents on a hidden worksheet. If
hidden new cell is named from within that sheet then the range is not visable
to the user and hidden columns or rows bug some people.

 

[sali]

Hello -

I wrote a little deleteCell() function that deletes a cell and shifts
the cells below up. I had to do that because most of the sheet is
locked and I don't want the user to fool with it. So I wanted to give
the option of a undelete as well. I store the .Value and
.Interior.ColorIndex in a string and integer respectively.

I was wondering if I could store the entire cell in an object or
something like that? I want to keep all the formatting including
character font etc. ...

I tried ...

' Global
dim gCell as object

Set gCell = lSheet.Cell(1,1)

This works ... just not when I try to assign it back later ...

lSheet.Cell(1,1) = gCell

maybe you need to copy property_by_property from "memory object" to
another cell [maybe not all of them, but at least interesting ones]?
something like:

sub cell_put(cell as range, gcell as variant)
with cell do
.value=gcell.value
.format=gcell.format
'rest of interesting props here

end with
end sub