Duplicate functionalty

Folks,
I would like to duplicate functionality in a spreadsheet, attached. Combo box labeled cboTextFunction works perfectly!

I have been trying to duplicate the same functionality for cboFormulas. I placed a stop in the click event for this combo box and I never get to the code?
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:
If listindex is 15 for example, line 5 looks for an object named "Home25". Lines 6 and 7 work similarly, so you need to have your command buttons named like they are for the text functions. If you need further clarification at all please let me know.


Public Sub FormulasInVisible(combo As Object)
    
    Application.Goto Range("C" & combo.ListIndex + 10)
    ActiveWindow.ScrollRow = combo.ListIndex + 10
    ActiveSheet.OLEObjects("Home" & combo.ListIndex + 10).Visible = True
    ActiveSheet.OLEObjects("Reselect" & combo.ListIndex + 10).Visible = True
    ActiveSheet.OLEObjects("Goto" & combo.ListIndex + 10).Enabled = True
    combo.ListIndex = -1
    combo.Text = "Choose a formula"

End Sub

Open in new window

0
Frank FreeseAuthor Commented:
The code you suggested is the exact code that I am using. What escapes me is that in my sub-routine for my combo box
Private Sub cboFormulas_Click()
    FormulasInVisible cboFormulas
End Sub

Open in new window


is not executed?
0
Martin LissOlder than dirtCommented:
I know that the code you have is what you are using because all I did was copy it and paste it here so that I could try to explain what is going on.

The problem is that as I said, when the Listindex is 15 (in other words when the user clicks the  the 16th entry in the combobox) the code looks for command buttons named Home25, Reselect25 and GoTo25, which as far as I know don't exist, so you need to rename the ones that you select for "Formulas". Please let me know if that's clear.

Do you understand the code now?
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
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Frank FreeseAuthor Commented:
I found my problem, but please affirm. I changed

cboFormulas.Text="Production per hour"

to

cboFormulas.Text="Choose a formula"

But I do not understand why?
0
Martin LissOlder than dirtCommented:
No, that's not the right thing to do. See my previous post.
0
Frank FreeseAuthor Commented:
OK now
0
Frank FreeseAuthor Commented:
Great job! Many thanks
0
Martin LissOlder than dirtCommented:
Again you're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
Frank FreeseAuthor Commented:
Marty,
One things still escapes me. When I put a break at the beginning of this sub procedure the sub procedure never ran?

Private Sub cboFormulas_Click()
    FormulasInVisible cboFormulas
End Sub

When I changed the property Value = Choose a formula
that the sub-routine ran and everything worked fine?
0
Martin LissOlder than dirtCommented:
Please attach your workbook.
0
Frank FreeseAuthor Commented:
Here you go.
In this spreadsheet the sub cboFormals is not being executed. I placed a break in the sub routine and it never made it there. When I changed the "Value=Choose a formula" under properties the sub procedure than began to work. Note: I have only 1 item in this spreadsheet that works with cboFormulas and that is the Production Per Hour.
In my master file I'm changing everything now that the cboFormulas sub routine is working.
I understood your code but when I could not get the sub routine to work I simply compared everything that was done under cboTextFunctions and made the changes to cboFormulas.
That why I'm curious about why the sub routine did not work until I made those changes?
Revised-Function-and-Formulas-fo.xlsm
0
Martin LissOlder than dirtCommented:
Maybe I don't understand the problem because if you put a breakpoint on the FormulasInVisible cboFormulas line and then select 'Production per hour' from the dropdown list everything works no matter if the combobox text used to say "Choose a formula" or not. The only purpose of Value = Choose a formula is to have the combobox say that when the program starts. BTW another way to do that would be to add line 16 below. I'm not suggesting that you need to do that, I just wanted to show you another way.


Private Sub Workbook_Open()
'new

'NOTE: THIS DOESN'T HIDE ALL OF THEM BECAUSE THE NAMES ARE NOT ALL OF THE FORM "HomeNN"
'      WHERE NN IS A NUMBER FROM 1 TO 95. IF YOU CHANGE THEM TO FIT THAT PATTERN THEN ALL
'      WILL BE INVISIBLE AT THE START
Dim intIndex As Integer

With Worksheets("Menu")
    On Error Resume Next
    For intIndex = 1 To 95
        .OLEObjects("Home" & intIndex).Visible = False
        .OLEObjects("Reselect" & intIndex).Visible = False
        .OLEObjects("GoTo" & intIndex).Enabled = False
    Next
    .cboFormulas.Text = "Choose a formula"
End With
End Sub

Open in new window

0
Frank FreeseAuthor Commented:
Thanks for the new code.
Thanks for the education. It's been very rewarding
0
Martin LissOlder than dirtCommented:
You're welcome. Is there anything about the code that I've supplied you so far that you don't understand?
0
Frank FreeseAuthor Commented:
It's been several years since I've coded (Access) and just too many senior moments. The code is clear to me now. What do you think of what I'm doing? My youngest son thinks he knows Excel.
0
Martin LissOlder than dirtCommented:
How old is he?

