Additional Functionality

Folks,
The attached spreadsheet is very awkward. I would like to remove all the Reselect and Goto command buttons and chose a topic from one of the combo boxes and then select that topic moving directly to the worksheet.
Function-and-Formulas-for-Excel-.xlsm
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Working on it.
0
Martin LissOlder than dirtCommented:
Here's a first draft based on an older version of your workbook. More to come:)
Jut-One-GoTo.xlsm
0
Frank FreeseAuthor Commented:
Got an error on Jut-One-GoTo:
Error 13: Type mismatch
Private Sub GoToSelection_Click()
'new
    GoToSelection.Enabled = False
    Sheets(arrTextGoto(intListIndex)).Activate
    Sheets(arrTextGoto(intListIndex)).Select
End Sub
0
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Martin LissOlder than dirtCommented:
I'll check into it when I am testing my modifications to the workbook you just posted.
0
Frank FreeseAuthor Commented:
understood - tomorrow
0
Martin LissOlder than dirtCommented:
Here's the new version. I detailed most of the changes I made in a new module called modChanges. You should read it. I also found some dead code (subs that aren't called from anywhere). I commented out the ones I found but I suspect that there's more and you should try to delete them. I found one case where a sub was dead and it was the only sub in the module so that module should be removed.

BTW since we've now significantly reduced the number of controls, the workbook loads faster than it did. It's still not fast, just faster.

I don't know how much you know about debugging but here's an article I wrote on the subject. Don't be put off by the title since most of it also applies to VBA.
Q-28231152.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
A couple of comments, but looks great - really great
1. The screen is cut off at the GoTo selection
2. Tab (worksheets) are all missing
3. It appears from your code that there is a worksheet that functions like a library of worksheets.
4. Is this a protected workbook?
0
Frank FreeseAuthor Commented:
I saved and renamed your work and now everything appears. I can look at it better now and let you know.
0
Frank FreeseAuthor Commented:
This is simply fantastic -
As I understand what you've done all I need to do is maintain SheetNames and modify the code below when adding more combo boxes?
Private Sub cboTextFunctions_Click()

    intListIndex = cboTextFunctions.ListIndex + 60
    ResetIndicators
    Invisible cboTextFunctions
   
End Sub

Open in new window

This is just beyond anything I expected
0
Frank FreeseAuthor Commented:
I thought I'd start to build my SheetNames by copying the names for each topic then pasting it to the sheet names. Do you know of any reason the Copy function (from the formula bar) is not working?
0
Frank FreeseAuthor Commented:
Forget that last post on the Copy function - just another senior moment
0
Frank FreeseAuthor Commented:
Ready for another new question?
0
Frank FreeseAuthor Commented:
This is simply a fantastic job!
Thank you so very much
0
Martin LissOlder than dirtCommented:
As I understand what you've done all I need to do is maintain SheetNames and modify the code below when adding more combo boxes?
I believe all you need to do is to complete the SheetNames list. Everything else should take care of itself.

To get a list of the sheet names you can add this (untested) to any module

Sub ListNames()

Dim ws As Worksheet

For Each ws in Worksheets
    Debug.Print ws.name
Next
End Sub

Open in new window

and run it. You can then copy the names you need from the Immediate Window. BTW you can run it from the Immediate Window if you like by just typing ListNames and pressing return.

And as for a new question - sure,

As always you're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
Frank FreeseAuthor Commented:
You answered my new question
"How can I get a list of my sheets names?"
But I'll still post it
0
Martin LissOlder than dirtCommented:
What do you think  of this?
0
Frank FreeseAuthor Commented:
The video was great way to make a point
I've downloaded it and will look at it
0
Frank FreeseAuthor Commented:
Martin,
I did a compile and came across an error "Variable not defined" at
ActiveSheet.Name = "SheetList"
Should I change that the "Sheetnames"?

Public Sub ListWorkSheetNames()
Dim Sheetnames
Sheetnames = Sheets.Count
Sheets.Add
ActiveSheet.Name = “SheetList”
Sheets(”SheetList”).Move after:=Sheets(Sheetnames + 1)
For i = 1 To Sheetnames
Range(”A” & i) = Sheets(i).Name
Next i
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
Earlier in this thread I mentioned that I had found some dead code and that I thought that there might be more. Well I just used a project analyzer tool that I have and it showed that there were just 5 dead subs and they are:

FormulasInVisible and so it and the modFormulasInVisible module can both be removed
Similarly FormulasVisibleYes and modFormulasVisibleYes module can both be removed
And GetFormula and modGetFormula
And VisibleYes and the modVisibleYes module
You have a second VisibleYes sub in modVisible and it can be removed but keep modVisible

