Recheck cell validation with VBA

B

Bjornar

Board Regular
Joined
Jan 9, 2008
Messages
84
I'm currently working on a project where cell validation is applied after a cell already contains a value. I was wondering if there is a way I can use VBA to check if the value in the cell is valid according to the current cell validation.

I don't know what kind of cell validation is applied to the cell, so what I'm actually looking for is a way to read the current validation settings for a cell.
I will then perform a validation in VBA, and do something with the formatting of the cell to show the user that he has to re-enter the value to get a valid value.

I have posted this question in another forum some days ago, without any response, so I try my luck here.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Welcome to the board.

Try this out..
MsgBox Range("A1").Validation.Formula1
 
Upvote 0
Thanks, that got me to look in the right direction.
It seems to me that

Range("A1").Validation.Value

might be what i need. It seems to me that this returns "False", if the cell value does not meet the current validation conditions. Please correct me if I'm wrong.
 
Upvote 0
That appears to be what it does. I didn't know that. That is kinda cool.
 
Upvote 0
Then I think I'm up and running very easy with this function:
Code: 
Public Function CheckValidation(testcell As String) As Boolean
    Application.Volatile True
    CheckValidation = Range(testcell).Validation.Value
End Function
Put this in a hidden column, cell B1
Code: 
=CheckValidation(ADDRESS(ROW(A1),COLUMN(A1)))
And use this formula for the conditional formatting of cell A1
Code: 
=NOT(B1)
It seems like I have to go thru cell B1 instead of putting the formula directly into the conditional formatting. I think this have something to do with the conditional formatting not beeing recalculated when the validation rule change, but not the actual cell value.

Thanks for putting me in the right direction, as I have actually a large range of cells to apply this too, This was my final step in creating a input range where the user can use as much cut, copy and paste as he likes to without distroying the formatting and validation, and even have a one level undo option.
 
Upvote 0
You must log in or register to reply here.

Similar threads

MartinS
  • Solved
Update Validation in Merged Cells
2
Replies
10
Views
202
Joe4
Joe4
A
  • Question
Creating A Validation List From Unique Values in A Column (Filtered and unfiltered)
Replies
3
Views
202
RoryA
RoryA
Z
  • Solved
Excel VBA: Using OR with <>
Replies
3
Views
148
rlv01
R
E
  • Question
Alternate Sheet/Cell Reference in VBA
Replies
1
Views
172
DanteAmor
DanteAmor
D
  • Question
Prevent paste from wepage overriding custom validation cell
Replies
0
Views
235
dpoag1
D

Forum statistics

Threads
1,215,782
Messages
6,126,872
Members
449,345
Latest member
CharlieDP

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top