if macro?

[zilch4ry]

hi all,

I need a macro which will only move onto the next step if either or all cells "H57, H43, H32, H22, H12" have any quantity entered apart from 0. I've been sat here for ages and can't seem to find a way of doing this (I'm really bad at using macros).

Thanks,

Tom

 

[MrKowz]

Maybe something like:

If Application.Sum(Range("H12").Value, Range("H22").Value, Range("H32").Value, Range("H43").Value, Range("H57").Value) > 0 Then
    'what you want it to do
End If

 

[reneuend]

I would use the Worksheet_change subroutine for that worksheet.
From VBA, Select the worksheet in question.
Select "Worksheet" from the first dropdown
Select "Change" from the second dropdown

You should see the "Worksheet_change" subroutine
Note: 'Be sure to define bNextStep outside this routine!

Private Sub Worksheet_Change(ByVal Target As Range)
 
    select case Target.Address
        case "$H$57": 
           if Target.value > 0 then bNextStep = True
        case "$H43":
           if Target.value > 0 then bNextStep = True
'add the rest of the cases......
        else
            bNextStep = False
     end select
 
End Sub

 

[zilch4ry]

Thank you both for the answers they have really helped!

Extra: is it possible to add a warning message to that code, so say if none of the cells in question have a value in the their cell higher than 1, then a warning message will appear saying "please enter a value into one of the cells to proceed"

 

[reneuend]

Yes. Just add it to the Else statement.

NOTE: After reading your warning message, If what I wrote is really what you wanted. With this Select statement, the value that the user changes will be evaluated (no other values are evaluated until the user changes that particular cell value). The Select routine evaluates one cell at a time as they are being changed.

So, if the user selects a cell NOT listed in the select statement, it will be ignored below.
If the user selects a cell listed below it will be evalulated but no other cells are evaluated until the user changes those cells.

Let me know if you wanted something different.

Private Sub Worksheet_Change(ByVal Target As Range)

select case Target.Address
case "$H$57":
if Target.value > 0 then bNextStep = True
case "$H43":
if Target.value > 0 then bNextStep = True
'add the rest of the cases......
else
bNextStep = False
msgbox "Please enter a value into one of the cells to proceed"
end select

End Sub

 

[zilch4ry]

Thanks