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?

 

[Dufus]

You could try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("A1").Address Then
        MsgBox "Your Code Here"
    End If
End Sub

 

[aka_krakur]

Okay, I got the Button to disappear when there's no data in Cell A1 by using this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case IsEmpty(A1)

    Case True
        Sheets("ShippedQty-SummarybyYr").CommandButton1.Visible = xlHidden
    Case False
        Sheets("ShippedQty-SummarybyYr").CommandButton1.Visible = xlVisible
End Select
End Sub

However, shouldn't that also show the button if there were something in cell A1?

the Select Case IsNumeric(A1) didn't work because if/when there is something in A1 it's usually text.

 

[dave3009]

Try HasValue instead.

Dave

 

[aka_krakur]

Not sure where I am to put HasValue. It's not liking that definition is I'm subbing it for IsEmpty.

 

[dave3009]

Good point, you may need to use Select Case twice in that case. one for IsEmpty, one for HasValue. It is at this point I start getting lost. I'll play some more for you and post any findings

Cheers


Dave

 

[aka_krakur]

Dufus, I'm not sure, but I tried this code and then clicked the button and it tried to continue the Macro regardless that cell A1 was empty without giving me the prompt of what I typed between the quotations.

do you think I did something wrong or didn't understand you completely?

Here's how I wrote the code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("A1").Address Then
        MsgBox "You must first copy/paste data from Viper Drill-Down"
    End If
End Sub

Looks like I'm supposed to tell it what Address is?
I'm not sure. Please help

 

[aka_krakur]

Dufus, I figured out what your code was doing. It would be if the user was actually physically changing something in A1. It's the opposite, I want it to work like if there is nothing in A1 (or null) and user presses commandbutton1 it would bring up this error message and end the macro

 

[dave3009]

Hi

I have come up with this so far, it doesn't work however I feel you might be able to tweak it to work or someone more suitable qualified might get it to work

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

Hopefully you can resolve this issue. I probably have the same knowledge of VBA as you hence my basic responses. I will continue to watch so as I can stick in my 2 pence in where its worth


Good Luck


Dave

 

[Dufus]

aka_krakur,

Sorry, I thought you were looking for a simple way to hide and unhide the button that hovered in your way. In my code you would trigger the hiding and unhiding any time A1 changed. Even restricting the possible entries in A1 using [Data][Validation] wouldn't really be necessary since you could just put a message "Change the cell contents to Hide or Unhide the Button" in the cell.

 

[aka_krakur]

Still needing some assistance with this. If anyone can help. Here's a brief summary:
CommandButton1.
When Workbook is open CommandButton1 appears.
User is supposed to copy/paste data over existing data.
Then they press CommandButton1 to run a macros that formats the data automatically for them.
Am able to hide CommandButton1 after macros is ran no problem.
1. Problem is, I need to unhide it based off of if user copied/pasted data back again
2. Was able to write a code that if A1 is empty, if user presses CommandButton1 it will just send up a message box reminding them that they have to copy/paste data first. Then it ends the macro until they do so.

Please help if you can....thanks