From the above it looks to me that you didn't realize that more than one sub or function can be put in the same module and I thought I'd mention that you can and that's what is usually done.
0
Martin LissOlder than dirtCommented:
Martin,
I did a compile and came across an error "Variable not defined" at
ActiveSheet.Name = "SheetList"
Should I change that the "Sheetnames"?
If what you are trying to do is to add the missing sheet names into the "SheetNames" sheet it won't work because they won't be in the right order. If that's not your objective please explain and I'll try to help.
0
Frank FreeseAuthor Commented:
On the "dead subs" the modGetFormula" is used in place of Excels Show Formulas, so I do use that a lot. I found that routine on the Internet.
The other dead subs I can delete.
I am currently updating my SheetNames worksheet and everything so far is OK. I only ran the compile to see if there were any errors. I was not expecting any but did get the one I described above. I've not changed any code. I am not sure of the statement
ActiveSheet.Name = "SheetList" that is referenced on line five for the above ccode. Here lies the question then.
0
Martin LissOlder than dirtCommented:
I copied the code you posted into your workbook and strangely all the quotes were changed to question marks. After I manually changed them to quotes I did a compile and got no errors and when I ran the sub the new SheetList sheet was created and filled in. Here's the code. It is strange about the quotes though because if you look at what you posted the quotes in the code are angled while mine are straight up and down.

Public Sub ListWorkSheetNames()
Dim Sheetnames
Sheetnames = Sheets.Count
Sheets.Add
ActiveSheet.Name = "SheetList"
Sheets("SheetList").Move after:=Sheets(Sheetnames + 1)
For i = 1 To Sheetnames
Range("A" & i) = Sheets(i).Name
Next i
End Sub

Open in new window

0
Frank FreeseAuthor Commented:
It gets even funnier. I copied and pasted your code. Now I'm getting a different compile error at:
For i = 1 To Sheetnames
where the error says that "i" is variable not defined

Public Sub ListWorkSheetNames()
Dim Sheetnames
Sheetnames = Sheets.Count
Sheets.Add
ActiveSheet.Name = "SheetList"
Sheets("SheetList").Move after:=Sheets(Sheetnames + 1)
For i = 1 To Sheetnames
Range("A" & i) = Sheets(i).Name
Next i
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
It's actually a good thing:) In the module you apparently have Option Explicit which requires that all variables be explicitly defined and that's good for several reasons including that it will catch spelling mistakes like these


MyVariable = MyVarible + 1
Sheetnmes = Sheets.Count
0
Martin LissOlder than dirtCommented:
BTW

Public Sub ListWorksheets()
Dim intSheetCount As Integer
Dim i As Long

intSheetCount = Sheets.Count
Sheets.Add
ActiveSheet.Name = "SheetList"
Sheets("SheetList").Move after:=Sheets(intSheetCount + 1)
    For i = 1 To intSheetCount
    Range("A" & i) = Sheets(i).Name
Next i

End Sub

Open in new window

0
Frank FreeseAuthor Commented:
I had forgotten to always include Option Explicit and now I do.
I ask some of my questions to affirm myself. I'd rather do that then introduce unnecessary problems.
No problems with Compile
Thanks again - this is so awesome still.
0
Martin LissOlder than dirtCommented:
What did you think of my last movie?
0
Frank FreeseAuthor Commented:
I really thought that was a great tool. It brought a different dimension to problem solving and EE needs to consider allowing this. I plan to look at it in more detail soon.
Thanks
0
Martin LissOlder than dirtCommented:
I suspect that some types of videos are allowed, just not .MOV files.
0
Frank FreeseAuthor Commented:
Question:
I've added another combo box and I have just one problem. I'm off by three worksheets (short) from where I need to be when I select an item (GoTo Selection) from the new combo box and do not know what other change(s) I still need to make? I did have to add a very lines to some of the sections.
I've also removed all the dead modules.
0
Martin LissOlder than dirtCommented:
Attach your new workbook and I'll see if I can find the problem.
0
Frank FreeseAuthor Commented:
The Date & Time combo box is new. I only labeled two items Test and Test2
Function-and-Formulas-for-Excel.xlsm
0
Frank FreeseAuthor Commented:
Please see if there is any text in A2 (Formulas) or A4 (Date and Time Functions)?
I put them there and save the workbook. When to workbook is reopened those text in A2 and A4 are gone. That is weird
0
Martin LissOlder than dirtCommented:
You have two more grey sheet names then you do grey menu items. One ends at row 89 and the other at row 91.These should match up.
0
Martin LissOlder than dirtCommented:
Please see if there is any text in A2 (Formulas) or A4 (Date and Time Functions)?
I put them there and save the workbook. When to workbook is reopened those text in A2 and A4 are gone. That is weird
In another thread of ours I posted the solution to that problem.
0
Frank FreeseAuthor Commented:
OK...I understand the matching part and need to see where I got off.
In regards to the second part, is the answer in this thread?
0
Martin LissOlder than dirtCommented:
No. See post ID 39468881 in this other thread.
0
Frank FreeseAuthor Commented:
thanks and have yourself a great weekend! you'ree a good man Martin
0
Martin LissOlder than dirtCommented:
You too my friend and thanks.
0
Martin LissOlder than dirtCommented:
I had another idea:)
Click me.
0
Frank FreeseAuthor Commented:
Let's open another question adding search functionality. I'll down load the latest version since I've done a lot of work to it. OK?
0
Martin LissOlder than dirtCommented:
Sure.
0
Frank FreeseAuthor Commented:
ON it's way
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.