Solved: Message box for offset column

[georgedaws]

I have code that provides a message box if the user tries to "move on" to the next column of their without putting any data in. This in turn sends the user back to that column and row. The trouble with it is a couple of things; The messagebox comes out long and stretched, I imagine its because of the VBA code.
[VBA]If Not Intersect(Target, Range("D3:J10000")) Is Nothing Then
If Target.Offset(0, -1).Value = "" Then
MsgBox "Some information has been missed.Please enter information in previous column labelled " & Cells(2, Target.Column - 1).Value, vbOKOnly
Target.Offset(0, -1).Select
End If
End If
End Sub
[/VBA]


At present the code is written for columns "D" to "J". this offset does not take into account column "J" so data can be missed.

To make it more complicated the workbook has to be accessed by three other departments. Their columns are "K to "N" and "O to "R" and "S" to "V" respectively.

Can I get this code to work independantly of each set of columns and ensuring that the end column of each set is filled in.

This has had me stumped for hours because all I have to work with is the offset code and I haven't the knowledge to change it.

Can I change my messagebox to a warning box or something, I would still just need the OK button because the user doesn't need a choice.

If anyone can help put me out of my misery with this, I appreciate it.


Thanks.

[mancubus]

for a shorter MsgBox you may try:

[VBA]
MsgBox "Some information has been missed." & vbNewLine & _
"Please enter information in previous column labelled" & vbNewLine & _
Cells(2, Target.Column - 1).Value, vbOKOnly
[/VBA]

[georgedaws]

Hi mancubus,

that works perfectly,

just got to get round the other bit now.

At least I can see waht you have done. I've learnt something else!

Thanks

[mancubus]

hi.

"vbNewLine" is easy to remember. instead, you may also use vbCr (carriege return), vbLf (line feed), vbCrLf and Chr(10) for msgbox.


