Using Named Ranges to allow for Inserting Cells Within a VBA Coded Range

[timmymckinney]

Hello all!

Attached is a sample of the sheet I am working on. I am pretty new to VBA and so far my code is working as intended. So far, this is what my code does: Whenever data is entered in any cell within the range K10:S41 excel will run a script which copies that cell information into other sheets (range K10:K41 copies into sheet 2 C9:C40, L10:L41 copies into sheet 3 C9:C40 etc.), next there is a script within sheets 2-9 that has automatically hides or unhides rows based on the value of cells C9:C40. So far, so good.

Now a problem arises when I run out of rows and need to insert rows within my sheets. The way I understand it, I have written the VBA code such that the coded range doesn't change when inserting cells, thus VBA doesn't apply the code to the new cells/rows. I believe I need to change the code to refer to Named Ranges rather than "hard coded" ranges yet I don't know exactly how to do that. Also, since the formatting of sheet one corresponds with the formatting of sheets 2-9 I believe I would need to add a VBA code that would recognize whenever I insert a row on sheet 1 within the range of rows 10:41 the code also inserts corresponding rows within sheets 2-9 rows 9:40 (i.e. if I insert a row on sheet 1 in between rows 10 & 11 the code would automatically insert rows within sheets 2-9 and in between rows 9 &10). I believe this would keep my formatting problems under control.

Recap: I would greatly appreciate it if anyone could help me: first, get my VBA code to allow me to insert rows (the range the code applies to would grow if rows were inserted within that range), and second, write a code that would insert rows as described above.

Thanks!

[jaslake]

Hi timmymckinney

Welcome to the Forum!

The attached has Code that appears to do this

change the code to refer to Named Ranges

and this

if I insert a row on sheet 1 in between rows 10 & 11 the code would automatically insert rows within sheets 2-9 and in between rows 9 &10)

The Named Ranges are dynamic such that, when you insert a new row the Named Ranges are updated. The Insert Code runs from a Button on Total Labor.

Please note the Formula in Cell E42 of each Goal Sheet. Let me know of issues.

[timmymckinney]

jaslake,

First off, thank you for your help and the warm welcome! I have a couple of questions and one slight problem. First, just to get an understanding on what you did, is everything which is green in VBA my original code and the ' makes that code inactive? Second, I see that you named the ranges Goal1, Goal2, Goal3 etc., but how exactly does that work? What specifies the cells within the range? To my eyes, it looks like the cells within the named range "Goal1" are completely arbitrary, yet the code works so I know that is not the case. Lastly, I want to take the code you used for the button and make it automatic,i.e. whenever you insert a row on the Total Labor sheet that falls between rows 10-41, the code automatically inserts the appropriate rows on each sheet. Would that be possible?

Lastly, the problem I noticed is that the rows in Sheets "Goal 1"-"Goal 8" are not un-hiding themselves. Any numbers that are entered in the Total Labor sheet within the range K10:S41 are transferred to the appropriate columns in the corresponding sheets. Any row with a 0 in the named range is automatically hidden but if I change the number from a 0 to any other number, those rows stay hidden. It was working before but I can't quite figure out why it isn't now.

Thanks!

[jaslake]

Hi timmymckinney

Here's a good primmer on Dynamic Named Ranges that will help you understand the apparent "arbitrariness".
http://www.ozgrid.com/Excel/DynamicRanges.htm

Regarding this

is everything which is green in VBA my original code and the ' makes that code inactive

I'm color blind but, yes, if the line of Code has an apostrophe in front of it, that line of Code is skipped ("commented out").

Regarding this

take the code you used for the button and make it automatic

Off the top of my head I don't know of a way to do that. I'll do a bit of research...get back to you on this.

This I'll need to look into

the rows in Sheets "Goal 1"-"Goal 8" are not un-hiding themselves

[jaslake]

Hi timmymckinney

I found some references to this...let me play with it a bit...

take the code you used for the button and make it automatic

[jaslake]

Hi timmymckinney

The Code in the attached has been modified to allow this

whenever you insert a row on the Total Labor sheet that falls between rows 10-41, the code automatically inserts the appropriate rows on each sheet

This appears to be working

Any row with a 0 in the named range is automatically hidden but if I change the number from a 0 to any other number, those rows stay hidden

Let me know of issues.

[timmymckinney]

Jaslake,

Thank you so much for your quick responses and help! When I open the excel file I am getting a '424' runtime error, Object Required. This error is preventing the auto insert code you created from working. This is a picture of what the debugger is returning.Debugger Image.jpg

