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/excelfile...#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 wrote:
> 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/excelfile...#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
Contextures
http://www.contextures.com/tiptech.html

[Marc]

"Debra Dalgleish" wrote
> 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.
>
>
> --
> Debra Dalgleish


*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 wrote:
> "Debra Dalgleish" wrote
>
>>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.
>>
>>
>>--
>>Debra Dalgleish
>
>
>
> *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
Contextures
http://www.contextures.com/tiptech.html

[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]

"Debra Dalgleish" wrote...
> 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