You have a lot going on and while I understand the functioning of your 'Reselect' buttons, you could do without them and put code in the click of the combobox click event that resets the command buttons. It would be less code to maintain. To bad you don't have VB6 because with it's "control array" concept you can have a "family" of controls that share the same code which would reduce your code tremendously.
0
Frank FreeseAuthor Commented:
My son is 25. He works for the Memphis NBA Grizzlies and is their statistican.
I know there's a lot going on. I can tell by how long it takes to load.
But you brought up something interesting. I have VB but I'm not sure what version I have. I know I cannot upgrade to Windows 8 because the version of VB is not compatible. I have the latest VB before Windows 8. If I have VB6 can you please walk me through what I need, regardless of the numbers of questions?
I know here at work I cannot install VB on my desktop though.
0
Martin LissOlder than dirtCommented:
If you start your Visual Basic and then click Help|About Microsoft Visual Basic you'll see something like this.
VB6Note that while you can create and manipulate Excel spreadsheets using VB6, you can't use control arrays in those spreadsheets and in order to do that you would need to write a VB6 application that would show a form or forms containing your comboboxes, command buttons, etc. It's also important to note that unless your users have VB6 installed that you would need to create an installation package if you want them to use the Vb6 app. So my advice is stick with what you have.
0
Frank FreeseAuthor Commented:
I have VB Studio Professional
VB Studio
0
Martin LissOlder than dirtCommented:
That is a version of VB.Net which (unfortunately for me) replaced VB6 10(?) years ago, and while it is no doubt a better product it doesn't support control arrays.
0
Martin LissOlder than dirtCommented:
What do you think of my idea in post ID: 39461472?
0
Frank FreeseAuthor Commented:
I'm open to it
How's the best way to purse this?
0
Martin LissOlder than dirtCommented:
I'd prefer a new question but if you like we can discuss it here.
0
Frank FreeseAuthor Commented:
Agreed - new question. But first take a look at what I was trying to achieve. I've completed the cboFormals section and made some changes:
1. All Goto command boxes are disabled.
2. Once a topic has been choosen by way of the combo box then the combo box is disabled and the Reslect and =====> box is visible. The selected GoTo command box is enabled.
3. If the user elects not to choose that topic, Reselect then enables the combo box, hides the Reselect and ======> box.
4. If the user clicks on the GoTo then the combo box is enabled and the GoTo box is disabled and hides the Reselct box and =====> box

I know this seems like a lot of coding but I wanted to make sure that the user could only act on the topic they were interested in or Reselect another on. See attached file.

By the way, I found my copy of VB^ Professional Edition withmy CD key.
Function-and-Formulas-for-Excel-.xlsm
0
Martin LissOlder than dirtCommented:
Here's what I'm suggesting,

1. All Goto command boxes are disabled.
2. Once a topic has been choosen by way of the combo box then then the combo box is disabled and the =====> box is visible. The selected GoTo command box is enabled.
3. If the user elects not to choose that topic, he selects a new topic from any of the 3 comboboxes which hides ======> box.
4. If the user clicks on the GoTo then the combo box is enabled and the GoTo box is disabled and hides the  =====> box.

Done that way I also don't see any need to disable any of the 3 comboboxes. What reason is there for doing that since a click in a combobox resets everything from the last click?
0
Frank FreeseAuthor Commented:
On point 1, 2 and 4 we're on the same page.
Point 3 - If a combo box has been disabled in point 2 how does it become enabled again?
That's what the Reselect did
0
Martin LissOlder than dirtCommented:
I propose not to disable it.
0
Frank FreeseAuthor Commented:
OK
Let me open another question to enable and disable a combo box - you've earned more points than I can give in all fairness to you
0
Martin LissOlder than dirtCommented:
Let me know when you create the question.
0
Frank FreeseAuthor Commented:
I did last night but someone got there first
Look for another one this morning - it is only fair.
0
Frank FreeseAuthor Commented:
I'm about to open a question - are you there?
0
Martin LissOlder than dirtCommented:
I wasn't, but I am now, although not for long. Just go ahead and create the question and if someone else get's to it first then so be it.
0
Martin LissOlder than dirtCommented:
Okay tell you what. I've attached a solution that has the Reselect buttons removed. It's based on a previous workbook so if you've made recent changes then they won't be included. In any case the new code is marked with 'new and you should be able to copy them to your current workbook. Some things to note:
All "Reselect" command buttons were removed
I found that "Home91" to "Home95" were on the wrong lines and I moved them up to where they belong but I didn't bother perfectly left aligning them.
I put a new sub ResetControls in modVisible but it could be in any module. You should call ResetControls from all 3 comboboxes.

If you still want to create a new question for this then post your most current workbook in that question and I'll update it. If not then no problem just enjoy the new code:)
No-Reselect.xlsm
0
Martin LissOlder than dirtCommented:
If you haven't already, please read my last post.

I made a movie showing another idea to make your life easier. You'll need to be able to run QuickTime movies to view it. There's also audio and you may have to turn your sound up to hear it. You'll need to download it from here because EE won't allow me to upload a file with a .MOV extension.
0
Frank FreeseAuthor Commented:
Your suggestion on the video is the way to go. I'll open a new question Additional Functionality and attach the latest workbook. Since all changes will be on the Menu tab that OK.
The combo boxes shown are three of the fourteen I'll use. I haven't got to the others (the command box visible just below the combo boxes)
Look for a new question shortly
Thanks
Frank
0
Martin LissOlder than dirtCommented:
So I assume you're also OK with the no reselect button concept. I would also change all the "===>" command buttons to labels since there is no code associated with those buttons.
0
Frank FreeseAuthor Commented:
Agree to all suggestions
New question posted
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.