Also, I am still having trouble getting the auto-unhide to work. The most recent excel file you attached, v4.2, has an input of 1 on sheet "Total Labor" range L10:S41. If I change that entire range to 0 then rows 9-40 are hidden on sheets Goal 1-Goal 8. However, if I change range L10:S41 back to 1, those rows are still hidden. Maybe this is a problem that only I am experiencing?

Sorry for all the trouble. You are helping me learn alot though!

[timmymckinney]

Also, Jaslake, thank you for the link

Here's a good primmer on Dynamic Named Ranges that will help you understand the apparent "arbitrariness".
http://www.ozgrid.com/Excel/DynamicRanges.htm

For some reason I was expecting to find the "naming of ranges" to happening within VBA ; now I see that I was mistaken. That solved my brief but real confusion over what I had found searching the web.

[jaslake]

Hi timmymckinney

Named Ranges can be created with VBA...in this instance I predefined them in Excel.

Regarding this

When I open the excel file I am getting a '424' runtime error, Object Required

What version of Excel are you running?

[timmymckinney]

What version of Excel are you running?

2007, is that problematic?

[jaslake]

Nope...that's what I'm using and I'm not experiencing either issue. I'll play with it a bit to see if I can break it.

[jaslake]

Hi timmymckinney

I was able to duplicate this error

If I change that entire range to 0 then rows 9-40 are hidden on sheets Goal 1-Goal 8. However, if I change range L10:S41 back to 1, those rows are still hidden.

I've modified the Code for the Sheet Change Events and the Code now appears to work under the scenario you described.

I'm unable to duplicate this error

Please Login or Register  to view this content.

Does this happen EVERY time you open the attached File?

[timmymckinney]

Jaslake,

Sorry I have not been able to get back to you. Thank you for fixing the following error:

If I change that entire range to 0 then rows 9-40 are hidden on sheets Goal 1-Goal 8. However, if I change range L10:S41 back to 1, those rows are still hidden.

It is now working!


However, I am still getting the runtime error every time I open the excel file. Maybe it is a setting on my side? Or a module I am missing?

This is a screenshot of right when I open the most recent excel file (version 4.4):
First Open.jpg

and a picture of the debugger:

Debugger Error.jpg

Thank you for everything you have done! Is there any other information I can give you that might help solve this last problem?

[jaslake]

Hi timmymckinney

I've opened and closed the File about 15 times and cannot duplicate the error. Hover over lngId (see jpg) and tell me what it says.

6-25-2013 2-33-30 PM.jpg

[timmymckinney]

jaslake,

Here are some pics of what IngID is returning plus hovering over some of the nearby code:

Hover 1.jpg
Hover 2.jpg
Hover 3.jpg

[jaslake]

Hi timmymckinney

Can you tell what it says for lngId when you get the error message?

[timmymckinney]

jaslake,

I am not quite sure what you mean. Right when I open the file (before I do anything) I get a runtime error. I then clicked debug and hovered over IngId and included a picture of what it says. What exactly do you mean by "when I get the error message"? I receive the error message when I open the file and when I close it. If I try to insert cells, I am able to but the code doesn't run. I hope that clarifies my issue.

[jaslake]

Hi timmymckinney

I simply cannot duplicate this issue. After dinner I'll post this issue in the Water Cooler and ask the REAL Forum Gurus to look at the issue...I'll get back to you.

[timmymckinney]

jaslake,

Thank you so much for the help! Even if this doesn't get figured out, I greatly appreciate it.

[jaslake]

Hi timmymckinney

You can follow this link. I've called in the Calvary to see if we can solve this issue.

http://www.excelforum.com/the-water-...html?p=3296261

[jaslake]

New File uploaded...EndRow is defined in Module Globals...don't know why it was commented out in the original upload...brain freeze...

[timmymckinney]

jaslake,

I had a coworker attempt to open the file and he received the same error. I'll keep testing stuff on my end but it doesn't make sense why both you and abousetta are able to open it with no runtime errors but my coworker and myself are not. Strange...

[jaslake]

Hi timmymckinney

I'm still looking for an alternate soluntion...

[jaslake]

Hi timmymckinney

In this version I've removed Row Insert from the Right Click Context Menu and replaced it with My Insert. Click on My Insert to do the Row Insertion.

The Code then resets the Right Click Context Menu to it's original state before you close the Workbook.

It seems do do as expected for me...how about you.

[timmymckinney]

jaslake,

I don't get a runtime error when opening that file and it seems that the My Insert is functioning as intended. Looks like that might be a solution. Not quite sure why but it worked!

Thanks for all your work!

[jaslake]

Hi timmymckinney

Well, I hope we found a "fix" for you...keep me posted...mark your Thread "Solved" as appropriate.

[timmymckinney]

jaslake,

Will do! I'll start using it and let you know how it works out. Thank you for the work around and everything you have done.

Tim