Make several cells mandatory if a cell in a range is filled out

Hi,

I've tried and tried to solve this but can't really wrap my head around it.
I'm relatively new to VBA and could really need som help.

I have a sheet in which a person need to fill out the name of a customer in column B.
If colum B has a value they also need to fill out colums C,D,E,H and I on the same row.

The macro needs to work between row 26 to 111.

See below for my macro which works perfectly for row 26.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = False
Application.EnableEvents = True
If Range("B26") <> "" Then
If Range("C26") = "" Then
Cancel = True
Message = Message & Range("C24") & Range("C25") & " måste fyllas i för kunden " & Range("B26") & "." & Chr$(13)
End If
If Range("D26") = "" Then
Cancel = True
Message = Message & Range("D24") & " " & Range("D25") & " måste fyllas i för kunden " & Range("B26") & "." & Chr$(13)
End If
If Range("E26") = "" Then
Cancel = True
Message = Message & Range("E25") & " måste fyllas i för kunden " & Range("B26") & "." & Chr$(13)
End If
If Range("H26") = "" Then
Cancel = True
Message = Message & Range("H24") & " " & Range("H25") & " måste fyllas i för kunden " & Range("B26") & "." & Chr$(13)
End If
If Range("I26") = "" Then
Cancel = True
Message = Message & Range("I24") & " " & Range("I25") & " måste fyllas i för kunden " & Range("B26") & "." & Chr$(13)
End If
End If

If Cancel = True Then MsgBox Message

End Sub


I can repeat this macro by adding this code after but change the range to B27 and then add another one referencing B28 but after a few of these I get an error saying "procedure is too long"

So basically I need to loop the macro in some way.

Txh,

//Stefan

Thank you for your quick response!

It doesn't quite work. If only B26 is filled out and one or more mantadory cells on that row are empty the messagebox pops up and I cant' save, which is correct. But when I press OK, the message pops up for every row with the same message and reference even thougt no other cell in range B26 to B111 is filled out.

I hope you understand what I mean.

//Stefan

Hi,

I'm not sure what your aim is with this macro, but here's a try. It seems very inefficient to me, since the "Message" variable keeps changing all the time and the procedure only uses the one after the last change. If you explained it more, maybe we could shorten your code a lot more.

Please Login or Register  to view this content.

Hi,

I took your VBA and re-wrote it a bit and it works perfectly now!
I was a bit unclear, the message was to always get the value of cells C24 & C25, D24 & D25, E25, H24 & H25 and I24 & I25 as reference.

Below you can see how it turned out.

Thank you so much for your help!!


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim Msg As String
Dim i As Integer

Cancel = False
Application.EnableEvents = True

For i = 26 To 111
If Range("B" & i) <> "" Then
If Range("C" & i) = "" Then
Cancel = True
Msg = Message & Range("C24") & Range("C25") & " måste fyllas i för kunden " & Range("B" & i) & "." & Chr$(13)
End If
If Range("D" & i) = "" Then
Cancel = True
Msg = Message & Range("D24") & " " & Range("D25") & " måste fyllas i för kunden " & Range("B" & i) & "." & Chr$(13)
End If
If Range("E" & i) = "" Then
Cancel = True
Msg = Message & Range("E25") & " maåste fyllas i för kunden " & Range("B" & i) & "." & Chr$(13)
End If
If Range("H" & i) = "" Then
Cancel = True
Msg = Message & Range("H24") & " " & Range("H25") & " måste fyllas i för kunden " & Range("B" & i) & "." & Chr$(13)
End If
If Range("I" & i) = "" Then
Cancel = True
Msg = Message & Range("I24") & " " & Range("I25") & " måste fyllas i för kunden " & Range("B" & i) & "." & Chr$(13)
End If
End If
Next i

If Cancel = True Then MsgBox Msg

End Sub