Solved

Posted on 2014-08-12

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.

Although I've done this before obviously I've missed something.

Excel-Dashboards.xlsm

In the attached workbook I keep getting an error after I've added a Topic that takes me to the Topic's w/s.

Although I've done this before obviously I've missed something.

Excel-Dashboards.xlsm

23 Comments

I promise you the worksheet name is listed and spelled correctly (yes, I made sure this time) in SheetNames

Here's what I'm using

Excel-Dashboards.xlsm

Excel-Dashboards.xlsm

1. I had explained in a previous thread that when intCatFactor is calculated it should be a count of all the Topics

it should be

```
intCatFactor = Sheets("Topics").Range("Introduction").Rows.Count
```

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

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)
```

rather than this.

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

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?
```
Case 3
GenFormulas "A", Sheets("Topics").Range("Introduction").Rows.Count
'new
intCatFactor = 1
```

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("Di

intCatFactor = 1 + Sheets("Topics").Range("In

If that's the case then in Case 3

intCatFact = 0

in you post 40256579

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
```

and in BeforeDoubleClick the calculation should be

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

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.

Now when I go to add another Category it would look like this in the code?

Case 5

GenFormulas "E", Sheets("Topics").Range("Fo

'new

' intCat factor must be the sum of counts of all the PREVIOUS topics

intCatFactor = Sheets("Topics").Range("In

intCatFactor = Sheets("Topics").Range("Di

Case 5

GenFormulas "E", Sheets("Topics").Range("Fo

'new

' intCat factor must be the sum of counts of all the PREVIOUS topics

intCatFactor = Sheets("Topics").Range("In

+ Sheets("Topics").Range("Di

OK - now that makes sense to me, which is the way we did it in he past. Thanks for clearing that up.

strSheetName = Worksheets("SheetNames").R

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

strSheetName = Worksheets("SheetNames").R

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 ()()

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

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

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

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

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.

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").R

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").R

doing something similar?

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

is exactly the same as the following where

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

and if it makes it clearer for you then use that version.

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

Connect with top rated Experts

**19** Experts available now in Live!