msg box to stop user if range is "" (tricky one)

[pswanie]

hey...

fyi...

at the moment i got 2 sheets that has a row (B23:T23) where the user needs
to enter closing stock. (a couple of colums are blank(future use)Sheet2
O:T). in the workbook i got a couple modules. one of them Clear the cells
that need to be cleard and then cary the closing stock over to the column for
the next days opening(B5:T5).

what i need is a code that i can put at the start of my existing code that
will check
if range b5 = something (product name/>" ") then if b23 = "" msgbox "unable
to clear".
if range c5 = something (product name/>" ") then if c23 = "" msgbox "unable
to clear".
if range d5 = somthing (product name/>" ") then if d23 = "" msgbox "unabale
to clear".
etc

in a nutshell the user should not be able to print and clear the workbook
if a column got a product name(B5:T5) with a blank closing(B23:T23).
(and told in a msg box all the products names with a blank closing)

 

[cht13er]

hey...

fyi...

at the moment i got 2 sheets that has a row (B23:T23) where the user needs
to enter closing stock.  (a couple of colums are blank(future use)Sheet2
O:T).  in the workbook i got a couple modules. one of them Clear the cells
that need to be cleard and then cary the closing stock over to the column for
the next days opening(B5:T5).

what i need is a code that i can put at the start of my existing code that
will check
if range b5 = something (product name/>" ") then if b23 = "" msgbox "unable
to clear".
if range c5 = something (product name/>" ") then if c23 = "" msgbox "unable
to clear".
if range d5 = somthing (product name/>" ") then if d23 = "" msgbox "unabale
to clear".
etc

in a nutshell the user should not be able  to print and clear the workbook
if a column got a product name(B5:T5) with a blank closing(B23:T23).
(and told in a msg box all the products names with a blank closing)

Try this:

If Cells(5,2) <> "" Then
If Cells(23,2) = "" Then
Call Msgbox "unable to clear"
End If
End If

etc.

HopeThatHelps

Chris

 

[pswanie]

will i need to repeat this for every column?
or is there a loop idea that i can do?
where would i tell it to first do sheet1 and then sheet2

 

[Bob Phillips]

Loop the columns

For ColNum To 2 To 20

If Cells(5,ColNum) <> "" Then
If Cells(23,ColNum) = "" Then
Call Msgbox "unable to clear"
End If
End If
Next ColNum

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[pswanie]

i get the

for colnum to 2 to 20 in red

and

Call Msgbox "unable to clear" in red aswell...


did not yet run the code (can only in the morn)

this will give a idea of where i got it pasted in. if any of the cells are
empty then exit the macro so the user can go and fix the closing total. if
they all are good. the macro enter the username and previos days date, print
and call upon another macro to clear the sheet



************************************************************

With Application
.EnableEvents = False
.ScreenUpdating = False
End With


ActiveWorkbook.Save


Sheets("inv 1st page").Select
For ColNum To 2 To 20

If Cells(5, ColNum) <> "" Then
If Cells(23, ColNum) = "" Then
Call Msgbox "All Closing totals not Enterd"
End If
End If
Next ColNum


Sheets("inv 2nd page").Select
For ColNum To 2 To 20

If Cells(5, ColNum) <> "" Then
If Cells(23, ColNum) = "" Then
Call Msgbox "unable to clear"
End If
End If
Next ColNum





Sheets("data capture").Select
Range("O8").Select
ActiveCell.FormulaR1C1 = "=username()"
Range("O11").Select
ActiveCell.FormulaR1C1 = "=now()-1"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("inv 1st page").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "=username()"
Range("L2").Select

 

[Bob Phillips]

That was a typo, it should have been

For ColNum = 2 To 20


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

i get the

Call Msgbox "unable to clear" in red aswell...

Also a typo. Use either this....

Call MsgBox("unable to clear")

or this...

MsgBox "unable to clear"

Rick