Data validation and click button

[Andre Pereira]

Hi there guys!

Hope that you are all well.

This is my first post on MR Excel, so if i do something wrong (not accurate, inappropriate, too dumb, etc) please just let me know in order to improve myself.

My question:

I have a list of cells ex C10:L10 and C20:L20 and so on, and i need to some people fill this cells with some values (numeric only) and after they do it, they press a click button that validate the data and if something left to fill the message show something like this "empty cell I20, please fill and then validate again".

this click button must complies all data validation in this worksheet, because i'll have a bunch of list range to "check", but all with the same premises

Can you please help?
thanks in advance

André

 

[ZVI]

Hi André,
Welcome to MrExcel!
You may start by this code:

Sub CheckEpmtyCells()
  Const MyRange = "C10:L10,C20:L20"
  Dim Cell As Range, Rng As Range
  Set Rng = Range(MyRange)
  For Each Cell In Rng
    If Trim(Cell.Value) = "" Then
      Cell.Select
      MsgBox "Empty cell " & Cell.Address(0, 0) & ", please fill and then validate again"
      Exit For
    End If
  Next
End Sub

or by that one:

Sub CheckNumericCells()
  Const MyRange = "C10:L10,C20:L20"
  Dim Cell As Range, Rng As Range
  Set Rng = Range(MyRange)
  For Each Cell In Rng
    If VarType(Cell) <> vbDouble Then
      Cell.Select
      MsgBox "Cell " & Cell.Address(0, 0) & " is not numeric, please fill it correctly and then validate again"
      Exit For
    End If
  Next
End Sub

Regards

 

[Andre Pereira]

Hi ZVI,

thanks a lot, the first one works like a charm

don't want to be a pain in the ***, but could you "merge" the two conditions in the same code?

1º rule -> Check if data inserted is numeric
2º rule -> if any cell is left unfilled
3º rule -> if rules 1 and 2 is verified then show message "everything is okay, thanks to ZVI "

Once again, thanks for the welcome message and for the suggested code

Regards,
André

Hi André,
Welcome to MrExcel!
You may start by this code:

Sub CheckEpmtyCells()
  Const MyRange = "C10:L10,C20:L20"
  Dim Cell As Range, Rng As Range
  Set Rng = Range(MyRange)
  For Each Cell In Rng
    If Trim(Cell.Value) = "" Then
      Cell.Select
      MsgBox "Empty cell " & Cell.Address(0, 0) & ", please fill and then validate again"
      Exit For
    End If
  Next
End Sub

or by that one:

Sub CheckNumericCells()
  Const MyRange = "C10:L10,C20:L20"
  Dim Cell As Range, Rng As Range
  Set Rng = Range(MyRange)
  For Each Cell In Rng
    If VarType(Cell) <> vbDouble Then
      Cell.Select
      MsgBox "Cell " & Cell.Address(0, 0) & " is not numeric, please fill it correctly and then validate again"
      Exit For
    End If
  Next
End Sub

Regards

 

[ZVI]

Below is a merged version of the code:

Sub CheckDataCells()
  Const MyRange = "C10:L10,C20:L20"
  Dim Cell As Range, Rng As Range
  Set Rng = Range(MyRange)
  For Each Cell In Rng
    If VarType(Cell) <> vbDouble Then
      Cell.Select
      If Trim(Cell.Value) = "" Then
        MsgBox "Empty cell " _
              & Cell.Address(0, 0) _
              & ", please fill and then validate again"
      Else
        MsgBox "Cell " _
              & Cell.Address(0, 0) _
              & " is not numeric, please fill it correctly and then validate again"
      End If
      Exit Sub
    End If
  Next
  MsgBox "Everything is okay!", Title:="Well done!"
End Sub

 

[Andre Pereira]

Once again, works just perfectly!!!

I want to thank you again your kindness and availability , it's never too much

Kind regards mate,

André

 

[ZVI]

Pleasure to know it has helped!