Excel’s VBA: Userform with Data validation

Hello!

I really need help with this one. I’ve been working on this for days but my code isn’t working. My code needs to check if Cell is a number. If cell isn’t a number, then it have to show UserForm and change the value of the cell from EntryBox. But for some reason after I press “OK”, it just switch to next Cell without changing.

Public Function test(x)
r = ActiveCell.Row
c = ActiveCell.Column
If IsNumeric(Cells(r, c).Value) Then
test = True
Else
test = False
MyEntryForm.Label1 = "Error " & Cells(r, c)
MyEntryForm.Show
If FormCancelled = True Then
MsgBox ("Cancelled")
Else
Cells(r, c).Value = MyEntryForm.EntryBox.Value
End If
End If
End Function

I’m using the same formula to validate multiple cells in one column.

Code inside UserForm (if need):

Public FormCancelled As Boolean
Public Sub InitializeForm()
FormCancelled = True
End Sub

Private Sub OKButton_Click()
MyEntryForm.Hide
End Sub

Top Contributor Asked on December 28, 2016 in VBA: User Forms.
Add Comment
2 Answer(s)

Hi,

I would like to understand where you are calling this function(test(x)), and what is the parameter ‘X’ you are passing in the function. If possible please provide the example macro file.

Email us at :  info@analysistabs.com

Thanks

 

Keymaster Answered on December 28, 2016.
Add Comment

“x” is the active Cell. I have another Cell that do the function “test” and to that cell is Data Validation attached. So it checks if cell “X” is working with formula test. And how can I provide an Example?

Thanks!

Top Contributor Answered on December 28, 2016.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.