Solved

Flagging excel cells in VBA

Posted on 2002-05-10
8
290 Views
Last Modified: 2010-05-02
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
Comment
Question by:lmindlin
  • 4
  • 3
8 Comments
 
LVL 16

Accepted Solution

by:
Richie_Simonetti earned 50 total points
ID: 7002293
could you use names?
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7002327
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7002353
<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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 44

Expert Comment

by:bruintje
ID: 7002368
<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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7002440
Rest, rest... who needs a keyboard to live anyway?
:)
0
 
LVL 12

Expert Comment

by:roverm
ID: 7002452
<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
 
LVL 44

Expert Comment

by:bruintje
ID: 7002479
<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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7002748
Thanks for "A" grade but i think bruintje has explained the point better than me.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question