Hide Command button on close

[Guest]

Hi All,
I have a worksheet that has a command button. When the command button is
clicked it emails the form. I need to hide the command button and the row
where it is located on the form. There are several things that happen when
the command button is selected. Everything works until I get to the send
email part so I have listed the part where I seem to be stuck.

I have tried the following

ActiveSheet.Unprotect
Shapes("CommandButton2").Visible = False
Rows("44:47").Select
Selection.EntireRow.Hidden = False
Rows("34:35").Select
Selection.EntireRow.Hidden = True
Range("B2:C2").Select
ActiveSheet.Protect

ActiveWorkbook.SendMail "***", ActiveSheet.Range("X5").Text

ActiveWorkbook.Close Savechanges:=False

When the command button is clicked it unhides rows 44:47, Selects cell B2:C2,
Protects the sheet, sends the email, closes without saving changes. But it
will not hide the command button or the rows 34:35.

What am I missing?

Any help is appreciated.

 

[IanKR]

Hi All,

I have a worksheet that has a command button. When the command
button is clicked it emails the form. I need to hide the command
button and the row where it is located on the form. There are
several things that happen when the command button is selected.
Everything works until I get to the send email part so I have listed
the part where I seem to be stuck.

I have tried the following

ActiveSheet.Unprotect
Shapes("CommandButton2").Visible = False
Rows("44:47").Select
Selection.EntireRow.Hidden = False
Rows("34:35").Select
Selection.EntireRow.Hidden = True
Range("B2:C2").Select
ActiveSheet.Protect

ActiveWorkbook.SendMail "***", ActiveSheet.Range("X5").Text

ActiveWorkbook.Close Savechanges:=False

When the command button is clicked it unhides rows 44:47, Selects
cell B2:C2, Protects the sheet, sends the email, closes without
saving changes. But it will not hide the command button or the rows
34:35.

What am I missing?

Any help is appreciated.

Have you tried your code without making unnecessary selections?

ActiveSheet.Unprotect
Shapes("CommandButton2").Visible = False
Rows("44:47").EntireRow.Hidden = False
Rows("34:35").EntireRow.Hidden = True
Range("B2:C2").Select '<============== assume you want to end up with these
cells selected.
ActiveSheet.Protect

ActiveWorkbook.SendMail "***", ActiveSheet.Range("X5").Text
ActiveWorkbook.Close Savechanges:=False

I may be wrong, but is a command button really a member of the Shapes
collection of the active sheet? Was it from the Control Toolbox toolbar or
the Forms toolbar?