How to associate validation code to a cell versus a button

[jnicholas]

I am needing to validate the format of a cell immediately after the cell is filled in. I have all the necessary validation code in a macro. When the macro is associated to a form button it performs correctly. What I am doing is checking a cell to make sure it is numeric and not greater than 6 digits, if it is OK I then change the format to A000000. So if 2211 is entered it is changed to A002211, if 22 is entered it is changed to A000022.

I want to associate this macro to the cell so that when the user fills in the cell and moves off, the code is executed. Is this possible or do I have to use a button.

 

[LTunnicliffe]

Re: How to associate validation code to a cell versus a butt

I think this is what you are looking for:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 
'  Insert you validation code here and remember that Target is the cell that was just edited!
  
End Sub

Put this in the Sheet section the cell that you are concerned about of the VBE.

Hope this helps!

 

[jnicholas]

Re: How to associate validation code to a cell versus a butt

I am sorry, I do not know what you mean by the sheet section of the cell???

 

[LTunnicliffe]

Re: How to associate validation code to a cell versus a butt

In other words if the cell that you are concerned about is on Sheet2, put the code in the Sheet2 section of the VBE.

Make sense? :wink: If not post again.

 

[jnicholas]

Re: How to associate validation code to a cell versus a butt

OK this is what I have...

Sub Worksheet_Change(ByVal J9 as Excel.Range)

Dim MyStr, x, MyCheck

MyStr = Worksheets("Sheet1").Range("J9").Value
x = Len(MyStr)
MyCheck = IsNumeric(MyStr)

If Worksheets("Sheet1").Range("J9").Value <> "" Then
If MyCheck = False Then
MsgBox ("Presenter's Assoc. ID must be a numeric value no greater than 6 digits")
Worksheets("Sheet1").Range("J9").Value = ""


Exit Sub
Else
If x > 6 Then
MsgBox ("Presenter's Assoc. ID must be a numeric value no greater than 6 digits")
Worksheets("Sheet1").Range("J9").Value = ""

Exit Sub
Else
Worksheets("Sheet1").Range("J9").Value = Format(MyStr, "A000000")
End If
End If
Else
MsgBox ("Presenter's Assoc. ID must be a numeric value no greater than 6 digits")
Worksheets("Sheet1").Range("J9").Value = ""


End If

End Sub

I have placed this code in the VBE but it is not being executed. Obviously I am not understanding...

 

[LTunnicliffe]

Re: How to associate validation code to a cell versus a butt

Try this:

Sub Worksheet_Change(ByVal Target As Excel.Range)
  Application.EnableEvents = False
  
  If Target.Address <> Range("J9").Address Then GoTo ExitSub
  
  If IsNumeric(Target.Value) And Len(Target.Value) <= 6 Then
    Target.Value = Format(Target.Value, "A000000")
  Else
    MsgBox ("Presenter's Assoc. ID must be a numeric value no greater than 6 digits")
    Worksheets("Sheet1").Range("J9").Value = ""
  End If
  
ExitSub:
  Application.EnableEvents = True

End Sub

This should do what you want. This assumes that the user enters the ID in cell J9, if that isn't the case change the Range("J9") to Range("A1"), where "A1" is the cell that they enter the ID into.

Hope this helps!

 

[jnicholas]

Re: How to associate validation code to a cell versus a butt

OK, I understand the code, but am still not getting it to initiate.

When I open my VBE from the main worksheet (which by the way there is only 1 at this time), I see to sub routines. The first is Worksheet_Change and the second is a macro I wrote to send the excel sheet as an attachment via an email, and is associated with a command button, called SubmitEmail.

I am able to initiate the the SubmitEmail and step through the code by press F8, also by click the command button. But nothing initiates the Worksheet_Change.

It has been a very long time since I have worked with VB and then I did not work with it and Excel. I am assuming that I must not be connecting the code to sheet.

 

[LTunnicliffe]

Re: How to associate validation code to a cell versus a butt

Which module are you putting the code in?

LT

 

[jnicholas]

Re: How to associate validation code to a cell versus a butt

I have the code in module 1. There are no declarations, and only one other Sub. That one is associated with a control button.

 

[LTunnicliffe]

Re: How to associate validation code to a cell versus a butt

10-4

You need to move the code from module1 to the worksheet section of your code. If you look in the Project Explorer there should be a Worksheet1 (or whatever the name of the worksheet is) if you double click that you will get a window that looks like module, paste that code in there and everything should be good.

LT