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]

Sounds like you may have an issue with where the Deactivate code went. WHen the error comes up next time, hit the Debug button and see where you go. Any errant code will be highlighted in yellow.

Yes, you do access the sheet module that way. You can also find it directly in the VBE Project Explorer.

 

[davidhall]

Two questions:

Button 1 already has VBA language in it.

Do I still need to include the first line Sub Button1()Click and the last line End Sub and just insert the With...End With information?

Sub Button1Click()
With Sheets("Sheet2")
.Activate
.Shapes("Button2").Visible = True
End With
End Sub


The debug error occurs and points to the second line beginning with the word Sheets below


Private Sub Worksheet_Deactivate()
Sheets("Sheet2").Shapes("Button2").Visible = False
End Sub

 

[Smitty]

Two questions:

Button 1 already has VBA language in it.

Do I still need to include the first line Sub Button1()Click and the last line End Sub and just insert the With...End With information?

No, get rid of Sub Button1Click()/End Sub and just keep the code in the middle. It should be sandwiched betwen Private Sub Button1_Click()/End Sub.

The debug error occurs and points to the second line beginning with the word Sheets below

Private Sub Worksheet_Deactivate()
Sheets("Sheet2").Shapes("Button2").Visible = False
End Sub

Is sheet 2 actually names "Sheet2"? The name in the code needs to match the sheet name exactly.

 

[davidhall]

No, get rid of Sub Button1Click()/End Sub and just keep the code in the middle. It should be sandwiched betwen Private Sub Button1_Click()/End Sub.


Ok. I have done the following.. The existing code looks like this:

Sub Button1()

Existing code
Existing code
Existing code

End Sub

I have placed the code you mentioned by the lines stating existing code so it now looks like this.

Sub Button1()

Existing code
Existing code
Existing code


Private Sub Button1_Click()
With Sheets("Sheet2")
.Activate
.Shapes("Button2").Visible = True
End With

End Sub

Whenever I did it this way and clicked on button1, a message popped up stating Compile error: Expected End Sub

So, I reconfigured the code to look like this.


Sub Button1()

Existing code
Existing code
Existing code

End Sub
There is a line here separating the code
Private Sub Button1_Click()
With Sheets("Sheet2")
.Activate
.Shapes("Button2").Visible = True
End With

End Sub

Now when I run the code this way, a new popup box comes up.

It says Run-time error'2147024809 (800070057)
The item with the specified name wasn't found.

When I click on the debug, it brings up the code that I insert in Sheet2..and the arrow on the left side points to the code I have in red below.

Private Sub Worksheet_Deactivate()
Sheets("Sheet2").Shapes("Button2").Visible = False
End Sub



The name of button2 on the actual button on the screen is "button2". Additionally, within VBA in the properties area, the (Name) row is titled "button1" for button1 and "button2" for button2. Directly below that in the projects area under the modules folder, the module names are "button1" and "button2".

 

[Smitty]

Check your PM

 

[davidhall]

Not to familiar with your PM acronym..project manager?

 

[Smitty]

PM = Private Message. There's a link at the top of the page where it says: Welcome, davidhall.

 

[davidhall]

I just sent you an email

 

[Smitty]

Resolved.

The issue was Forms controls vs. ActiveX.