asked on

Flagging excel cells in VBA

I am writing an application in VBA to do simple subtotaling, completely independent of the subtotaling functions in excel.
I need a way to mark cells by manipulating a hidden property of some sort, so that the app sees these cells as subtotal cells. The marking needs to occur through the code, and needs to be invisible to the user. I can do it by bolding the text for example, but that is not elegant since a user could modify it. Any ideas?

ASKER CERTIFIED SOLUTION

Richie_Simonetti

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Mulder

Hi lmindlin,

Going on the way Richie mentioned

-Choose insert | name | define
-then with CTRL+mouseclick select the cells you want to use in teh range
-call it SubTotal

-now in code you can do something like this to loop through the values

Sub t()
Dim c
For Each c In ActiveWorkbook.Names("SubTotal").RefersToRange.Cells
MsgBox c.Value
Next
End Sub

:O)Bruintje

Richie_Simonetti

<offtopic>
Hi bruintje! where are you when we need you?
could you see this:
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msoffice&qid=20299301
</offtopic>

Brian Mulder

<OT>i tried something and bk has repeated that in his recap :) i'm EE tired, programming tired and maybe i need a few days off on the beach whenever summer in the Netherlands is kind enough to make that happen<OT>

Richie_Simonetti

Rest, rest... who needs a keyboard to live anyway?
:)

roverm

<continue offtopic>
Brian:
Well, we should get nice weather Saturday and Sunday so...enjoy life! Then we can get some points as well ;-)
</continue offtopic>

lmindlin:

Yes, I would use the named ranges as well.
However, if you don't like using that, how about creating a "shadow" sheet, hidden for the user (Sheet.Visible = xlVeryHidden) ?
Then use the SelectionChange event to capture the contents of a cell and copy it, along with your properties to the hidden sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Debug.Print Target.Address(, , xlR1C1)
'build copy code here
End Sub

D'Mzzl!
RoverM

Brian Mulder

<My last OT response here> Mark > weather better be nice since i got to do some work on my new bicycle away from a keyboard.....points enough for everyone these days ;) more then 80000 on Open Q's in Office alone</OT>

Richie_Simonetti

Thanks for "A" grade but i think bruintje has explained the point better than me.