Link to home
Start Free TrialLog in
Avatar of Frank Freese
Frank FreeseFlag for United States of America

asked on

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

Avatar of Martin Liss
Martin Liss
Flag of United States of America image

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

If cboMyInvisibleCommandButton is on another sheet you need to do

Worksheets("SomOtherWorkSheetName").cboMyInvisibleCommandButton.Visible = True
Avatar of Frank Freese

ASKER

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?
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
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.
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
Is it possible that once a cell has the focus that the cell itself can be programmed?
I've tried the Range object but could not get that to work
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
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.
"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.
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.
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
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
I'll get back to you in 2 or 3 hours.
Thanks - you've been very patient with me and I am grateful
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
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
I just came back from exercising and I'll work on that as soon as I take a shower:)
no problem we can look mat it tomorrow
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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.
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!
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.
Great job and many thanks - more to come
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
Yes...I've resubmitted and awarded the points to you. I hope there's no problem.
All's well that ends well:)
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