Hiding an ActiveX button when hiding columns

[crobaseball]

I am putting several buttons into my spread sheet and would prefer to use ActiveX controls rather than form controls. But I also need to hide the buttons from the end user (controls are to update data and will be used by the person who maintains the workbook). When I go to hide the columns, the ActiveX button doesn't hide with it. On the other hand, if I use a Form Control Button instead, it does indeed disappear when I hide the columns. Is there any way to get the ActiveX button to hide when hiding the columns?

Thanks!

 

[BadgerRoad]

How do the columns get hidden? One option is to set the Visible property to False when the column is hidden.

 

[crobaseball]

Can I have this be a part of the vba of the sheet itself? I can either have someone hide the columns manually, or I can create another button to do this. I assume the button will reappear when the columns are unhidden?

 

[BadgerRoad]

You would have to hide and unhide the buttons using vba. If the button you want to hide/unhide is named CommandButton1 then to hide the button use the following:
CommandButton1.Visible=False
To unhide the button use
CommandButton1.Visible=True
If you have a button to hide the column just add the first line above to that code and similarly, if you have a button to unhide the column add the second line.
The above method is pretty straightforward. I'm not sure if hiding/unhiding a column triggers a change event or not without testing, but if it does the code could be added there as well.

 

[crobaseball]

I know the code to hide or unhide the button, what i need is for it to do so when the columns are hidden, then to unhide when the columns are unhidden. I can certainly do this using vba/button when hiding . . . but there's no way to do it when unhiding. Or there would be a button visible to do so. I guess I could just have a macro which someone could run to do so and lock out the macro's, but this really isn't user friendly the way I'd like it to be

 

[BadgerRoad]

The following code, using Column H as the hidden column will hide/unhide the button

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Columns("H:H").EntireColumn.Hidden = True Then
    Me.CommandButton1.Visible = False
End If
If Columns("H:H").EntireColumn.Hidden = False Then
    Me.CommandButton1.Visible = True
End If
End Sub

 

[BadgerRoad]

By the way, the above code doesn't execute when the column is hidden/unhidden, but when you select a cell after hiding/unhiding or whenever any selection change occurs.

 

[crobaseball]

And I assume this goes into the sheet in question?

 

[crobaseball]

And geez, where are my manners . . . thank you!!!

 

[crobaseball]

I realized that wasn't clear: what I meant more specifically . . . The code you wrote, does that go into the vba of the actual sheet? As opposed to in a separate module?