i'm assuming:
- cols D, E, F are common to all depts.
- and cols G to J / K to N / O to R / S to V are dept specific.
- and you want to force the user populate the previous cell before populating the target cell
- and if the target cell is in the the last col of 4-col sets (ie cols J, N, R, V), this cell must be populated by responsible dept. (keeping in mind that when editing an empty cell, pressing the space bar and then enter key makes that cell changed.)
- each dept is responsible for own 4-col sets. (so if i am responsible for cols O-R, i don’t care whether cols J, K or S, etc are empty or not.
- all cols are visible to all.

do you think i get the point correctly?

[georgedaws]

Hi mancubus,

I never knew about vbNewline and the others until now. Thank you for that. Learnt something which is the point!

With regards to the columns you are correct apart from my bad explanation from the start. All is correct except columns "D" to "J" are department specific. ("A" and "B" are auto triggered by first input of column "C". I have to hope and assume that the user will kick off with column "C" at least!)

The rest of it (fingers crossed!) is right on the money.

Do you have a plan in mind already?

Thanks for your help so far.

[mancubus]

you are wellcome.

conditional formatting with a formula to display the blank cells may help. you may apply different fill colors for different departments.

select the related range for a dept and then conditional format the range with, say, =ISBLANK(D3) formula.
use relative cell reference.

[georgedaws]

Thanks for your help mancubus. I would hope to be able to prevent folks from missing data with the help of vba and messageboxes, which I would hope would"hold the users hand" throughout the process of entering data. This would at least ensure no important information is missed or the original person having to be found to account for missing data.

If it cannot be done through VBA at least I can look at alternatives or abandon it.

So if anyone who is in the know thinks it can't please say. At least then I have taken solid advice from an expert.

Thanks

[mancubus]

you may play around with the following. (adopted from mikericson's code)


[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
'http://www.mrexcel.com/forum/showthread.php?t=313142

Dim blnkRng As Range
Dim msg1 As String, msg2 As String
Dim LR As Long, LC As Long

LR = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
LC = Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
msg1 = "Some information has been missed."
msg2 = "Please enter information in range:"

If Intersect(Target, Range("D3:V" & LR)) Is Nothing Then Exit Sub

On Error Resume Next

With Target
Select Case .Column
Case 4 To 10 'col D thru J
Set blnkRng = Range("D" & .Row & ":J" & .Row).SpecialCells(xlCellTypeBlanks)
MsgBox msg1 & vbNewLine & msg2 & vbNewLine & blnkRng.Address, vbOKOnly
Case 11 To 14 'col K thru N
Set blnkRng = Range("K" & .Row & ":N" & .Row).SpecialCells(xlCellTypeBlanks)
MsgBox msg1 & vbNewLine & msg2 & vbNewLine & blnkRng.Address, vbOKOnly
Case 15 To 18 'col O thru R
Set blnkRng = Range("O" & .Row & ":R" & .Row).SpecialCells(xlCellTypeBlanks)
MsgBox msg1 & vbNewLine & msg2 & vbNewLine & blnkRng.Address, vbOKOnly
Case 19 To 22 'col S thru V
Set blnkRng = Range("S" & .Row & ":V" & .Row).SpecialCells(xlCellTypeBlanks)
MsgBox msg1 & vbNewLine & msg2 & vbNewLine & blnkRng.Address, vbOKOnly
Case Else
End Select
End With

End Sub
[/vba]

[BrianMH]

Not sure what you are using your data for but using forms may be a good way to 'hand hold' your users into inputting all the details you need.

Also if you have multiple users/departments using this Access is a much better tool for data entry. I am going grey with all the data being lost by my team using shared excel spreadsheets.

[georgedaws]

Hi mancubus and BrianMH

Been out in the garden this afternoon doing a spot of gardening. Took my mind off staring at my code!

Mancubus. I will have a play with the code. Thank you for that.
BrianMH, hello mate, believe me, I would use Acess tomorrow for what I need. Trouble is, we only have excel and 2003 at that. I've got another post going at the moment trying to work out how to put four rule conditional formatting into the mix.

These two things I have left to do then I am free! (till the next thing!!)

Still all help stops me going grey too!

Thanks

[georgedaws]

Hey!

Back again to ask for more advice regarding this.

The code inpost #8 seems to work OK on a trial workbook, but I need to move it into my into an already "VBA packed" workbook.

Courtesy of mdmackillop he guided (well did it!) through being able to have multiple Sub Worksheet Changes as below

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
Case 3
Call Macro1(Target)
Case 16
Call Macro2(Target)
Case Else
Call Macro3(Target)
End Select
End Sub[/VBA]

These of course called their respective macros but how would I incorporate post#8 code into this?

As it's another Worksheet change which alludes to many different "cases" I do not know how to put it in.

Can anyone tell me how please.

Thanks folks.

[nepotist]

If I understand you question right,Change the name of the sub routine to something else In this case say "Test"

[vba] public Sub Test (ByVAL Target As Range)
......
End Sub



'Update your worksheet change event to call the renamed Sub routine
Private Sub Worksheet_Change(ByVal Target As Range)
call Test(Target)
End Sub

and you can use the the call method to call the test sub routine where ever you want
[/vba]

[georgedaws]

Hi nepotist,

Your solution worked, thank you.

For yourself or others that may be able to help, can this code be modified to give a friendlier messagebox, i.e the column headers rather than for example $A2$ and such like. It would be easier for the user to read. My headers are all on row 2.

Lastly, can the user be coaxed back or forward to the last (or next) "missed cell. I am just trying to setup a "do not pass go!" unless you fill it all in environment.

I can imagine it is a bit tricky, and I wish work had Access!

Any more help (or requests for me to explain myself a better) much appreciated. This is my last hurdle!

[nepotist]

Try using
[VBA]Cells(2, blnkRng.Column)[/VBA] in the message box line instead of blnkRng.Address
Example

[VBA]MsgBox msg1 & vbNewLine & msg2 & vbNewLine & Cells(2, blnkRng.Column), vbOKOnly[/VBA]

[georgedaws]

Hi nepotist,

I will give it a go and let you know how i get on,

Thank you

[georgedaws]

Hi nepotist,

That has sovled the column header issue.

Any thoughts on returning to the (first) blank cell?

Thank you.

[nepotist]

Could you explain it more
Are you looking for first blank cell in a Row??

[georgedaws]

Hi nepotist,

Of course I will. Thanks for sticking with me! What the xisting code does at the moment is it just tells the user where there is data missing. I nee the user to be forced back to that cell on the row they have left blanks(s).

So within the "D" to "J" section if they try to move onto "F" without filling anything in on "D" and "E" the macro will pick up that "D" (say for example the column header is "Name") is the first one to be left blank, the messagebox will say "please enter information in column marked name" and the user is forced back to that cell, unable to progress.

Everything else you have done so far is brilliant. I just need the user to be "stopped in their tracks" and sent back to fill in the respective missing cell on that row. I still need to keep the parameters though; "D" to "J" etc.

I hope that made some sense.


Thanks nepotist

[nepotist]

Try
[VBA]blnkRng.select [/VBA] immediately after the message line you have

[georgedaws]

nepotist, you are a genius! A BIG thank you for all your help all day. Sorry for my delay in replying back.

I cannot see any more problems. What a star!

Thank you.