Print a range when an event happens

[kefee85]

I need to print cells c1-g1 when cell b1 changes value from "" to anything
else.

 

[Shane Devenshire]

Hi,


Here is some sample code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("B1"))
If Not isect Is Nothing Then
'Your code here
End If
End Sub

Record the print command you need and put the code in where marked above.
You did not say what happens if the value in B1 is "Z" and the user types
"B". The code I gave you would run the macro in that case also. Is that
what you want?

 

[Chip Pearson]

Right-click on the worksheet tab and choose View Code. In the code
module that appears, paste the following code:


Private Sub Worksheet_Change(ByVal Target As Range)
Static WasEmpty As Boolean
If StrComp(Target.Address, "$B$1", vbTextCompare) = 0 Then
If Target.Value <> vbNullString Then
If WasEmpty = True Then
Me.Range("C1:G1").PrintOut preview:=True
WasEmpty = False
End If
Else
WasEmpty = True
End If
End If
End Sub

This will print C1:G1 when B1 changes from something to nothing, but
not when B1 changes from something to something else. Leave
"preview:=True" while testing and then change to "preview:=False" when
the code hits the real world.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gord Dibben]

Assuming B1 will be a calculated value.

Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("B1")
If .Value <> "" Then
Me.Range("C1:G1").PrintPreview 'PrintOut
End If
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Chip Pearson]

This will print C1:G1 when B1 changes from something to nothing, but
not when B1 changes from something to something else.

The code is correct but the explanation is wrong. The code prints
C1:G1 when B1 changes from nothing to something. Sorry for any
confusion.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)