• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 128
  • Last Modified:

Error 1004

Folks,
In the attached workbook I keep getting an error after I've added a Topic that takes me to the Topic's w/s.
Dashboard errorError locationAlthough I've done this before obviously I've missed something.
Excel-Dashboards.xlsm
0
Frank Freese
Asked:
Frank Freese
  • 12
  • 11
1 Solution
 
Martin LissRetired ProgrammerCommented:
In Topics there's no data in column D which is what "Offset(0, 1)" is referring to.
0
 
Martin LissRetired ProgrammerCommented:
Wait that's not it. In the Name Manager you have the range spelled "DisplayPrinciples" and in the code "DisplayPrincipals".
0
 
Frank FreeseAuthor Commented:
Changes made - but now it is when I double click it is telling me that the 'Go To select' sheet name for you selection needs to be added to sheet 'SheetNames'
I promise you the worksheet name is listed and spelled correctly (yes, I made sure this time) in SheetNames
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Frank FreeseAuthor Commented:
Here's what I'm using
Excel-Dashboards.xlsm
0
 
Martin LissRetired ProgrammerCommented:
OK there were two problems.
1. I had explained in a previous thread that when intCatFactor is calculated it should be a count of all the Topics before it so in Menu's Worksheet_SelectionChange sub, case 4,

it should be
intCatFactor = Sheets("Topics").Range("Introduction").Rows.Count

Open in new window

rather than

intCatFactor = Sheets("Topics").Range("DisplayPrinciples").Rows.Count

Open in new window


2. In the BeforeDoublkeClick you neglected to add the code that uses the scroll bar's position so

it should be this
    strSheetName = Worksheets("SheetNames").Range("ShowSheet")(intCatFactor + Target.Row + Sheets("ScrollbarData").Range("B2") - 4)

Open in new window


rather than this.
   
strSheetName = Worksheets("SheetNames").Range("ShowSheet")(intCatFactor + Target.Row - 2)

Open in new window

If you don't 100 percent understand the code let me know which part and I'll explain it because given my age who knows?
0
 
Martin LissRetired ProgrammerCommented:
I just found one more thing, and this one's on me. In Case 3 intCatFactor s/b 1 and not 0 (it bothers me that that's required because it doesn't follow my "count of everything before it" rule but it works)

 
       Case 3
            GenFormulas "A", Sheets("Topics").Range("Introduction").Rows.Count
            'new
            intCatFactor = 1

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
@#$#%^$$! There's still a problem so don't make any changes yet.
0
 
Frank FreeseAuthor Commented:
First question
I saw my error in Case 4 in Select Case Target.Row but I don't see where intCatFactor is being indexed.
Shouldn't we be doing this:
Case 4
            GenFormulas "C", Sheets("Topics").Range("DisplayPrinciples").Rows.Count
            intCatFactor = 1 + Sheets("Topics").Range("Introduction").Rows.Count

If that's the case then in Case 3
intCatFact = 0
in you post 40256579
0
 
Frank FreeseAuthor Commented:
I think I just posted it
0
 
Frank FreeseAuthor Commented:
intCatFact = 1 not 0
0
 
Martin LissRetired ProgrammerCommented:
No, that's not correct. Rather my instincts were correct. The thing that was bothering was wrong. I've now tested it thoroughly and here is what the code should look like.

In Menu sheet SelectionChange the code should be

        Case 3
            GenFormulas "A", Sheets("Topics").Range("Introduction").Rows.Count
            'new
            intCatFactor = 0
        Case 4
            GenFormulas "C", Sheets("Topics").Range("DisplayPrinciples").Rows.Count
            'new
            ' intCat factor must be the sum of counts of all the PREVIOUS topics
'            intCatFactor = Sheets("Topics").Range("DisplayPrinciples").Rows.Count
            intCatFactor = Sheets("Topics").Range("Introduction").Rows.Count

Open in new window


and in BeforeDoubleClick the calculation should be

strSheetName = Worksheets("SheetNames").Range("ShowSheet")(intCatFactor + Target.Row + Sheets("ScrollbarData").Range("B2") - 3)

Open in new window

Note that this has  - 3 rather than - 4 as in post ID 40256551 and that's the change I should have made earlier, rather then changing intCastFactor to 1 from 0.

