Issue Hiding Sheets with Command button

[oakley 0226]

I am having an issue with a command I am using,
I have a bunch of command buttons on my main sheet to unhide one single specified sheet each however they don't always work. The goal of this button is to simply unhide the tab the sheet it referring to, but not go to that sheet yet...
Some times some of them will work in 1 click, sometimes it will finally work after 3 or 4 clicks, and sometimes not at all. It seems random when they work and when they dont.
They will almost always work if I click it once and then push the space bar.

Is there a problem with my formula? or is there a way to tell automatically execute a space bar push afterwards? or to get it to always execute the command? any ideas? (side note, I tried changing to "True" to "xlSheetVisible") which didn't seem to help.

Private Sub CommandButton3_Click()
ActiveWorkbook.Worksheets("Sheet 30").Visible = True
End Sub


note, I also have the below command buttons for unhiding and going to the indicated worksheet, and these buttons always work fine.

Private Sub CommandButton4_Click()
ActiveWorkbook.Worksheets("Sheet 30").Visible = True
ActiveWorkbook.Worksheets("Sheet 30").Select
End Sub


Thanks!

 

[Micron]

All I can think of is that you have other code that is interfering at some point, like maybe you left screen updating disabled so the sheet is unhidden but doesn't show. Or similar idea but EnableEvents was left set to False so code won't run. Or it is really being made visible but you're not noticing.

I have never heard of having to press space bar to get code to work properly and I would not entertain the idea of making that happen to fix the problem. The following edit will confirm that your code is running when you click. Then hit F8 to make it continue when the code window opens.

Private Sub CommandButton3_Click()
ActiveWorkbook.Worksheets("Sheet 30").Visible = True
Stop
End Sub

 

[oakley 0226]

All I can think of is that you have other code that is interfering at some point, like maybe you left screen updating disabled so the sheet is unhidden but doesn't show. Or similar idea but EnableEvents was left set to False so code won't run. Or it is really being made visible but you're not noticing.

I have never heard of having to press space bar to get code to work properly and I would not entertain the idea of making that happen to fix the problem. The following edit will confirm that your code is running when you click. Then hit F8 to make it continue when the code window opens.

Private Sub CommandButton3_Click()
ActiveWorkbook.Worksheets("Sheet 30").Visible = True
Stop
End Sub

Thank you for your response, I have tried adding "Stop" like you showed, then when i click the button it goes to the code in debugger, highlighting the stop. if i push F8 is moves the yellow to another the next line.

 

[Micron]

So you at least know that it is running.

 

[oakley 0226]

Since my second code for unhiding and going to the worksheet always works I decided to try the below, essentially to go to the worksheet, then go back to the sheet I was on; I added screen updating so you don't see it flashing between screens. seems to be working, at least better than before.

Now I am wondering if i can add a test first to test if the wkst is hidden and if so execute the command, but if it is already unhidden I want it to do nothing. DO you know how to do this?

Private Sub CommandButton3_Click()
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets("Sheet 30").Visible = True
ActiveWorkbook.Worksheets("Sheet 30").Select
ActiveWorkbook.Worksheets("Sheet 1").Select
Application.ScreenUpdating = True
End Sub

 

[Jaafar Tribak]

Try adding a DoEvents function call prior to unhiding the worksheet and see if that fixes the problem:

Private Sub CommandButton3_Click()
DoEvents
ActiveWorkbook.Worksheets("Sheet 30").Visible = True
End Sub

Also, I would try setting the commandbutton TakeFocusOnClick Property to FALSE . That may also help.

Private Sub CommandButton3_Click()
CommandButton3.TakeFocusOnClick = False
ActiveWorkbook.Worksheets("Sheet 30").Visible = True
End Sub

BTW, better not to use "ActiveWorkbook", just use Worksheets("Sheet 30").Visible = True

EDIT:
Yet, another possible workaround :

In the sheet code module:

Private Sub CommandButton1_Click()
    Application.OnTime Now, Me.CodeName & ".UnHideNow"
End Sub

Private Sub UnHideNow()
    Worksheets("Sheet 30").Visible = True
End Sub

 

[oakley 0226]

Thank you for your suggestions, i am going to test them today.

I had read some other things saying doevents can do more harm than good, so i had been avoiding it, but if you have used it with no issue before i can try it.

For this workbook, users may have more than one open at the same time (so both would have sheets would have same names and what not), or have other excel workbooks opened; instead of "Activeworkbook" should I use

"ThisWorkbook.Worksheets("Sheet 30").Visible = True"

or still just use "Worksheets"

I also use "ActiveSheet" like in the below to make a copy of the sheet I am on and place it immediately after. I don't specify the name of the worksheet because I want them to be able to make multiple copies and each time the sheet name changes... Is there a better way? it works fine, but not i'm wondering about using "ActiveSheet"

Private Sub CommandButton2_Click()
ActiveSheet.Copy after:=ActiveSheet
End Sub

Thank you!

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Issue with command button unhiding sheets
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.