command button help

[nyyfan_nc]

M & E # QTY NAME FILLED NIS COMMENTS PRINT NIS
26-2911-3-0031 1 HOSE, CASE DRAIN PUMP ER10403



have spreadsheet setup like this.
i am trying to hide or unhide a command button in the print nis cell
by placing an "x" in the NIS cell

also when the command button is unhid i want to click it and open another
workbook print it and then close it
then gray out the command button

please help.

M & E # QTY NAME FILLED NIS COMMENTS PRINT NIS
26-2911-3-0031 1 HOSE, CASE DRAIN PUMP x ER10403 button


thanks

 

[OssieMac]

Whether the following will work is dependant on whether I have interpreted
your question correctly.

There are 2 types of Command buttons. Forms control and ActiveX control. The
code requires the ActiveX control. In xl2007 select the button from the
ActiveX group. In earlier versions select from the Control Toolbox toolbar.

Right click on the worksheet tab name and copy the code into the VBA editor.
You will see comments re name of command button and also on how to obtain the
code to open, print and close the workbook. I have included a line to disable
the command button.

If you have problems then post the code you finish up with including any
error messages etc.

Private Sub Worksheet_Change(ByVal Target As Range)

'Edit E2 to the range where you place X
If Target = Range("E2") Then
If UCase(Range("E2")) = "X" Then
'Edit CommandButton1 in following lines _
to the name of your command button
Me.CommandButton1.Visible = True
Me.CommandButton1.Enabled = True
Else
Me.CommandButton1.Visible = False
End If
End If

End Sub

'Edit CommandButton1 to name of your command button.
Private Sub CommandButton1_Click()

'Record a macro to open the required workbook, _
print the required worksheets and close the _
workbook.

'Then edit Macro1 in the following line to _
match your recorded macro name. (It might _
not required changing.)

Call Macro1
Me.CommandButton1.Enabled = False

End Sub

 

[nyyfan_nc]

Work great thanks

but do have another question
can you use a command button to
close a document and bypass the save or not to save question
and not save?

 

[OssieMac]

Private Sub CommandButton2_Click()
'Closes another open workbook without saving

Workbooks("Test.xls").Close SaveChanges:=False

End Sub

Private Sub CommandButton3_Click()
'Closes the workbook containing the _
macro code without saving

ThisWorkbook.Close SaveChanges:=False

End Sub