Confused? I don't blame you. Try to make these two changes and test it out. If it doesn't work, post your updated workbook and I'll fix it.
0
 
Frank FreeseAuthor Commented:
Not really too confused....it works
Now when I go to add another Category it would look like this in the code?
Case 5
            GenFormulas "E", Sheets("Topics").Range("Formatting").Rows.Count
            'new
           ' intCat factor must be the sum of counts of all the PREVIOUS topics
            intCatFactor = Sheets("Topics").Range("Introduction").Rows.Count
            intCatFactor = Sheets("Topics").Range("DisplayPrinciples").Rows.Count
0
 
Martin LissRetired ProgrammerCommented:
I'm sorry but I guess I'm not explaining it well enough. My comment in the code says "' intCat factor must be the sum of counts of all the PREVIOUS topics and I guess I should have capitalized sum because the code you just posted doesn't sum the two previous. All it does is to set it to the count of "Introduction" which in the next line is overwritten by the count of "DisplayPrinciples". So the code s/b
Case 5
            GenFormulas "E", Sheets("Topics").Range("Formatting").Rows.Count
            'new
           ' intCat factor must be the sum of counts of all the PREVIOUS topics
            intCatFactor = Sheets("Topics").Range("Introduction").Rows.Count _
                                  + Sheets("Topics").Range("DisplayPrinciples").Rows.Count
0
 
Frank FreeseAuthor Commented:
OK - now that makes sense to me, which is the way we did it in he past. Thanks for clearing that up.
0
 
Frank FreeseAuthor Commented:
thanks Martin - great job explaining
Appreciate you very much
0
 
Martin LissRetired ProgrammerCommented:
As a test for the student, explain in detail what is going on here:)

strSheetName = Worksheets("SheetNames").Range("ShowSheet")(intCatFactor + Target.Row + Sheets("ScrollbarData").Range("B2") - 3)
0
 
Frank FreeseAuthor Commented:
We're assigning to a variable strSheetName that if found in our SheetsNames tab takes us to that tab - else we need to enter a sheet name.checking to see if we have a corresponding sheet
0
 
Martin LissRetired ProgrammerCommented:
We're assigning to a variable strSheetName that if found in our SheetsNames tab takes us to that tab...
That part is correct, but i'm hoping to teach you why the formula is the way it is. In other words what do the various sheet and variable references in that line of code represent? If you can figure out why - 4 was wrong and - 3 was right you'll know the answer to the test.
0
 
Frank FreeseAuthor Commented:
Can I have my points back? :)

strSheetName = Worksheets("SheetNames").Range("ShowSheet")(intCatFactor + Target.Row + Sheets("ScrollbarData").Range("B2") - 3)
I suspect you're referring to the Case statements?
Case 4 was incorrect because you weren't indexing intCatFact
Case 3 was correct because you were setting up the first category
BTW I italicized the second half - I don't recall seeing a formula written that way ()()
0
 
Martin LissRetired ProgrammerCommented:
Here's the explanation.

Let’s let me break down that line of code for you.

Assume that ‘Display Principles’ is selected and the user double-clicks the ’Smoothing Data’ topic.

The first part of the code refers to Worksheets("SheetNames").Range("ShowSheet")

As I’m sure you know, the sheet “SheetNames” when it’s completed will contain a single list of the names of the sheets in the workbook and “ShowSheet” is a Named range that refers to that list. For the sake of this explanation let’s assume that the list looks like this:
Introduction
GetDataReady
DesignPrinciples
ChartsTricksTrending
SecondaryAxis
SmoothingData

and since ‘Smoothing Data’ was double clicked it’s our job to point to the “SmoothingData” row.

