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