2nd Form Control Button Appearing When 1st Form Control Button Clicked

[davidhall]

I have two buttons which are required for a user to press. Button 1 is on Sheet 1. After button 1 is pressed, the user ends up on sheet 2 and looks at sheet 2 for certain items. Regardless if the items are present, the user has to go back to sheet 1 to press button 2 to generate another set of data. I would like to have button 2 show up on sheet 2 after button 1 is pressed.

The entire sheet 2 must always start off clear, otherwise, I would just have the button in there from the start.

Any suggestions?

 

[Smitty]

Why not set button2 to visible in button 1's click event? You can use sheet 2's deactivate event to hide button 2, so that when the user leaves the sheet it's hidden again.

HTH,

 

[davidhall]

One of the first processes the user does is to copy an entire sheet of data from another workbook all together and paste it in sheet 2. Will that make a difference if the button is setup in that manner?

And if not, I'm really not familiar with how to regulate the visibility of the button in the VBA language.

 

[Smitty]

If the user goes to sheet 2, then to the other workbook to copy/paste, then it could certainly make a difference. If they do that before pressing button 1 then it won't, only after, when the button get's displayed.

Here's how you can control the button's visibility (provided it's not an ActiveX control):

ActiveSheet.Shapes("Button1").Visible = False

 

[davidhall]

Thanks

 

[davidhall]

Ok. After some trial and error, maybe I'm not understanding where I need to insert that code.

Do I need to go into button1's VBA code and insert that at the end?

Do I need to click on sheet2 and select view code at the bottom and insert anything there?

Do I need to initially have the button in sheet2 prior to all of this instead of having it start in sheet1?

 

[Smitty]

Let's see:

1) Button 1 - If you want it to control button 2's visibility, then yes, you need to do it in button 1's click event. The code to do so could probably go anywhere in the click event, since the user won't see it anyway.

2) Sheet 2 - If you want to hide button 2 when the sheet is deactivated, so that only button 1 can unhide it, then you'll need to go into Sheet 2's module and add that code to the sheet's Deactivate event.

3) If that's where you want it to be, then yes, you should probably initially place it on sheet 2.

 

[davidhall]

So, this is what I have put in my button1's VBA.

Sheets("Sheet1").Select

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("Sheet2").Select
ActiveSheet.Shapes("Button1").Visible = True

End Sub


I'm not sure if that's correct or not.

I don't know what kind of code to write for sheet 2's deactivate event or how that is suppose to look in the view code part of sheet 2.

Also, how do I make button 2 not appear in sheet 2 when the document is initially opened if I already have the button in there.<!-- / message --><!-- sig -->

 

[Smitty]

Well, that's a selection change event, which will fire as you move around the worksheet.

You're looking for someting like:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> Button1Click()<br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet2")<br>        .Activate<br>        .Shapes("Button2").Visible = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

That would go in a general module, and you can assign it to Button 1.

Then in Sheet2's module put:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Deactivate()<br>    Sheets("Sheet2").Shapes("Button2").Visible = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

So that will hide the button when the sheet is deactivated, and show it again when button 1 on sheet1 is clicked.

 

[davidhall]

Ok. For some reason whenever I click on other tabs at bottom a run-time error appears stating "The item with the specified name wasn't found."


Any when you mentioned Sheet2's module, I access that by right clicking on the tab at the bottom for Sheet2 and clicking on View Code right?