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:
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,451
Messages
5,572,193
Members
412,447
Latest member
immy
Top