hiding Command Button

[aka_krakur]

Is there a way to hide the command button after it's already done what it needs to do. For instance, CommandButton1 just runs a macro to format a Worksheet. After the worksheet is formatted, the button now hovers over some of the data. I know how to set the option so it doesn't print; but as far as on the screen, can I hide it by a simple code at the end of the macro?

 

[dave3009]

Hi There

You could use something like

Sheets("sheet name").CommandButton1.Visible = xlHidden

Where you would just change CommandButton to the name of the object you want to hide and sheet name to the sheet you want to hide it from, alternatively this could be written as ActiveSheet. If you wanted re show it at a later point you could use xlVisible in another macro.

Hope this helps


Dave

 

[aka_krakur]

that helped. Realize now why it didn't work the 1st time...I wrote everything the same except xlHidden, I wrote False.

 

[aka_krakur]

Also need some kind of statement that says if while commandbutton1 is hidden if data is pasted into worksheet, to unhide commandbutton.

 

[dave3009]

you could use worksheet_change like this in the Sheet code for the sheet in question.

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet1").CommandButton1.Visible = xlVisible
End Sub

Hope this helps


Dave

 

[aka_krakur]

I tried that already, and basically the button becomes invisible with the slightest change. If user just selects a cell, button appears.

I'm looking for more of an if statement maybe in this same place that says if cell A2 has changed or is not blank then to unhide commandbutton

I'm not very good at if statements in macros

 

[dave3009]

Try setting the xlVisible line in BeforeDoubleClick instead of Change and ask users to double click the sheet to run your macro?

I too am not too good with statements, I tried a few If statements that didn't work. Hopefully one of the wizards here might come up with a better suggestion.

Kind Regards

Dave

 

[aka_krakur]

Thanks dave for your help and ideas. I really do need an if statement though. So if anyone else knows of a way to unhide button based off worksheet recognizing that new data was pasted in or something of that nature, that'd be great.

 

[dave3009]

Have you tried using Range("A2").IsNumber in your If statement?

I'm playing with it now so if I find a solution I'll let you know


Dave

 

[dave3009]

Try this changing as required to fit your needs

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case IsNumeric(A2)
    Case True
        Sheets("Sheet1").CommandButton1.Visible = xlVisible
    Case False
        Sheets("Sheet1").CommandButton1.Visible = xlHidden
End Select
End Sub

I knew there was some method.


HTH




Dave