PDA

View Full Version : Warning for missing information



maksinx
02-19-2007, 01:41 PM
Dear All,
I have a shipping tracker and on column d we write customer names i want to set a warning if i write a customer EXS02 or EXS03 at any row under column d, the order number row under column e can not be blank,

i.e if i write in d5 exs05 once i press enter the warning should say something like please enter order number in e5,
same goes for exs03

sometimes i forget to enter order number on column e and it makes my calculations wrong

column d=customer name
column e= order number

any help is highly appreciated.

thanks in advance

lucas
02-19-2007, 02:00 PM
http://vbaexpress.com/kb/getarticle.php?kb_id=587

Bob Phillips
02-19-2007, 02:32 PM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D:D" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Offset(0, 1).Value = "" Then
MsgBox "Order number cannot be blank"
.Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

maksinx
02-19-2007, 02:36 PM
hi lucas
it seems the sample you quoted is not as i required,
i want to set a warning only in case exs02 or exs03 written on a cell
but leaving another relative cell empty.

what do you think

Bob Phillips
02-19-2007, 02:46 PM
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D:D" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value = "EXS02" Or .Value = "EXS03" Then
If .Offset(0, 1).Value = "" Then
MsgBox "Order number cannot be blank"
.Value = ""
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

mdmackillop
02-19-2007, 04:18 PM
If you add the following line into XLD's code, it will deal with capitals.

With Target
.Value = UCase(.Value)

maksinx
02-24-2007, 06:34 AM
dear xld
when i write ometing on columnd it gives a warning says that compile
error variable not defined.

i tried to write either exs02 or someother word still gave me the same warning.

any comments more than welcome

mdmackillop
02-24-2007, 07:31 AM
I've inserted the missing . in XLD's code.
If .Value = "EXS02" Or .Value = "EXS03" Then

maksinx
02-24-2007, 09:18 AM
dear mdmackillop
i have added the (.) into the code which made the code working fine. thank you for your prompt help.
however i have tried to enter the code as you quoted below for the capitalisation but seems i couldnt have managed.can you tell me where exactly i should paste the code.

when i paste the code into afew different place, made below first line of the entire code yellow.

quote
Private Sub Worksheet_Change(ByVal Target As Range)
unquote.

thanks
murat

mdmackillop
02-24-2007, 09:20 AM
Insert the UCase line after the With Target line

maksinx
02-24-2007, 09:57 AM
thanks mate that work all fine at the moment,

can i ask another favour which is abit complex that the ones we discussed before.

i have a shipping tracker which consist of nearly 32 columns and 400 rows.
This tracker is updated by 2-3 people every day with new shipping information such as invoice numbers,supplier names,product details,estimated time of arrival etc.
and i have two shipping line tht i work with, lets name them as msc shipping line and maersk.
i need to monitor on a day basis how many maersk has bookings that agreed and how many of them used on averyday same goes for msc.

while i am monitoring day to day basis each shipments, lets say second week or third week of each month as i see the booking and shipment levels, i should split remaining balances into these shipping lines as i wish.

for instance 3rd week of the month i see msc has got 34 bookings and maersk has less as 23, the remaining 30 shipments i will allocate to maersk or vise versa.

i can use pivot tables to do that it would be a good idea if could it be done with vb codes such as if i click one button which calculates all the booked shipments and remaining shipments and monitor on another sheet.

if you think it is possible to do that i can give you further information.

your help is highly appreciated.

thanks for your time and help.

murat










Bu arada kafama baska birsey daha takildi aslinda bayagi teferratli bir is ama zamanla cok da onem kazaniyor bizim is icin.
Problem su ayni shipping tracker den bir rapor yardimiyla belirli tarihler arasinda hangi yukleyiciden, ve gemi acentasindan kac tane konteynar booking yapilmis,toplam kaci yuklenmis.Bunlari takip edecek bir gunluk rapor yapmak istiyorum tabii burada da senin yardimina ihtiyacim olucak.
Buradaki amacimiz diyelim hem maersk hemde msc ile calisiyorum ve her ikisiylede 50 ser adet aylik konteynar yukletmek istiyorum veya duruma gore bu oranlari ayarlamak istiyorum ve bunu gunluk update etmek istiyorum, yeri geldiginde konteynar sayilarinda degistirme de yapabiliriz 50, 50 yerine 40, 60
gibi mesela.Bunu pivot table ile yapabilirim diye dusunuyorum ama seninde yardiminla vb katarak bir tus yardimiyla yapabilirsek super olur.

mdmackillop
02-24-2007, 10:01 AM
If you can post a workbook with sample data, we'll have a look.

maksinx
02-24-2007, 11:51 AM
dear mdmackillop
i have attached a sample file and also created one pivot table,

i need to monitor when any supplier lets say bmw booked container quantity reaches to 40 for march it should give me a warning which would enable me to choose the other shipping line for instance msc.

as a company policy we would like to split the whole booking quantity into two different company by supplier,

for eaxmple we buy 100 container from bmw or malaysia and we would like to split by 80% to msc and 20% to maersk when any or the supplier or country booked container values reaches to limits system should give me a warning so then i can use other shipping line.

if you need further infomation please let me know.

mdmackillop
02-24-2007, 01:42 PM
This presents a message box when data is entered in the green cell, prior to completing the shipper column. The other columns used in the code are highlighted for info. More detail could be incorporated in the messagebox if required.

maksinx
03-03-2007, 04:38 AM
dear mdmackillop,
your code works fine.Thank you very much.Can i ask one more favor
Is there any chance to send the last cell to a specisic column,
another way of saying
for example i try to write exs02 on any cell on column d and gave me a warning says you need to enter order number on column j lat say, then instead of going with mouse or write arrows can we send the active cell into a specific cell..
i hope i explain the situation if not please let me know.

thanks in advance
have a nice weekend.

mdmackillop
03-03-2007, 06:55 AM
I don't see how your columns relate to your posted sample, but here's the code for Columns D & J

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 16 Then Warning Target.Row
If Target.Column = 4 Then
If Target.Value = "exs02" Then
MsgBox "you need to enter order number on column j"
Target.Offset(, 6).Activate
End If
End If

End Sub

maksinx
03-08-2007, 01:28 PM
mdmackillop
last code that you provided is an addition code to the existing one if so where i should write the code
if not do you want me replace to the old one.

olease advice

thanks