Run macro if cell has a specific value

[dsvleeuw]

Hi All,

i'm creating a macro to print a specific range of offset cells. in my list there are 2 outcomes. if column H = X i would like to activate macro Retz() if column H is not X i would like to activate Macro ret(). i have a macro that should run Ret() & Retz() depending on the value of column N (select_cell()). i also have a macro that should activate (select_cell()) if i scan a barcode in Column A. now the problem is that I can't get (
select_cell()) correct.
Sorry i'm really new to VBA, i hope someone can help me!





'this should print if select_cell() value is 3

Sub ret()
'
' ret Macro
'

'
ActiveCell.Offset(-1, 7).Range("A1:B1").Select
Selection.PrintOut Copies:=1, Collate:=True
ActiveCell.Offset(1, -7).Range("A1").Select
End Sub

'this should print if select_cell() value is 2


Sub retz()
'
' retz Macro
'

'
ActiveCell.Offset(-1, 10).Range("A1:B1").Select
Selection.PrintOut Copies:=1, Collate:=True
ActiveCell.Offset(1, -10).Range("A1").Select
End Sub


'this should activate Select_cell()

Private Sub worksheet_change(ByVal target As Range)
If Not Application.Intersect(Range("a1:a1000"), Range(target.Address)) Is Nothing Then
Call worksheet_change1
End If
End Sub


'this should check the value of (-1) column N

Sub select_cell()
set = Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(-1, 13)).Select
If Range.Value = "2" Then
Call retz
End If
If Range.Value = "3" Then
Call ret
End If
End Sub

[Paul_Hossler]

Not exactly sure where you're going, but see if this moves you farther along

In a standard module:



Option Explicit


'this should print if select_cell() value is 3
Sub ret()
MsgBox "ret -- " & ActiveCell.Offset(-1, 13).Address
' ActiveCell.Offset(-1, 7).Range("A1:B1").Select
' Selection.PrintOut Copies:=1, Collate:=True
' ActiveCell.Offset(1, -7).Range("A1").Select
End Sub
'this should print if select_cell() value is 2

Sub retz()
MsgBox "retz -- " & ActiveCell.Offset(-1, 13).Address
' ActiveCell.Offset(-1, 10).Range("A1:B1").Select
' Selection.PrintOut Copies:=1, Collate:=True
' ActiveCell.Offset(1, -10).Range("A1").Select
End Sub

'this should check the value of (-1) column N
Sub select_cell()
MsgBox "select_cell -- " & ActiveCell.Offset(-1, 13).Address
Select Case ActiveCell.Offset(-1, 13).Value
Case "2"
Call retz
Case "3"
Call ret
End Select
End Sub





In the worksheet's code module



Option Explicit

'this should activate Select_cell()
Private Sub worksheet_change(ByVal target As Range)
If Not Application.Intersect(Range("a1:a1000"), Range(target.Address)) Is Nothing Then
Call select_cell
End If
End Sub

[dsvleeuw]

Wauw! thank you so much, but do you maybe know how to active it automaticly

without the msgbox?

[Paul_Hossler]

Delete the 3 Msgbox lines, and uncomment the one you had

I used Msgbox's to trace and because I didn't want to actually print anything