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

  • Thread starter Thread starter Joe HM
  • Start date Start date
J

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
 
Joe,

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

Jim Cone
San Francisco, USA
 
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.
 
Joe HM said:
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
Click to expand...

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
 
Back
Top