Excel Programing

[Just This Guy]

Anyone here have experience writing macros in Excel? I am working on a project for work and can't seam to get it to work the way I want it to. I was wondering if anyone here can help mu trouble shoot what I am doing wrong.

[Corsair]

Maybe. What are you trying to do?

[Just This Guy]

I'm trying to create a universal reporting form. So based on what they select in a specific cell, the sheet would automatically hide or unhide certain rows.

[Corsair]

The macro programming does include commands for hiding rows and columns such as:

Rows(2).Hidden = True

That's a full macro programming command and not necessarily an update based in a cell formula. I do not know of a cell formula that would change the "hide" property of a row or column. It seems like something that should exist.

[Reuben]

What Corsair said, except that a formula that alters cell attributes should not exist.

(I can make bold statements like this because I'm part of a team at Microsoft Research that consults with Excel's designers on the design of the formula language. I'll follow that posturing up with an explanation, though.)

One of the most important properties of workbook recalc - the cell value updates that happen after you alter a formula - is that it doesn't matter what order the formulas are recalculated in. If order mattered, you would get different answers depending on which cells you edit, because recalc only recalculates the edited cells and the cells that depend on them. Finance companies across the globe would rebel and switch to Google Sheets. Auditors would cry themselves to sleep.

Because there are worksheet functions that are sensitive to which rows are hidden, such as the frequently used SUBTOTAL which can skip hidden rows, allowing a formula to change the hidden attribute would make recalc order matter. If one formula hid a row and another formula showed it, any SUBTOTAL that included that row would give different answers depending on whether each of those formulas was recalculated and in which order. The last recalculated would always have its way.

To avoid all such ambiguities now and in the future, Excel locks down every open workbook during recalc. Not even VBA functions can change them during recalc.

--

Just This Guy: I think you can make Excel do what you want anyway. Corsair's command should work as long as it's run after recalc.

This looks like the kind of thing you want:

https://docs.microsoft.com/en-us/office ... lls-change

I don't know a lot about VBA, but I think by the time Worksheet_Change is called, the cells you want to monitor will have their new values in them, and the workbook will be unlocked so you can hide or show rows.

[Red Ryder]

Wow Reuben, I think I had an excelgasm!

Keep talking dirty man, that’s sexy stuff!

[Reuben]

Wow Reuben, I think I had an excelgasm!

Keep talking dirty man, that’s sexy stuff!

Drag-fill. Relative references. Manual recalc. NPV. Data validation. Name manager! Conditional formatting!

Did you know that if A1 is blank, then A1=False and A1=0 and A1=""? That's my dirtiest secret.

[wtfluff]

Wow Reuben, I think I had an excelgasm!

Keep talking dirty man, that’s sexy stuff!

Drag-fill. Relative references. Manual recalc. NPV. Data validation. Name manager! Conditional formatting!

Did you know that if A1 is blank, then A1=False and A1=0 and A1=""? That's my dirtiest secret.

What aboot pivot tables?

[Reuben]

Wow Reuben, I think I had an excelgasm!

Keep talking dirty man, that’s sexy stuff!

Drag-fill. Relative references. Manual recalc. NPV. Data validation. Name manager! Conditional formatting!

Did you know that if A1 is blank, then A1=False and A1=0 and A1=""? That's my dirtiest secret.

What aboot pivot tables?

Ssshhh! Do you want to get him PREGNANT?

[wtfluff]

Drag-fill. Relative references. Manual recalc. NPV. Data validation. Name manager! Conditional formatting!

Did you know that if A1 is blank, then A1=False and A1=0 and A1=""? That's my dirtiest secret.

What aboot pivot tables?

Ssshhh! Do you want to get him PREGNANT?

Boy Howdy!

(How soon before a mod marks this thread NSFW? )

[Red Ryder]

What aboot pivot tables?

Ssshhh! Do you want to get him PREGNANT?

Boy Howdy!

(How soon before a mod marks this thread NSFW? )

Aboot? Now your adding a Canadian accent with excel dirty talk? How do you type drooling sounds?

Confession: my girlfriend was Canadian in high school and so I have this thing for Canadian accents. One of the ladies in our corporate office is Canadian so I’ll sometimes call and ask random questions just to chat her up just to hear a thick heavy accent.

[Reuben]

Ssshhh! Do you want to get him PREGNANT?

Boy Howdy!

(How soon before a mod marks this thread NSFW? )

Aboot? Now your adding a Canadian accent with excel dirty talk? How do you type drooling sounds?

bwleuuuhhfup. mlehhhchchghugfffffffffp. *wipes cheek*

[wtfluff]

Ssshhh! Do you want to get him PREGNANT?

Boy Howdy!

(How soon before a mod marks this thread NSFW? )

Aboot? Now your adding a Canadian accent with excel dirty talk? How do you type drooling sounds?

Confession: my girlfriend was Canadian in high school and so I have this thing for Canadian accents. One of the ladies in our corporate office is Canadian so I’ll sometimes call and ask random questions just to chat her up just to hear a thick heavy accent.

Doh.

Sorry aboot that. Dang it... Did it again!

I only pretend to be from the Great White North now and again...