Data Validation with "VBA Refreshing" out of order

[Marc]

Dear All,
I am adjusting an excel.sample, discovered in this beautiful website
http://www.contextures.com/excelfiles.html to my purpose for job. The
spreadsheet
sample is: http://www.contextures.com/excelfiles.html#DataValNameID , but I
realize that it doesn't work or I should say, it works only in the dedicated
cells, built by the author (B2:B16) and not in a different range (such as
I6:I16). I analized the VBA routine, but I didn't see anything strange,
perhaps I missed something.

Can anyone help me in refreshing the Data Validation in another cells range?
Thanks in advance,
Marc


°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°
The routine is as follow:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
If Target.Cells.Count > 1 Then GoTo exitHandler
If Target.Column = 2 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If

End Sub

Sub MyFix()
Application.EnableEvents = True

End Sub

 

[Debra Dalgleish]

I'm glad you like the site! You can change the number of the target
column, e.g.:

If Target.Column = 9 Then

so it works on column I, instead of column B.

 

[Marc]

I'm glad you like the site! You can change the number of the target
column, e.g.:

If Target.Column = 9 Then

so it works on column I, instead of column B.

*Thanks a lot* Debra, it works perfectly!

If I had 2 or more that one data validation cells in the same Sheet1, what
should I write in the VBA code in order to refreshing both cells? I tried to
adjust the vba row as follow:

If Target.Column = 2 and 9 Then

but only the first column refreshs.
Anyway thank you very much for your first answer and best wishes!
Marc

 

[Debra Dalgleish]

If you want the same code to run for either column, you could use:

If Target.Column = 2 Or Target.Column = 9 Then

If you want different code to run in specific columns, you could use:

'======================
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
If Target.Cells.Count > 1 Then GoTo exitHandler
Select Case Target.Column
Case 2, 9
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes") _
.Range("ProdList"), 0), 0)
Case 8
If Target.Value = "" Then GoTo exitHandler
MsgBox "Column 8"
Case Else
If Target.Value = "" Then GoTo exitHandler
MsgBox "Nothing happens here"
End Select

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If

End Sub
'====================

 

[Marc]

*Thanks a lot* Debra, it works perfectly!

If I had 2 or more that one data validation cells in the same Sheet1, what
should I write in the VBA code in order to refreshing both cells? I tried
to adjust the vba row as follow:

If Target.Column = 2 and 9 Then

but only the first column refreshs.
Anyway thank you very much for your first answer and best wishes!
Marc

Delete my last post Debra, I solved the problem by myself. In the vba row we
must use "Or" and NOT "And", so both data validation can refreshing.
e.g.
If Target.Column = 2 Or 9 Then

Kind regards,
Marc

 

[Marc]

If you want the same code to run for either column, you could use:

If Target.Column = 2 Or Target.Column = 9 Then

If you want different code to run in specific columns, you could use:

'======================
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
If Target.Cells.Count > 1 Then GoTo exitHandler
Select Case Target.Column
Case 2, 9
If Target.Value = "" Then GoTo exitHandler

[...]

Thanks again for your prompt replay, your guidelines are useful for building
a spreadsheet in the factory concernig a ExpenditureNote template for the
reimbursement fuel cost when associates use their own car and not the
company ones.
Kind regards,
Marc
Italy