Clearing Comments?

[(PeteCresswell)]

I'm in MS Access VBA, constructing Excel spreadsheets.

During the process, I need to squirrel away a couple of numbers that are
associated with each sheet - to be used later on.

My preference is for the users never to see those numbers and I'm inserting them
as comments rather than cell contents. Now that I think of it, just putting
them into a cell somewhere and then deleting them would be more logical... but
the current approach has raised an issue that I'd like to get to the bottom of.

Namely: When I go to clear the comments, a chart that happens to be positioned
over the range containing the comments seems to get deleted.

Can anybody find anything wrong with the code below? It seems to work without
deleting the chart if I do the .Select... but my understanding is that .Select
in VBA code is bad practice unless it's absolutely needed.

To create the comments:
---------------------------------------------------
3030 With .Range(.Cells(mRowNum_Comment_DealID, mColNum_Comment_DealID),
.Cells(mRowNum_Comment_DealID, mColNum_Comment_DealID))
3031 .AddComment
3032 With .Comment
3033 .Text Text:=Format$(theDealID, "0000000")
3034 .Visible = False
3035 End With
3039 End With

3040 With .Range(.Cells(mRowNum_Comment_TrancheID, mColNum_Comment_TrancheID),
.Cells(mRowNum_Comment_TrancheID, mColNum_Comment_TrancheID))
3041 .AddComment
3042 With .Comment
3043 .Text Text:=Format$(theTrancheID, "0000000")
3044 .Visible = False
3045 End With
3949 End With
---------------------------------------------------

To retrieve, then delete the comments:
---------------------------------------------------
1860 With myWS
1870 With .Range(.Cells(mRowNum_Comment_DealID, mColNum_Comment_DealID),
.Cells(mRowNum_Comment_DealID, mColNum_Comment_DealID))
1871 curDealID = Val(.Comment.Text & "")
'1872 .Select
1873 .ClearComments
1874 End With

1875 With .Range(.Cells(mRowNum_Comment_TrancheID,
mColNum_Comment_TrancheID), .Cells(mRowNum_Comment_TrancheID,
mColNum_Comment_TrancheID))
1876 curTrancheID = Val(.Comment.Text & "")
'1877 .Select
1878 .ClearComments
1879 End With
1899 end with

 

[Jon Peltier]

I would have used a Name:

wks.Names.Add Name:="HiddenProperty1", RefersTo:=Format$(theDealID,
"0000000")
wks.Names("HiddenProperty1" ).Visible = False
wks.Names("HiddenProperty1" ).Delete

I can't see why your code is deleting any charts.

- Jon

 

[(PeteCresswell)]

Per Jon Peltier:

I would have used a Name:

wks.Names.Add Name:="HiddenProperty1", RefersTo:=Format$(theDealID,
"0000000")
wks.Names("HiddenProperty1" ).Visible = False
wks.Names("HiddenProperty1" ).Delete

I can't see why your code is deleting any charts.

That sounds like a *much* more sensible solution.

Thanks.

 

[(PeteCresswell)]

Per Jon Peltier:

I would have used a Name:

Went back and re-did it using Names.

Also, aside from making more sense,
the new code is more concise.

viz:
-----------------------------------------------
3030 With theWS.Names
3031 .Add Name:="DealID", RefersTo:=Format$(theDealID, "0000000")
3032 .Add Name:="TrancheID", RefersTo:=Format$(theTrancheID, "0000000")
3039 End With
-----------------------------------------------
1860 With myWS
1861 curDealID = Val(Replace(.Names("DealID").RefersTo, "=", ""))
1862 curTrancheID = Val(Replace(.Names("TrancheID").RefersTo, "=", ""))
1869 End With
-----------------------------------------------

I didn't bother with .Visible=False bc it's already not in plain view
and there's no harm if a user stumbles on to it.
Also being able to see it might help debugging some day.

Thanks again.

 

[NickHK]

Pete,
A Range has an ID property that you set to some value.
If these data are associated with specific ranges, then using the ID ties it
together.
The user cannot see these without using VBA.

NickHK

 

[Peter T]

"(PeteCresswell)" wrote in message

I can't see why your code is deleting any charts.

I replicated the OP's problem IF a chart on same sheet is selected while
doing
rng.ClearComments

This is irrespective of where the chart is located if on same sheet as
comment(s) being deleted which is also the activesheet. Ie not necessarily
"positioned over the range containing the comments". Also, when doing that
the cell comment(s) is/are not deleted.

Similar occurs for me if any object is selected, eg a selected Rectangle
will get deleted instead of the comment.

So it would seem sensible to ensure the current selection is any cell range
before deleting comments (if deleting comments on the activesheet).

Regards,
Peter T

 

[Peter T]

Just to add, a cell's ID property is not saved with the workbook, which may
or may not be useful depending on your needs.

Regards,
Peter T

Pete,
A Range has an ID property that you set to some value.
If these data are associated with specific ranges, then using the ID ties it
together.
The user cannot see these without using VBA.

NickHK

I'm in MS Access VBA, constructing Excel spreadsheets.

During the process, I need to squirrel away a couple of numbers that are
associated with each sheet - to be used later on.

My preference is for the users never to see those numbers and I'm inserting them
as comments rather than cell contents. Now that I think of it, just putting
them into a cell somewhere and then deleting them would be more

logical...