Unhiding a command button

Folks,
I am using a combo box to move to a specific location I selected from the combo list. Once I get to my location I have a command button whose property is Not Visible. When I get to my location I would like to make the command button Visible (There's no code behind this command button). I would then click on another command button that takes me to the sheet I'm interested in and need to change the property of the Visible command button back to Not Visible


Private Sub cboTextFunctions_Click()
    Application.Goto Range("C" & cboTextFunctions.ListIndex + 60)
    cboTextFunctions.ListIndex = -1
    cboTextFunctions.Text = ""
End Sub

Open in new window

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:
I'm note sure this is what you are looking for.
Private Sub cboTextFunctions_Click()

Application.Goto Range("C" & cboTextFunctions.ListIndex + 60)
ActiveWindow.ScrollRow = cboTextFunctions.ListIndex + 60
cboTextFunctions.ListIndex = -1
cboTextFunctions.Text = "Choose a text function"
cboMyInvisibleCommandButton.Visible = True

End Sub

Private Sub cboMyInvisibleCommandButton.Visible()

cboMyInvisibleCommandButton.Visible = False

End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
If cboMyInvisibleCommandButton is on another sheet you need to do

Worksheets("SomOtherWorkSheetName").cboMyInvisibleCommandButton.Visible = True
0
Frank FreeseAuthor Commented:
Not exactly. If you remember I had all those Home command buttons. I can get delete all the code behind them and make them Not Visible. The caption for the command buttons would be =====> and when a particular function of formula is selected from any of the combo boxes I would like to go to the cell that was selected and then make the command box visible. That would function as an identifer to the user exactly where they were. When I click on the GoTo command button the worksheet for the command button that was visible would no longer be visible.
Does this help?
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Martin LissOlder than dirtCommented:
Does this work for you? In this workbook choose the 'Search' item from cboTextFunctions. The 4 code changes are all marked with 'new. BTW, what where you attempting to do with the following code in the several Home buttons?

Sheets("Menu").Activate
Sheets("Menu").Range("K2:M2").Select
Q-28225373.xlsm
0
Frank FreeseAuthor Commented:
The purpose for the code you referenced above was simply to return the user to the last combo box they used. But by locking the row and scrolling this would not be necessary. Hopefully I simply will hide all the Home command buttons and when a particular cell was referenced unhide a command button with a caption =====> just to help the user know where they were. I'll try you previous suggestion and let you know.
0
Frank FreeseAuthor Commented:
I ran your suggested code and it does what I am asking, however, it only can act on one command button named cmdMyInvisibleCommandButton. With probably 500 of these type of command buttons I am looking for an easier way so that when I get to my cell location I then can execute the VBA to unhide just that one command button.
I do not know if you can code a "cell" to take action on an object?
I maybe asking more than Excel can deliver.
Function-and-Formulas-for-Excel-.xlsm
0
Frank FreeseAuthor Commented:
Is it possible that once a cell has the focus that the cell itself can be programmed?
0
Frank FreeseAuthor Commented:
I've tried the Range object but could not get that to work
0
Martin LissOlder than dirtCommented:
I'm a little confused since the workbook you attached has the 'Home' buttons and I thought you were done with that. Did you try the the workbook I attached in post ID 39447618?

However if you want to implement the invisible command button idea you could do this. In module3 (or any other module) add this code

Public Sub Invisible(cmd As Object, cmdInvis As Object)
    Application.Goto Range("C" & cmd.ListIndex + 60)
    ActiveWindow.ScrollRow = cmd.ListIndex + 60
    cmd.ListIndex = -1
    cmd.Text = "Choose a text function"
    cmdInvis.Visible = True

End Sub

Open in new window


and then change this

Private Sub cboTextFunctions_Click()
    Application.Goto Range("C" & cboTextFunctions.ListIndex + 60)
    ActiveWindow.ScrollRow = cboTextFunctions.ListIndex + 60
    cboTextFunctions.ListIndex = -1
    cboTextFunctions.Text = "Choose a text function"
    cmdMyInvisibleCommandButton.Visible = True
End Sub

Open in new window


to this.
Private Sub cboTextFunctions_Click()

    Invisible cboTextFunctions, cmdMyInvisibleCommandButton

End Sub

Open in new window


And then put similar code to that last bit of code in every command button click event.

Note that line 6 in the first two bits of code set the control to be Visible but nowhere in your current code do you set the button to Visible = False.

And about this code

Sheets("Menu").Activate
Sheets("Menu").Range("K2:M2").Select

That doesn't "return the user to the last combo box they used" but rather to cells near that command button. To return to the command button you would do worksheets("Menu").cbotextfunctions.select
0
Frank FreeseAuthor Commented:
You're correct regarding this code:
Sheets("Menu").Activate
Sheets("Menu").Range("K2:M2").Select

Sorry for the confusion
I plan to use the old Home command buttons only as a point of reference for the user and but there will be no code behind them. You solved that with the ScrollRow. I haven't gotten around to change their properties from Visible to Not Visible and the caption to =====>

I do know that once I click on a Goto command button I can reset the Visible property to Not Visible on the old Home command button

Let me incorpoarte you suggestions and let you know. Thanks for hanging in there with me.
0
Frank FreeseAuthor Commented:
"Note that line 6 in the first two bits of code set the control to be Visible but nowhere in your current code do you set the button to Visible = False."

I do that in the Properties section setting Visbible = False by default.
0
Martin LissOlder than dirtCommented:
Okay but that means that once they are set to Visible = True, that without code that says Visible = False they can never be invisible again.
0
Frank FreeseAuthor Commented:
Here's how I managed that. When I click on the GOTO command button I refer back to the visible command button (old Home) and set the Visile property = False
0
Frank FreeseAuthor Commented:
I hope we're on the same page.
I've made the changes as recommended, however, I am not sure about this:

"And then put similar code to that last bit of code in every command button click event."

Which command button click event? Outside of the GoTO there should be none.

I also do not know how to name my old Home command buttons.
Finally, the attached file genertaes an error where I select the first item in the Text functions. I figure once the text function works I'll move on the the other combo functions.

Again, my objective if that once subject has been selected in a  catergory (I staying with Text for now), we scroll to that catergory making the command button that is not visibile (the old Home command button) now visible with the caption =====> and the cell where the GoTo has the focus.
Function-and-Formulas-for-Excel-.xlsm
0
Martin LissOlder than dirtCommented:
I'll get back to you in 2 or 3 hours.
0
Frank FreeseAuthor Commented:
Thanks - you've been very patient with me and I am grateful
0
Martin LissOlder than dirtCommented:
Okay there were several problems with your implementation of my code. First you should never give the same name to a Sub (or Function) that you give to a module, so when you named the new module 'Invisible' and you also gave the Sub it contained the same name, VBA got confused. Second, until you are more sure of your VBA coding you should go to the 'Debug' menu and select 'Compile VBAProgram'. That won't catch any logic errors and it sometimes misses some coding errors but it usually finds them all. In this case I did that and corrected the 'Invisible' sub in the attached workbook. I also changed the module's name to modInvisible. If this works for you please don't extend it since even with that reduced amount of code that the 'Invisible' sub let's you get away with you have a lot of buttons and I'd like to see if I can come up with a better way. So let me know if this wb does what you want.

BTW when I said "And then put similar code to that last bit of code in every command button click event" I misspoke and should have said "And then put similar code to that last bit of code in every combobox click event."
Q-28225373.xlsm
0
Frank FreeseAuthor Commented:
OK - closer. I understand what I did wrong on the module name.

Now in executing this code

 
Invisible cboTextFunctions, cmdMyInvisibleCommandButton

Open in new window

works great but only on a single command button cmdMyInvisibleCommandButton. How do we manage all other command buttons, since they all have to have different names? Can command buttons be grouped and action taken on a group and the command button in that group? For example, we have a group called cmdTextFunctionsGroup and with in that group all command buttons from A60:A88 are members of that group so when I select a specific topic it knows what command button to make visible and that command button only?

I've been concentrating on one area only - Text Functions - thinking we get one working then cookie cutter what was done to all other combo box, current and future.
The attached workbook is the master that I am using to incorporate and test changes.

I really appreciate this -
Frank
Function-and-Formulas-for-Excel-.xlsm
0
Martin LissOlder than dirtCommented:
I just came back from exercising and I'll work on that as soon as I take a shower:)
0
Frank FreeseAuthor Commented:
no problem we can look mat it tomorrow
0
Martin LissOlder than dirtCommented:
Unfortunately as far as I know (and I can find no web articles that say differently) there's no way in VBA to avoid duplicating code in a lot of places. That being said, see if this works for you. I added code for both 'Separate Text String of Numbers' and 'Convert Invalid Numbers'. I made a lot of changes including the following. All the code changes are marked with 'new

Changed the name of cboMyInvisibleCommandButton back to Home60
Added a routine that sets all the "Home" buttons to invisible when the wb is opened. Please see the note in that procedure (Workbook_Open)
I merged cells K2:M2 so that the SeparateNumbers_Click event in sheet53 works better. Note that the fact that you have objects with the same name on different sheets makes maintenance more difficult and potentially confusing to VBA.
I simplified the 'Invisible' sub
Q-28225373.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:
This looks real close - I'm opening another question on a different issue here, but one major problem has shown up.
I have a module name modGetFormula that I use to show each individual formula. You can look at any workheet to see what happening. See attached file

Function GetFormula(x As Range) As String
GetFormula = x.Formula
End Function

Open in new window



Rather than return the formula I now get #Name?

Things are really looking good. I hope you've been challenged some here. I know I've learned a lot
Function-and-Formulas-for-Excel-.xlsm
0
Frank FreeseAuthor Commented:
Something doe not make sense. The problem seems to have gone away so forget my last comment.
GREAT JOB on this phase of my project - more to come soon.
0
Frank FreeseAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for fh_freese's comment #a39452360

for the following reason:

Simply a fantastic job and much appreciated!
0
Martin LissOlder than dirtCommented:
I assume you meant to award the points to me so I've asked a moderator to open the question if that's the case.
0
Frank FreeseAuthor Commented:
Great job and many thanks - more to come
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
Frank FreeseAuthor Commented:
Yes...I've resubmitted and awarded the points to you. I hope there's no problem.
0
Martin LissOlder than dirtCommented:
All's well that ends well:)
0
Frank FreeseAuthor Commented:
I've posted another question - it seems I cannot get the other combo boxes to work like cboTextFunctions. Get a chance take a look at the new question
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.