So the the rest of the code which is (intCatFactor + Target.Row + Sheets("ScrollbarData").Range("B2") - 3) is a calculation that results in a row number in the Named Range and in this case it needs to be 6 because SmoothingData is the 6th row of SheetNames. If all the Topics for all the Categories were displayed all at once in the topics list and all the topics could be seen at once without scrolling, ‘Smoothing Data’ would be the 6th in the list and it would be a very simple matter simple matter to relate that 6th row to the 6th row of ’Show Sheet’ ( all you would need to do would be to compensate for the 2 rows above the Topics, but in our case it’s more complicated.

The first complication is the fact that only the Topics for ‘Display Principals” are shown and to come up with 6 from a list that shows ’Smoothing Data’ as the 3rd item we need to know how many items ‘Introduction to Dashboards’ would have contributed to our imaginary show-all-topics list, and that’s where our friend intCatFactor comes into play. As you know now it’s the sum of the rows in all previous topics which in our case is 3 since there are three items in ‘Introduction to Dashboards”

Target.Row gives us 5 (rather than 3 because it includes the 2 rows above Topics that I mentioned above) and so far intCatFactor + Target.Row gives us 8 (3 + 5) but that’s not the right answer yet.

If there never was any scrolling we could easily come up with 6 by subtracting 2 but since there is sometimes scrolling we need to talk about it.

The first thing to understand is that while the Topics list for ‘Display Properties’ (or any other Category) looks like a listbox list, it’s not, Instead it’s a bunch of individual cells filled via formulas that are generated in the GenFormulas sub. The scrolling is accomplished via a the Form Control scrollbar which I named vscrTopic (“vscr” is from Vertical Scrollbar”). If you go into Design Mode at right click on that scrollbar and click ‘Properties’ from the ‘Developer’ pane you’ll see that the ‘Cell Link’ property is set to range(B2)  of the ScrollbarData sheet. With that assignment the scrollbar automatically updates B2 with the index of the first item that is visible so initially “Charts and Tricks for Trending” is on top so B2 is 1 but if you scroll down 1 so that “Secondary Axis” is on top, B2 will be 2.

So let’s assume now that we do scroll down so that “Secondary Axis” is on top and we double-click the ’Smoothing Data’ topic. In that case intCatFactor will still be 3 but Target.Row will be 4 and B2 will be 2, and that results in 3 + 4 + 2 = 9 which still isn’t the right answer but including - 3 does result in the correct answer of 6.

Why “- 3” you ask. Well to tell you the truth I don’t remember (and I had it wrong before) so I’ll get back to you, but until then consider it a “fudge factor”.

Phew! BTW I realize that it's complex but it's important that you understand what's going on in case you need to make changes or you need to explain it to someone else.
0
 
Frank FreeseAuthor Commented:
It is important to me and I am very grateful. I guess you've figured out that I some things I ask challenging. Your explanation here was really fantastic and helpful. After dropping the ActiveX for Category and Topic it did became more complicated.  Please keep the questions coming. It's been years since I coded and then it was in Access. Excel VBA is still new to me.

The previous question regarding pulling values from a formula was design to help the person better understand the function. If they can see the arguments listed rather than read about arguments they have a better chance of understanding the data.

Although I have lots of reference books I find the Internet a great resource. I'll try to find out first if what I'm asking for someone else has been there with an understandable solution. If so, I'll beat the beast then post a question.

There still something that bugs me with this:
strSheetName = Worksheets("SheetNames").Range("ShowSheet")(intCatFactor + Target.Row + Sheets("ScrollbarData").Range("B2") - 3)  
In the above example you gave the results of the bolded calculation as a 6. I believe it means to look at the 6th line
for a worksheet named in "SheetNames". If there is not one then I'm to add a sheet name. How does the value 6 marry back to the first part of  the formula (not bolded)?

I know I do this a lot:
With Worksheets("RANKEQ")
.Range("O7") = 8
.
.
End with

Is:
strSheetName = Worksheets("SheetNames").Range("ShowSheet")(intCatFactor + Target.Row + Sheets("ScrollbarData").Range("B2") - 3)
doing something similar?
0
 
Martin LissRetired ProgrammerCommented:
strSheetName = Worksheets("SheetNames").Range("ShowSheet")(intCatFactor + Target.Row + Sheets("ScrollbarData").Range("B2") - 3) 

Open in new window



is exactly the same as the following where .rows is added

strSheetName = Worksheets("SheetNames").Range("ShowSheet").rows(intCatFactor +Target.Row + Sheets("ScrollbarData").Range("B2") - 3)

Open in new window


and if it makes it clearer for you then use that version.
0
 
Frank FreeseAuthor Commented:
now that I understand! thanks...
until I need to think in color again have a good night
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now