Cell Calculation 1

[cranebill]

Not sure if Im putting this in the right forum, however im pretty sure I will need this accomplished with vba.

I have several check boxes which upon checking them hides columns on a report. By hiding these columns the user does not wish to see these programs which the columns represent. Now for my issue.

I have a totals column which I need to tabulate the columns that are still visible and as each new program is checked will update the total based on whether a column was made visible or hidden.

I hope this made sense, thanks for any insight you can provide.

 

[SkipVought]

Hi,

No VBA required.

Check out the SUBTOTAL function. One set of function_Num's will not aggregate hidden cells.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cranebill]

To my understanding subtotal only worked with filtered results, not if you manually(or vba by clicking a checkbox) hid columns. I will look into it thanks.

 

[cranebill]

Another thing to note... it is specific cells per row...

C10, AR10, CD10 and so forth... not the entire row

 

[SkipVought]

I believe that you are correct.

Using VBA, you can evaluate the RangeObject.entirecolumn.hidden property. If FALSE, then aggregate.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

=subtotal(109,range) only sums visible cells... though lookup help on the function for alternate values for the first parameter

In code use something like

Sub Macro1()
Dim myFormula As String
myFormula = "=SUM(" & Range("A2:A10").SpecialCells(xlCellTypeVisible).Address & ")"
ActiveCell.Formula = myFormula
End Sub

Gavin

 

[Gavona]

Cranebill: 109 ignores hidden columns as well

Gavin

 

[SkipVought]

"C10, AR10, CD10 and so forth... not the entire row"

What makes them candidates for aggregation and others not?

Would this not work...
[tt]
=subtotal(109,C10,AR10,CD10)
[/tt]




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

or:
myFormula = "=SUM(" & Range("C10,AR10,CD10").SpecialCells(xlCellTypeVisible).Address & ")"

Gavin

 

[cranebill]

I tried both ways...

using skips subtotal example I still get hidden values

I could not get Gavins code to work.

I did however find a workaround, which will be tedious but will work.

I added code to the checkbox so that when a checkbox = true it sets the cell value to a vlookup(which is how it originally was populated) and when false sets the value to 0.

There has to be an easier way though lol

 

[SkipVought]

Nice on Gavin!!!

"I could not get Gavins code to work."
[tt]
[A10].Formula = "=sum(" & Union([C10], [F10], [H10]).SpecialCells(xlCellTypeVisible).Address & ")"
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Skip: What is the advantage of Union over Range?

Gavin

 

[SkipVought]

personal preference, another example.

no real advantage.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

[Quote:Cranebill] using skips subtotal example I still get hidden values
I could not get Gavins code to work.[/quote]
Well if you give us a bit more detail then we will try to help.
How are the columns hidden?
What code did you try and what error or unexpected result did you get?

Gavin

 

[cranebill]

The code to hide the columns is as follows:

Private Sub CC_Click()
If CC.Value = False Then
      Columns("C").Hidden = True
      Range("C10").Value = 0
       CCREG.Value = False
       CCREG.Enabled = False
   Else
      Columns("C").Hidden = False
      CCREG.Enabled = True
      End If
End Sub

This is a checkbox used to denote participation in a particular program.
The cell with the total is AS10. It is summing cells C10 and X10 at the moment I will add more later when I get these two working. Column AS10 would also need to refresh automatically if possible. I have found some examples that work, however it requres a manual refresh.

As far as your code Gavona maybe i am putting it in the wrong place or not calling it correctly. Apparently it works as Skip got it to work. I apologize as it has been a while since I have used excel on this level and I am just trying to do a little automating to get this report that takes someone 16 hours to create to be more efficient and less time consuming.

 

[SkipVought]

It works as I have coded it.

1 a Cell Object
2 the FORMULA property for the Cell Object
3 the STRING to assign

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cranebill]

Ok I guess my question would now be where do I store the code Gavona provided (with your update) and what is the proper way to call this from within the cell.

 

[cranebill]

Apparently I have asked the wrong question lol...

I have used functions in excel however not that familiar with subroutines. How can I get this to work.. you guys obviously have more experience with this than I. I have searched and searched and tried different variations etc.

Still cant get it to work and obviously it is not code related but more of a process.

 

[Gavona]

Firstly,
The SUBTOTAL function I originally posted does NOT work in your situation - sorry. That function (as explained in help) is not designed for rows of data, or horizontal ranges.

So I don't think a function will help you. You need a Macro that is called by the routine that hides the columns.

I have several check boxes which upon checking them hides columns on a report.

The code I posted was a macro to create a formula in a single cell - the currently selected cell if only one cell is currently selected. You can replace ActiveCell with anything that results in a range - eg Range("C10") or Range("C2:C10").

Have you got a version of my/Skip's code working so that when you run the macro it works as you expect? I suspect that you might have something that works ok in the first row - so demonstrates the concept - but needs adjusting to copy correctly down a range of cells (if that is a requirement). Suggest you get as far as you can and then post the code that you are working on with detail of any issues that you need help resolving.

Gavin