help! Want macro button to be visible/invisible

Smelco

New Member
Joined
Dec 2, 2005
Messages
4
Ok heres the deal.

I have a macro button at the top of the page.

I want this button to be visible ONLY when I click on a cell is within a specified range.

I.E. - If I select a cell in the range A10:A20, I want the button to appear at the top of the page (or right next to the active cell if possible!). If I click on a cell outside of A10:A20, I want the button to dissappear.

I know the start of what the macro should be but I cant get anything to work :(

"Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)...................."

help!!!! thanks a million :wink:
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Welcome to MrExcel Board!

How about:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    If Application.Intersect(Target, Range("A10:A20")) Is Nothing Then Exit Sub
    ActiveSheet.Shapes("Button 1").Visible = False             'forms button
    ActiveSheet.Shapes("CommandButton1").Visible = False       'control toolbox button
    Application.EnableEvents = True
End Sub

Hope that helps!
 

Smelco

New Member
Joined
Dec 2, 2005
Messages
4
thanks for your help but I cannot get it to work. Im not too great with macros yet so mayebe its me. But I made the buttons and pasted the code by opening the tab code (clicking "view code" by right clicking on the tab name). It doesnt do anything. I suck hahaha :rolleyes:

also just want to double clarify that it doesnt matter what is in range A1:A10. The button should just appear when I make the active cell anywhere in that range.

Thanks for your concern so far!
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
I had two lines flipped. :oops:

Try:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.Intersect(Target, Range("A10:A20")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    ActiveSheet.Shapes("Button 2").Visible = False             'forms button
    ActiveSheet.Shapes("CommandButton1").Visible = False       'control toolbox button
    Application.EnableEvents = True
End Sub

Remember, this code goes in the particular sheet's code module where the button's are, not in a standard module you create with Insert, Module, not in the ThisWorkbook module (though it could be modified to go in there, if you wanted every sheet in the workbook to behave this way.

HTH
 

Smelco

New Member
Joined
Dec 2, 2005
Messages
4

ADVERTISEMENT

Hey!!!

We're gettin there :)

Problem is:

When I click on a cell between a10:a20, the button actually dissappears instead of appears. I want the button to be visible when Im in those cells and invisible when im in any other cell

And also once it dissappears, It wont come back! The buttons get a one way ticket and never come back :p

thanks for your amazing help so far. im loving these boards. im trying to learn macros so this is great!
 

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
How about this?:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myAns
    Application.EnableEvents = False
    On Error Resume Next
    myAns = Application.Intersect(Target, Range("A10:A20")).Count
    ActiveSheet.Shapes("Button 2").Visible = Not Err.Number And 1       'forms button
    ActiveSheet.Shapes("CommandButton1").Visible = Not Err.Number And 1 'control toolbox button
    Application.EnableEvents = True
End Sub

Remember to include only one of these lines:
Code:
    ActiveSheet.Shapes("Button 2").Visible = Not Err.Number And 1       'forms button
    ActiveSheet.Shapes("CommandButton1").Visible = Not Err.Number And 1 'control toolbox button

Depending on which kind of button you're using, and change the names also. HTH
 

Smelco

New Member
Joined
Dec 2, 2005
Messages
4
Taz!!! You are the man! It works like a charm.

Forever thanks for sticking with me on this!

You da man
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,998
Messages
5,834,808
Members
430,323
Latest member
Regash

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