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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pswanie - 25 Mar 2008 18:33 GMT

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)

Signature

Helpful advice on posting to newsgroups here...
http://www.cpearson.com/excel/newposte.htm

Reply to this Message

cht13er - 25 Mar 2008 20:17 GMT

> hey...
>
[quoted text clipped - 22 lines]
> --
> Helpful advice on posting to newsgroups here...http://www.cpearson.com/excel/newposte.htm

Try this:

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

etc.

HopeThatHelps

Chris

Reply to this Message

pswanie - 25 Mar 2008 20:34 GMT

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

> Try this:
>
[quoted text clipped - 3 lines]
> End If
> End If

Reply to this Message

Bob Phillips - 25 Mar 2008 21:03 GMT

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

Signature

---
HTH

Bob

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

> will i need to repeat this for every column?
> or is there a loop idea that i can do?
[quoted text clipped - 7 lines]
>> End If
>> End If

Reply to this Message

pswanie - 25 Mar 2008 21:29 GMT

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

Reply to this Message

Bob Phillips - 25 Mar 2008 23:35 GMT

That was a typo, it should have been

For ColNum = 2 To 20

Signature

---
HTH

Bob

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

>i get the
>
[quoted text clipped - 51 lines]
> ActiveCell.FormulaR1C1 = "=username()"
> Range("L2").Select

Reply to this Message

Rick Rothstein (MVP - VB) - 26 Mar 2008 09:36 GMT

>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

Reply to this Message