Worksheet needs certain cells to have a value before triggering the userform.

[danbates]

Hi,

Please could someone help me?

I have a worksheet (attached) and I would like certain cells to have values before triggering the userform.

I have added the details to the worksheet with arrows which hopefully will make it easier to understand (I hope)

Any help would be much appreciated.

Kind Regards

Dan

[mehmetcik]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.Row < 6 Or Target.Column <> 12 Then Exit Sub

Application.EnableEvents = False
  
On Error Resume Next
Range(Cells(Target.Row, 3), Cells(Target.Row, 9)).SpecialCells(xlCellTypeBlanks).Select
On Error GoTo 0

If ActiveCell.Column <> 12 Then
'Select Blank Cell
Beep
Else
'No Blank Cell
If Cells(Target.Row, 9) < 11 Or Cells(Target.Row, 11) <> "" Then
UserForm1.Show
Else
Cells(Target.Row, 11).Select
'Column K needs to be completed
Beep
End If
End If

Application.EnableEvents = True
  
End Sub

[danbates]

Hi mehmetcik,

thank you for your response but unfortunately its coming up with "Run Time Error 1004 - No cells were found

and its highlighting this line:

Range(Cells(Target.Row, 3), Cells(Target.Row, 9)).SpecialCells(xlCellTypeBlanks).Select

Any ideas?

Thanks again

Dan

[mehmetcik]

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.Row < 6 Or Target.Column <> 12 Then Exit Sub

Application.EnableEvents = False
  
On Error Resume Next
Set Z = Range(Cells(Target.Row, 3), Cells(Target.Row, 9)).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Z.Column <> 12 Then
'Select Blank Cell
Beep
Else
'No Blank Cell
If Cells(Target.Row, 9) < 11 Or Cells(Target.Row, 11) <> "" Then
UserForm1.Show
Else
Cells(Target.Row, 11).Select
'Column K needs to be completed
Beep
End If
End If

Application.EnableEvents = True
  
End Sub

[danbates]

Hi mehmetcik,

I've tried this code and it come up with a compile error - Variable not defined and it highlighted the Z =. So I put in Dim Z As Range, is that correct?

but then it come up with the same error as before and highlighted the same line.

Kind Regards

Dan

[mehmetcik]

I have modified it a bit and have done some testing.

There is an error in your userform that you will need to address,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.Row < 7 Or Target.Column <> 12 Then Exit Sub

Application.EnableEvents = False
  
On Error Resume Next
Z = Range(Cells(Target.Row, 3), Cells(Target.Row, 10)).SpecialCells(xlCellTypeBlanks).Column
On Error GoTo 0

If Z < 10 Then
'Select Blank Cell
Cells(Target.Row, Z).Select
Beep
Else
'No Blank Cell
If Cells(Target.Row, 9) < 11 Or Cells(Target.Row, 11) <> "" Then
UserForm1.Show
Else
Cells(Target.Row, 11).Select
'Column K needs to be completed
Beep
End If
End If

Application.EnableEvents = True

End Sub

[danbates]

Hi mehmetcik,

That's perfect mate, thank you. I've managed to sort the error in my userform as well.

Thanks again

Dan