Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

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?
0
lmindlin
Asked:
lmindlin
  • 4
  • 3
1 Solution
 
Richie_SimonettiIT OperationsCommented:
could you use names?
0
 
bruintjeCommented:
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
0
 
Richie_SimonettiIT OperationsCommented:
<offtopic>
Hi bruintje! where are you when we need you?
could you see this:
http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msoffice&qid=20299301
</offtopic>
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
bruintjeCommented:
<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>
0
 
Richie_SimonettiIT OperationsCommented:
Rest, rest... who needs a keyboard to live anyway?
:)
0
 
rovermCommented:
<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
0
 
bruintjeCommented:
<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>
0
 
Richie_SimonettiIT OperationsCommented:
Thanks for "A" grade but i think bruintje has explained the point better than me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now