hiding Command Button

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
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?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,006
Office Version
  1. 365
  2. 2016
Hi There

You could use something like


Code:
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

Active Member
Joined
Jan 31, 2006
Messages
438
that helped. Realize now why it didn't work the 1st time...I wrote everything the same except xlHidden, I wrote False.
 

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
Also need some kind of statement that says if while commandbutton1 is hidden if data is pasted into worksheet, to unhide commandbutton.
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,006
Office Version
  1. 365
  2. 2016

ADVERTISEMENT

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

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

Hope this helps


Dave
 

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,006
Office Version
  1. 365
  2. 2016

ADVERTISEMENT

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

Active Member
Joined
Jan 31, 2006
Messages
438
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,006
Office Version
  1. 365
  2. 2016
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,006
Office Version
  1. 365
  2. 2016
Try this changing as required to fit your needs

Code:
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
 

Forum statistics

Threads
1,137,341
Messages
5,680,926
Members
419,945
Latest member
Carrie Sellers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top