Print a range when an event happens
 

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

Print a range when an event happens
 

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?

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"kefee85" wrote:

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

Print a range when an event happens
 

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)



On Mon, 16 Feb 2009 13:26:01 -0800, kefee85
wrote:

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

Print a range when an event happens
 

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

On Mon, 16 Feb 2009 13:26:01 -0800, kefee85
wrote:

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

Print a range when an event happens
 

On Mon, 16 Feb 2009 16:31:54 -0600, Chip Pearson
wrote:

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)



On Mon, 16 Feb 2009 16:31:54 -0600, Chip Pearson
wrote:

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)



On Mon, 16 Feb 2009 13:26:01 -0800, kefee85
wrote:

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